Share via

Returning the identity column value from a TableAdapter DBDirect method

  • Article

A common scenario when using tables with an auto-increment primary key is to get the value of the identity column for the row you just inserted. Sometimes you can't, or don't want to, use DataSets, which is why we created the DBDirect methods on the TableAdapter. However, the default INSERT function doesn't return the primary key value. How can we get this in one step?

I've created a Visual Basic Console Application and added a DataSource with the Orders table from the SQL Northwind database. The first thing I'm going to do is make a custom INSERT query on the OrdersTableAdapter. Go to the Northwind.xsd file, right-click on OrdersTableAdapter, and choose Add -> "Query...". The TableAdapter Query Wizard will appear. Choose "Use SQL Statements" since we won't be using stored procedures in this example. Select "INSERT" for the query type. Now the default SQL statement appears:

INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate],
[ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode],
[ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight,
@ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE
(OrderID = SCOPE_IDENTITY())

Let's delete the bottom half since we won't be using this query to refresh a DataSet:

INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate],
[ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode],
[ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight,
@ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);

That's a bit more manageable. Now comes the magic part. We'll add a statement to return the @@IDENTITY value:

INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate],
[ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode],
[ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight,
@ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);
SELECT @@IDENTITY;

I named the new function "InsertAndReturnIdentity". There's one last important piece: select the new query in the DataSet Designer and change the ExecuteMode property from NonQuery to Scalar. We'll explain the difference between the two in a later entry.

Now we're ready to code. We'll create a new TableAdapter and insert a row with a bunch of dummy values:

Sub Main()
Dim ordersTA As New NorthwindDataSetTableAdapters.OrdersTableAdapter
Dim id As Integer
id = ordersTA.InsertAndReturnIdentity("ALFKI", Nothing, Nothing, _
Nothing, Nothing, Nothing, Nothing, "", "", "", "", "", "")
Console.WriteLine("The new OrderID is {0}", id)
End Sub

The output is:
The new OrderID is 11083

And that's all there is to it!

- Ryan Cavanaugh

Comments

  • Anonymous
    November 08, 2005
    What would be the best way to return the @@identity from insert commands that were inside a .net system.transaction?

  • Anonymous
    December 15, 2005
    Thanks for the tip. I always prefer SELECT SCOPE_IDENTITY() over SELECT @@IDENTITY, because the later could get the wrong result if there is a TRIGGER in the involved table that in turns also inserts rows on another table with identity columns. If you look at the code the wizard generates it uses SCOPE_IDENTITY() in the WHERE clause.
  • Anonymous
    July 05, 2006
    HI I found your code useful but still I have a problem, I need to insert a row and return an Identity I made myself and is a string... tableadapter insert only returns an integer... How can I find way to do this????here my query:INSERT INTO Myndatokur (id_efnistattur, myndatexti, stadur, dags_pantad, dags_myndatoku, timi_myndatoku, id_ljosmyndari_email, id_bladamadur_email, kenniord, simi_myndefnis, leitarord, dags_umbrots, timi_umbrots, hofundur_adsendrar, teg_myndbeidni, doubleclick, id_fyrirtaeki, aths_til_ljosmyndara, utklippt, efnistattur)VALUES (@id_efnistattur,@myndatexti,@stadur,@dags_pantad,@dags_myndatoku,@timi_myndatoku,@id_ljosmyndari_email,@id_bladamadur_email,@kenniord,@simi_myndefnis,@leitarord,@dags_umbrots,@timi_umbrots,@hofundur_adsendrar,@teg_myndbeidni,@doubleclick,@id_fyrirtaeki,@aths_til_ljosmyndara,@utklippt,@efnistattur); SELECT full_id_myndataka FROM Myndatokur WHERE (id_myndataka = @@IDENTITY)
  • Anonymous
    August 23, 2006
    Very small code,Very sharp,It works but it doesn't work (at least for me) when I use @ID=@@IDENTITY or SCOPE_IDENTITY() insead of @@IDENTITY.