2

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?

Patrick Karcher
  • 22,995
  • 5
  • 52
  • 66
Vivian River
  • 31,198
  • 62
  • 198
  • 313

4 Answers4

3

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)
Mikael Svenson
  • 39,181
  • 7
  • 73
  • 79
  • The code that is running to actually insert the data is generated by the designer when I add the typed dataset objects to my project. I've been directed to avoid having SQL statements in my code. – Vivian River Apr 21 '10 at 14:09
  • 2
    I hate to tell you, but the designer is writing SQL statements to be executed in your code. If you don't have a problem with that, then you shouldn't have any problem modifying the SQL that the designer generated for you. Either way, some SQL somewhere has to get executed. – Jeremy Apr 21 '10 at 14:14
  • 1
    Then I would follow @Oded answer and wrap SCOPE_IDENTITY in a StoredProc. If you don't want to do this you have to require the recently inserted row which is inefficient. In some cases auto generation removes flexibility. – Mikael Svenson Apr 21 '10 at 14:15
  • @Jermeny, I couldn't agree more. I don't like autogenerated code which is "hidden" somewhere. I'd rather pack the SQL in a business layer somewhere. – Mikael Svenson Apr 21 '10 at 14:16
  • Thanks guys, that was very informative. One of my co-workers has been encouraging me to use the LINQ queries and these typed dataset seem to help facilitate that. – Vivian River Apr 21 '10 at 14:35
  • 1
    [Here's](https://blogs.msdn.microsoft.com/smartclientdata/2005/10/31/returning-the-identity-column-value-from-a-tableadapter-dbdirect-method/) an MS blog entry that suggests adding, in the query designer, another insert query with SELECT @@IDENTITY appended. – peterG Dec 13 '17 at 15:45
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.

Community
  • 1
  • 1
Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • That would be a solution if I were doing this with stored procedures. However, I'm trying to do this with the typed datasets that I setup with the Visual Studio designer. – Vivian River Apr 21 '10 at 14:10
1

There's a library, Kailua - The forgotten methods in the ADO.NET API. , that does provide this and additional metadata for the top 5 vendors. This info is vendor specific.

Wrap your connection in a net.windward.utils.ado.WrCommand and use the ExecuteInsert function.

Ken Bloom
  • 57,498
  • 14
  • 111
  • 168
David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • 1
    Does the library you linked answer the OP's question, and if so, how? – Robert Harvey Feb 21 '11 at 19:49
  • Yes. He wants a way to get the value of the primary key just inserted and Kailua has a call to give him that information. He can either use the library or read the code for the Sql Server driver to see how it is done specifically for Sql Server. – David Thielen Feb 22 '11 at 18:02
0

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.

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

this. __curious_geek
  • 42,787
  • 22
  • 113
  • 137