How do I get the primary key of the row I just inserted into a typed dataset in .net?

Question

I have some VB.net code that inserts data into my SQL database using a typed dataset as follows:

dim usersTa As New authorizedUsersTableAdapters.Authorized_UsersTableAdapter usersTa.Connection = New Data.SqlClient.SqlConnection(MY_CONNECTION_STRING) usersTa.Insert(first_name, last_name)

In the database, there is a primary key by which I identify the rows. What is the most efficient way to find out the primary key of the row that I just inserted when I run this code?

Answer 1

If you are using stored procedures you can get the value as a return value of the stored procedure.

See this SO question and answers (Best way to get identity of inserted row?) dealing with the SQL side of this.

Answer 2

I assume you are executing some kind of SQL in the Authorized_UsersTableAdapter.Insert() method.

In order to return the identifier you use SCOPE_IDENTITY().

Example if your identifier is an Int.

Dim conn As SqlConnection = ...
Dim cmd As New SqlCommand(INSERT INTO T (Name) VALUES('Test')" & ChrW(13) & ChrW(10) & "SELECT SCOPE_IDENTITY() As TheId", conn)
Dim tId As Integer = CInt(cmd.ExecuteScalar)

Answer 3

You need to tell your table's table-adapter to refresh the data-table after update/insert operation. This is how you can do that.

1. Open the properties of TableAdapter -> Default Select Query -> Advnaced options. and Check the option of Refresh the data table. Save the adapter now. Now when you call update on table-adapter, the data-table will be updated [refreshed] after the update/insert operation and will reflect the latest values from database table. if the primary-key or any coloumn is set to auto-increment, the data-table will have those latest value post recent update.

2. Now you can Call the update as TableAdapterObj.Update(ds.dataTable);

3. Read latest values from the DataTable(ds.dataTable) coloumns and assign respective values into the child table before update/insert. This will work exactly the way you want.