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

Question
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
- Marked as answer by srajmuneer Friday, April 14, 2017 12:49 PM
-
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
- Marked as answer by srajmuneer Monday, April 17, 2017 2:40 PM
All replies
-
-
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
- Marked as answer by srajmuneer Friday, April 14, 2017 12:49 PM
-
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. -
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 -
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
-
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 processesPlease 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
-
thank you very much Kareninstructor your code very good
-
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
thank you Kareninstructor , for help ,
I want use select query and show result into textboxID , textboxCompanyName ........... use same your code
-
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
- Marked as answer by srajmuneer Monday, April 17, 2017 2:40 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
-
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