2

i cannot retrieve the new identity value from an inserted record(via DataAdapter.Update) in my DataRow.

I'm using a strong typed Dataset as DAL. The table i want to update is joinded with other tables in the SelectCommand, hence the designer cannot automatically generate the insert-/update-/delete-commands and also it cannot "Refresh the DataTable" automatically(s. http://msdn.microsoft.com/de-de/library/dex7k4dw%28v=VS.100%29.aspx).

I've tried to set AutoIncrement=true/false on the primary-key's DataColumn but the result is the same: last MAX-ID+1 instead of the actual ID the database generated(SQL-Server 2005 EP; pk datatype:int, Is identity:yes, Identity Increment:1).

This will be the wrong value f.e if another instance of the application inserted a record that the first instance yet not knows and therefore generates an ID that already exists.

To retrieve the new identity from db i've appended following to my insert-command's CommandText:

;SELECT CAST (SCOPE_IDENTITY() AS int) AS newIdRMA

Also i've tried to add an Output-Parameter to it's parameter-collection: enter image description here

This is part of my code that updates the database and sets the new ID(that doesn't work):

 Me.dsRMA.RMA.AddRMARow(newRMA) ' adding new row to the (strong typed) DataTable ' 
 numRowsUpdated = daRMA.Update(Me.dsRMA.RMA) ' update via DataAdapter and insert the new record in DB '
 DirectCast(Page, Services).IdRma = newRMA.IdRMA ' this is not the actual value from DB but old Max-ID +1 '

Edit:

this is a screenshot of my TableAdapter's InsertCommand and it's parameter-collection: enter image description here

Thanks in advance.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) – nawfal Feb 23 '13 at 08:19

1 Answers1

4

Here How i do it: I append the following to my insert-command's CommandText:

--Return the new id
SELECT SCOPE_IDENTITY()

Then I set the "ExecuteMode" of that Query to "Scalar" instead of "NonQuery"

Query Properties window

so I could retrive it this way:

newRMA = Me.dsRMA.RMA.AddRMARow()
DavRob60
  • 3,517
  • 7
  • 34
  • 56
  • I'll try that. But where should i change that from `NonQuery` to `Scalar` because the standard-InsertCommand's properties haven't exposed such properties(only `Text`,`TableDirect` and `Stored Procedure`)? I'm now trying to change the InsertCommand to Stored-Procedure and use `SCOPE_IDENTITY` as Output parameter. But here again -if this would work - how is the DataTable refrehed with the new value? And second: is it possible to get two values as output and refresh the datarow with them(ID and RMA_Number)? Thank you. – Tim Schmelter Mar 14 '11 at 13:53
  • The properties values that you are referring are for the command type, the Execute mode is another propety as i show in the screen shot I just Added. – DavRob60 Mar 14 '11 at 14:22
  • For the 2 values as output, I don't like to do that. I let the database engine generate the ID and I Set other values like the RMA_Number in my BLL. note that you could also use a [Transaction](http://www.asp.net/data-access/tutorials/wrapping-database-modifications-within-a-transaction-cs) to make sure a block of queries are all executed at once. – DavRob60 Mar 14 '11 at 14:29
  • I see what you mean. But how to configure the TableAdapter to use this query on updating? AddRmaRow currently is a Sub and returns nothing(i think because it's ExecuteMode is default NonQuery). See my image above. Edit(on your second comment): the RMA_Number must be unique and is better generated in SQL-Server too. Therefore i wanted it to be an Output parameter. – Tim Schmelter Mar 14 '11 at 14:34
  • OK, I see, it's because you use the InsertCommand instead of another query. I add a separated Query on the Table Adapter to do this, then I warp all select insert and update queries in a BLL, witch it don't look like what you are doing. to keep the RMA_Number unique, if you warp the query that check the last RMA_Number with the insert query in a transaction, you will be OK. Meanwhile, I feel like I answered beside the question. – DavRob60 Mar 14 '11 at 14:50
  • So it's impossible to use the TableAdapter's Update method to update the strongly typed DataTable and retrieve the identity value from database back and refresh the DataTable automatically? I wanted to avoid several roundtrips to database and wanted to use the builtin functionality of the typed Dataset instead of writing all queries by myself. – Tim Schmelter Mar 14 '11 at 15:30
  • Impossible? I cannot say that. I could only tell I don't know how, and that I don't see how, as far as I know. – DavRob60 Mar 14 '11 at 15:45