none
how to get (id) number before insert data use vb.net and sql server ?

    Question

  • I'm use connection String , I want get (id) number before insert to reserve a row empty in table sql server for the purpose of entering data

    Thursday, April 13, 2017 9:22 PM

Answers

  • The following is against SQL-Server. Insert a new row on form load, get it's key. Press the insert button finds that record and updates it else pressing cancel removes the newly added record.

    Imports System.Data.SqlClient
    Public Class ExampleForm
        Private NewIdentifier As Integer = 0
        ''' <summary>
        ''' Insert row, present id in text box
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub ExampleForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO [Customer] (CompanyName) VALUES (@CompanyName); SELECT CAST(scope_identity() AS int);"
                    cmd.Parameters.AddWithValue("@CompanyName", "Temp")
                    cn.Open()
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    txtId.Text = NewIdentifier.ToString
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Update row with id set in form load
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdInsert_Click(sender As Object, e As EventArgs) Handles cmdInsert.Click
            If String.IsNullOrWhiteSpace(txtCompanyName.Text) Then
                MessageBox.Show("Please enter a company name")
                Exit Sub
            End If
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName WHERE Identifier = @Identifier"
                    cmd.Parameters.AddWithValue("@CompanyName", txtCompanyName.Text)
                    cmd.Parameters.AddWithValue("@Identifier", NewIdentifier)
                    cn.Open()
                    Dim affected As Integer = cmd.ExecuteNonQuery
                    If affected <> 1 Then
                        MessageBox.Show("Update failed")
                    Else
                        MessageBox.Show("Updated")
                    End If
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Remove record
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdCancel_Click(sender As Object, e As EventArgs) Handles cmdCancel.Click
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "DELETE FROM Customer  WHERE Identifier = @Identifier"
                    cmd.Parameters.AddWithValue("@Identifier", NewIdentifier)
                    cn.Open()
                    Dim affected As Integer = cmd.ExecuteNonQuery
                    If affected <> 1 Then
                        MessageBox.Show("Delete failed")
                    Else
                        cmdInsert.Enabled = False
                    End If
                End Using
            End Using
        End Sub
    End Class

    Using MS-Access the difference is the insert e.g.

    Public Function AddNewRow(ByVal Name As String, ByRef Identfier As Integer) As Boolean
        Dim Success As Boolean = True
        Dim Affected As Integer = 0
    
        Try
            Using cn As New OleDbConnection With {.ConnectionString = "TODO"}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName) Values(@CompanyName)"
    
                    cmd.Parameters.AddWithValue("@CompanyName", Name)
    
                    cn.Open()
    
                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
        End Try
    
        Return Success
    
    End Function
    If using TableAdapter you will need to check your constraints and test as the insert is easily done unless working with MS-Access will take a tad more work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by srajmuneer Friday, April 14, 2017 12:49 PM
    Thursday, April 13, 2017 10:44 PM
    Moderator
  • Basics

    Using cn As New SqlConnection With {.ConnectionString = "TODO"}
        Using cmd As New SqlCommand With {.Connection = cn}
            cmd.CommandText = "SELECT CompanyName, FROM [Customer] WHERE Identifier = @Identifier"
            cmd.Parameters.AddWithValue("@Identifier", txtId.Text)
            cn.Open()
            Dim reader = cmd.ExecuteReader
            If reader.HasRows Then
                reader.Read()
                txtCompanyName.Text = CType(reader.GetSqlString(0), String)
            End If
        End Using
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by srajmuneer Monday, April 17, 2017 2:40 PM
    Monday, April 17, 2017 1:29 PM
    Moderator

