retrieve id from database (SQL) while updateing dataset

[mcerk]

Hi. I want to update dataset into a table with 'autoincrease id field'.

 

 

I'm updating it like this:

       Dim NewRow As DataRow = Me.DataSet.Table1.NewRow
       With NewRow
           .Item("Field1") = "value 1"
           .Item("Field2") = "value 2"
           .Item("Field3") = "value 3"
           me.text = .Item("id") 'at this stage, I got an ID generated by ??? (but it is suretanely not generated by SQL server)
       End With

       
       DataSet.Table1.Rows.Add(NewRow) 'update dataset

       DataAdapter.Update(DataSet) 'there dataset is actually updated to SQL server database.

 

So. My question is. How can I figure out which 'id' is generated by SQL server. (I need it to manually update related tables).

 

PS: I do not copy the whole database table into dataset, but only one, or few records. So it is impossible for dataset to figure out exact 'id' that would be generated by SQL server.

 

tx a lot,

 

 

matej

[mcerk]

one solution is this:

 

      DataAdapter.Update(Dataset.Table1)
      myID = RetrieveID("SELECT top 1 * FROM opisDM ORDER BY id desc")
     
...

   Private Function RetrieveID(ByVal sSQL As String)
       Dim MyConnection As New OleDbConnection(sConnect)
       MyConnection.Open()
       Dim MyCommand As New OleDbCommand(sSQL, MyConnection)
       Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()
       While MyReader.Read
           RetrieveID = MyReader("id")
       End While
       MyConnection.Close()
       MyReader.Close()
       MyCommand.Dispose()
   End Function

 

while I'm adding only one record at the time, this should work. but what it I yould be adding multiple records, or if the server would be so busy, that would actually retrieve wrong ID (many people are adding records at same time))

[pendragon]

One way to do it is to use the @@IDENTITY Command and use the RowUpdated Event to get the number, This will return the last autonumber generated on the current connection.

 

OleDbCommand GetAutoNumber = new OleDbCommand("SELECT @@IDENTITY ", cn);

da_Insert.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdate);



private static void HandleRowUpdate(object sender, OleDbRowUpdatedEventArgs e) 

{

	if ((e.Status == UpdateStatus.Continue) && (e.StatementType == StatementType.Insert)) 

	{

		e.Row[TempStr[9]] = (int)GetAutoNumber.ExecuteScalar();

	}

}

[/Code]

 

I am using this with an Access Database but it should work with SQL. You might want to look at the other ways SQL can do this, SCOPE_IDENTITY(), batched queries and stored procedures.

Edited December 21, 2004 by pendragon

[penfold69]

Hrm, I don't directly use the SQL/ODBC/OLE libraries - I interface with a MySQL server using the Connector/NET.

 

The way that they handle it is that after the .Update command, the dataset ID field will contain the actual inserted ID from the database.

 

So, if you know which row you added, (or can find it) then perhaps you can retrieve the ID from it, after issuing the .Update statement?

 

B.

[mcerk]

So, if you know which row you added, (or can find it) then perhaps you can retrieve the ID from it, after issuing the .Update statement?

 

B.

 

I see. And which row did I add?

DataSet.Table1.Rows.Add(NewRow)

Is it Dataset.Table1.Rows.count-1 ????

 

 

tx

 

matej