Retrieving the identity value after insert a row.

[aecordoba]

Hello. I'm programming a Windows Application which manage data in a MS SQL Server database. So, I created a DataSet of the database by means of the wizard, and in the "Advanced Options" I checked the followin check box: Refresh the data table Adds a Select statement after Insert and Update statements to retrieve identity column values, default values, and others values calculated by the database. Now, I need to retrieve the identity value of the row I'm inserting. Which "Select statement" was added in order to get the identity column value after insert a row? How should I use those statements? Thank you, in advance.

-- [Adrián Córdoba]

[SomeGuyThatIsMe]

If i remember correctly it adds whatever select statement it initially uses to the ends of update and insert and delete commands so that the dataSet in memory is always accurate to what is in the database so you can spot errors easily, and you dont have to worry about the dataset saying one thing and the db saying another. You can change the statements, but you have to move the declaration statements out of the InitializeComponent method which is generated for you and put them into the forms constructor or load event. there may be problems with moving them that i havnt found yet..i dont do much with the data once i get it.

[aecordoba]

OK. Please, suppose I just inserted a new row in a datatable with:

myDataSet.myDataTable.Rows.Add(myNewRow)

How can I get the identity column value? Thank you.

-- [Adrián Córdoba]

[SomeGuyThatIsMe]

What are you using for your identity column value..is it an auto increment field? I never use auto increment fields, for this reason, i actually have a table called counters that has maybe a dozen or so values that i increment every time i reference them, but i never let the db adjust them on its own, i use a getNextNumber stored procedure that returns the current value and then increments it so its ready for next time. that way i always know what PK i'm dealing with in the code. Or i make the PK derive from something i know so that its never not known or available to me in code or SQL. but anyway, You would have to call the update method on the dataAdapter assoc'd with your dataSet(assuming you are doing it that way) and then go find your new row in the result set it returned..probably the last one, unless you order them differently in your select statement.

[aecordoba]

Yes, I used an auto increment identity column. Thank you very much.

-- [Adrián Córdoba]

[SomeGuyThatIsMe]

There are several problems with an auto increment column , mostly involves using foreign keys and moving the data around the databse if you ever had to..its always a good idea to control what your keys are and not let something decide for you, then you have to get the new key before you insert so you dont have this problem.. I dont know your data so i cant suggest a good key, but if you're happy with a simple incrementing integer, why not just do table that contains several rows with the id name, its current value, and what to increment it by and use a stored procedure to return the current value then increment it, that way 2 synchrous requests cant duplicat the value...its a little more work but solves a lot of problems now and in the future. onces its set up you can use it all over the place when the data you're trying to store doesnt give you a decent primary or composite key.

[aecordoba]

I understood you, but database is already working (in production), so I can't modify it. Thank you very much.

-- [Adrián Córdoba]

[SomeGuyThatIsMe]

then the only way i know if is to insert your row then go looking for it in the dataset, if you are using a data adapter. If you are using a stored procedure you could have the SP return the new identity. sorry auto increments make things harder than they should be sometimes.