OK, I know it’s recommended to use stored procedures but sometimes you need a short one for domestic use.
So, how do we do it, without setting two SqlCommand objects?
1. We define a connection
Dim conSQLDBConn As New SqlConnection([Connection String])
2. Create a command and set the params.
Dim cmdSQLCommand As New SqlCommand
cmdSQLCommand.CommandType = Data.CommandType.Text
3. Assign the connection
cmdSQLCommand.Connection = conSQLDBConn
4. Will define and configure a parameter that will return the value we need: the last inserted item ID
Dim prmInsert As New SqlParameter
prmInsert.Direction = ParameterDirection.Output
prmInsert.ParameterName = “ProductID” ‘ that would be your ID, in my case I was playing with products
prmInsert.Size = 10 ‘this should match what you defined in your table
cmdSQLCommand.Parameters.Add(prmInsert)
5. Now we create the insert statement
cmdSQLCommand.CommandText = “INSERT INTO Products (ProductName,ProductDescription) VALUES ([ProductName],[ ProductDescription]); SELECT SCOPE_IDENTITY() AS ProductID”
SELECT SCOPE_IDENTITY() is the magic word, that will return our ID.
6. Now we make sure we get that back from the SQL statement by using “ExecuteScalar”
Dim strProductID as String = cmdSQLCommand.ExecuteScalar()
Note that the values in square brackets would be your constants, whatever.
That’s it. Easy peasy, lemon squeezy 
© 2010 SolidPitch