Identity

[Leeus]

Hi all this is my code for a start.

Sub sqlinsert()

Try

kermitconnection = New SqlConnection("Server=localhost;uid=USERNAME;pwd=PASSWORD;database=DATABASE")

kermitconnection.Open()

Dim cmdinsertsupportitem As SqlCommand

cmdinsertsupportitem = New SqlCommand("Insert employees (lastname,firstname,department) Values (@thelastname,@thefirstname,@thedepartment)", kermitconnection)

cmdinsertsupportitem.Parameters.Add("@thelastname", alastname.text)

cmdinsertsupportitem.Parameters.Add("@thefirstname", afirstname.text)

cmdinsertsupportitem.Parameters.Add("@thedepartment", adepartment.text)

cmdinsertsupportitem.ExecuteNonQuery()

 

kermitconnection.Close()

Catch

End Try

End Sub

I have looked all over and found quite a few examples of getting the Identity after I have done this insert, does anyone know of a way of implementing this into my existing code?

[Nerseus]

I assume this is SQL server? You'll have to add "SELECT @@IDENTITY" to your SqlCommand and run an actual query, not ExecuteNonQuery. You can use a DataReader or a DataSet - but you need to get the value out. The "SELECT @@IDENTITY" will give you the ID of the last inserted row, in column1 or row1 of your executed statement.

 

You may have to also run "SET NOCOUNT ON" as the first part of your SQL statement as the rowcount of the actual INSERT may cause problems with retrieving the ID (I can't remember).

 

Here's the final SQL statement that should be built (all in one SQL string):

SET NOCOUNT ON
Insert employees (lastname,firstname,department) Values (@thelastname,@thefirstname,@thedepartment)
SELECT @@IDENTITY

 

-Ner