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

2017-04-13

Question

_{Thursday, April 13, 2017 9:22 PM}

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

All replies (12)

_{Thursday, April 13, 2017 10:44 PM ✅Answered}

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

_{Monday, April 17, 2017 1:29 PM ✅Answered}

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

_{Thursday, April 13, 2017 9:50 PM}

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"

_{Friday, April 14, 2017 5:18 AM}

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:21 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

_{Friday, April 14, 2017 5:29 PM}

I mean Insert data in one table within multiple users is normal
for my interface use your code

_{Friday, April 14, 2017 6: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.

_{Friday, April 14, 2017 6:40 PM}

thank you very much Kareninstructor your code very good

_{Monday, April 17, 2017 11:28 AM}

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.

thank you Kareninstructor , for help ,

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

_{Tuesday, April 18, 2017 7:41 PM}

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 8:19 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

_{Thursday, April 20, 2017 5:40 AM}

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

שתף באמצעות

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

Question

All replies (12)

משאבים נוספים