All replies

  • You would need to create a new record with the ID set and all other values set to null, assuming the database fields allow it.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, April 13, 2017 9:50 PM
    Moderator
  • The following is against SQL-Server. Insert a new row on form load, get it's key. Press the insert button finds that record and updates it else pressing cancel removes the newly added record.

    Imports System.Data.SqlClient
    Public Class ExampleForm
        Private NewIdentifier As Integer = 0
        ''' <summary>
        ''' Insert row, present id in text box
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub ExampleForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO [Customer] (CompanyName) VALUES (@CompanyName); SELECT CAST(scope_identity() AS int);"
                    cmd.Parameters.AddWithValue("@CompanyName", "Temp")
                    cn.Open()
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    txtId.Text = NewIdentifier.ToString
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Update row with id set in form load
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdInsert_Click(sender As Object, e As EventArgs) Handles cmdInsert.Click
            If String.IsNullOrWhiteSpace(txtCompanyName.Text) Then
                MessageBox.Show("Please enter a company name")
                Exit Sub
            End If
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName WHERE Identifier = @Identifier"
                    cmd.Parameters.AddWithValue("@CompanyName", txtCompanyName.Text)
                    cmd.Parameters.AddWithValue("@Identifier", NewIdentifier)
                    cn.Open()
                    Dim affected As Integer = cmd.ExecuteNonQuery
                    If affected <> 1 Then
                        MessageBox.Show("Update failed")
                    Else
                        MessageBox.Show("Updated")
                    End If
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Remove record
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdCancel_Click(sender As Object, e As EventArgs) Handles cmdCancel.Click
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "DELETE FROM Customer  WHERE Identifier = @Identifier"
                    cmd.Parameters.AddWithValue("@Identifier", NewIdentifier)
                    cn.Open()
                    Dim affected As Integer = cmd.ExecuteNonQuery
                    If affected <> 1 Then
                        MessageBox.Show("Delete failed")
                    Else
                        cmdInsert.Enabled = False
                    End If
                End Using
            End Using
        End Sub
    End Class

    Using MS-Access the difference is the insert e.g.

    Public Function AddNewRow(ByVal Name As String, ByRef Identfier As Integer) As Boolean
        Dim Success As Boolean = True
        Dim Affected As Integer = 0
    
        Try
            Using cn As New OleDbConnection With {.ConnectionString = "TODO"}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName) Values(@CompanyName)"
    
                    cmd.Parameters.AddWithValue("@CompanyName", Name)
    
                    cn.Open()
    
                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
        End Try
    
        Return Success
    
    End Function
    If using TableAdapter you will need to check your constraints and test as the insert is easily done unless working with MS-Access will take a tad more work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by srajmuneer Friday, April 14, 2017 12:49 PM
    Thursday, April 13, 2017 10:44 PM
    Moderator
  • Hi srajmuneer,

    Based on your description, can you tell why you want to do this? If your project is single use, you can get the last record ID from Sql, and then +1 to get the latest ID. If you project is used by many people, I think it is very unreasonable practice.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 5:18 AM
    Moderator
  • thank you very much Kareninstructor ,

    I have a question and if 50 users use this  my interface that contains your code at the same time by insert or updating or deleting it will cause a slowdown in these processes

    Friday, April 14, 2017 5:21 PM
  • I mean Insert data in one table within multiple users is normal
     for my interface use your code
    • Edited by srajmuneer Friday, April 14, 2017 5:30 PM
    Friday, April 14, 2017 5:29 PM
  • thank you very much Kareninstructor ,

    I have a question and if 50 users use this  my interface that contains your code at the same time by insert or updating or deleting it will cause a slowdown in these processes

    No the code will not cause performance issues.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 14, 2017 6:29 PM
    Moderator
  • thank you very much Kareninstructor your code very good
    Friday, April 14, 2017 6:40 PM
  • The following is against SQL-Server. Insert a new row on form load, get it's key. Press the insert button finds that record and updates it else pressing cancel removes the newly added record.

    Imports System.Data.SqlClient
    Public Class ExampleForm
        Private NewIdentifier As Integer = 0
        ''' <summary>
        ''' Insert row, present id in text box
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub ExampleForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO [Customer] (CompanyName) VALUES (@CompanyName); SELECT CAST(scope_identity() AS int);"
                    cmd.Parameters.AddWithValue("@CompanyName", "Temp")
                    cn.Open()
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    txtId.Text = NewIdentifier.ToString
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Update row with id set in form load
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdInsert_Click(sender As Object, e As EventArgs) Handles cmdInsert.Click
            If String.IsNullOrWhiteSpace(txtCompanyName.Text) Then
                MessageBox.Show("Please enter a company name")
                Exit Sub
            End If
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName WHERE Identifier = @Identifier"
                    cmd.Parameters.AddWithValue("@CompanyName", txtCompanyName.Text)
                    cmd.Parameters.AddWithValue("@Identifier", NewIdentifier)
                    cn.Open()
                    Dim affected As Integer = cmd.ExecuteNonQuery
                    If affected <> 1 Then
                        MessageBox.Show("Update failed")
                    Else
                        MessageBox.Show("Updated")
                    End If
                End Using
            End Using
        End Sub
        ''' <summary>
        ''' Remove record
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdCancel_Click(sender As Object, e As EventArgs) Handles cmdCancel.Click
            Using cn As New SqlConnection With {.ConnectionString = "TODO"}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "DELETE FROM Customer  WHERE Identifier = @Identifier"
                    cmd.Parameters.AddWithValue("@Identifier", NewIdentifier)
                    cn.Open()
                    Dim affected As Integer = cmd.ExecuteNonQuery
                    If affected <> 1 Then
                        MessageBox.Show("Delete failed")
                    Else
                        cmdInsert.Enabled = False
                    End If
                End Using
            End Using
        End Sub
    End Class

    Using MS-Access the difference is the insert e.g.

    Public Function AddNewRow(ByVal Name As String, ByRef Identfier As Integer) As Boolean
        Dim Success As Boolean = True
        Dim Affected As Integer = 0
    
        Try
            Using cn As New OleDbConnection With {.ConnectionString = "TODO"}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "INSERT INTO Customer (CompanyName) Values(@CompanyName)"
    
                    cmd.Parameters.AddWithValue("@CompanyName", Name)
    
                    cn.Open()
    
                    Affected = cmd.ExecuteNonQuery()
                    If Affected = 1 Then
                        cmd.CommandText = "Select @@Identity"
                        Identfier = CInt(cmd.ExecuteScalar)
                    End If
                End Using
            End Using
        Catch ex As Exception
            Success = False
        End Try
    
        Return Success
    
    End Function
    If using TableAdapter you will need to check your constraints and test as the insert is easily done unless working with MS-Access will take a tad more work.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    thank you Kareninstructor , for help ,

    I want use select query and show result into textboxID , textboxCompanyName ........... use same your code


    Monday, April 17, 2017 11:28 AM
  • Basics

    Using cn As New SqlConnection With {.ConnectionString = "TODO"}
        Using cmd As New SqlCommand With {.Connection = cn}
            cmd.CommandText = "SELECT CompanyName, FROM [Customer] WHERE Identifier = @Identifier"
            cmd.Parameters.AddWithValue("@Identifier", txtId.Text)
            cn.Open()
            Dim reader = cmd.ExecuteReader
            If reader.HasRows Then
                reader.Read()
                txtCompanyName.Text = CType(reader.GetSqlString(0), String)
            End If
        End Using
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by srajmuneer Monday, April 17, 2017 2:40 PM
    Monday, April 17, 2017 1:29 PM
    Moderator
  • thank you Kareninstructor , you can search for CompanyName any part text and show result into textbox and use button NEXT to move row1 to row2 to row3 to row4 ............  by your code select
    Tuesday, April 18, 2017 7:41 PM
  • Use LIKE and when doing so use parameters

    https://www.w3schools.com/sql/sql_like.asp

    See the following to get an idea for parameters (I would write something up but in the middle of a BDD: behavior driven development session)

    http://stackoverflow.com/questions/665129/use-of-sqlparameter-in-sql-like-clause-not-working


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 18, 2017 8:19 PM
    Moderator
  • thank you  Kareninstructor , can Using Sequence with your query (insert)  "INSERT INTO [Customer] (CompanyName) VALUES (@CompanyName); SELECT CAST(scope_identity() AS int);"

    because jump identity ID column from 100 to 1000

    I'm use sql server 2014

    help code


               


    • Edited by srajmuneer Thursday, April 20, 2017 6:23 AM
    Thursday, April 20, 2017 5:40 AM