Have Geek Brain

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

I have VB.net code that inserts data into my SQL database using a typed dataset like this:

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

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

+2


a source to share


4 answers


I am assuming you are executing some kind of SQL in a method Authorized_UsersTableAdapter.Insert()

.

To return the id you use SCOPE_IDENTITY()

.



Example if your id is 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)
+3


a source


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



See this SO Question and Answers (Best way to get the ID of an inserted row?) Related to the SQL side of this.

+1


a source


There's a library in there, Kailua - Forgotten Methods in the ADO.NET API. which provides this and additional metadata for the top 5 vendors. This information is vendor specific.

Wrap your connection in net.windward.utils.ado.WrCommand

and use the function ExecuteInsert

.

+1


a source


You need to tell your desktop table adapter to update the data table after an update / insert operation. Here's how you can do it.

  • Open TableAdapter properties - > Default Select Query -> Advnaced options . and select the Refresh Data Table check box . Save the adapter now. Now, when you call update on table-adapter, the data table will be updated [updated] after the update / insert operation and will display the latest values ​​from the database table. if the primary key or any coloumn is set to auto-increment, the data table will have the last update of the last value.

  • Now you can call the update as TableAdapterObj.Update (ds.dataTable);

  • Read the latest values ​​from the DataTable columns (ds.dataTable) and assign the appropriate values ​​to the child table before updating / inserting . This will work exactly the way you want.

alt text http://ruchitsurati.net/files/tds1.png

0


a source






More articles:


All Articles