Retrieving Identity value after a new row is saved.

[OnTheAnvil]

I have a SQL Server 2000 database with a table "Customers" with a column "csID" with Identity Seed = 1 and Identity Increment = 1. Nothing fancy. When I save a row using the following code I would like to have the value of the csID returned back to me so that I can use it else where. I could write a query to go back to the database and find the row and then get the csID back out of that row but that seems like a waste. Is there anyway that I can refresh the drNewRow so that it will contain the csID that was automatically given to it when it was saved?

 

'-------------------------------------

 

Dim da As OleDb.OleDbDataAdapter

da = returnPageDataAdapter()'Return a valid dataadapter for my table

Dim dt As New DataTable()

Dim drNewRow As DataRow

da.Fill(dt)

drNewRow = dt.NewRow()

 

'Code to populate the drNewRow

 

dt.Rows.Add(drNewRow)

da.Update(dt) 'Commit the changes to the database

 

 

Thanks,

OnTheAnvil

[Derek Stone]

Execute the following statement after the row in question is inserted/updated:

SELECT @@IDENTITY;

[OnTheAnvil]

Execute the following statement after the row in question is inserted/updated:

SELECT @@IDENTITY;

 

Does that return that last identity created? Because if it does how can I be sure no other rows where created since I saved the row I'm working with.

 

~OnTheAnvil

[Derek Stone]

The SELECT @@IDENTITY statement has to be executed after the row is inserted/updated, and prior to any other rows being inserted/updated in order for it to return the "expected" value. Ideally, you should place your SQL code in a stored procedure and use the SCOPE_IDENTITY function to restrict the returned value to the current scope and session.

[Joe Mamma]

Get the ado.net cook book!

Go to oreilly.com

get the code for chapter 4.1 (i think thats it)

 

No need to 'retrieve' the identity. set your identity increment to a negative number, on update the idenitiy will value will be the one generated on the server

[OnTheAnvil]

Joe Mamma,

You idea sounds like exactly what I want. Unfortunately I'm not close to a store that would sell any kind of computer books. Would you mind sending me a code snippet so I can see what your talking about. I'll try and order the book online but I'd like to get started on solving this problem.

 

 

Thanks,

OnTheAnvil

[Joe Mamma]

sorry, didn't see your reply.

 

http://examples.oreilly.com/adonetckbk/

 

in the zip, .\Main\Chapter 04\AutoIncrementWithoutConflictForm.cs (or vb if you get that zip)