Help: How do u get primary key from a dataset after update inserts new row?

[Barley T.]

I have an SQL table with the primary key set as autoincrement.
My goal is to insert a new row using a dataset and then retrieve the
primary key of the row just created.

My code works some of the time, but the primary key I get back from the
dataset is not always in sync with the SQL server's primary key.

I have read in a number of posts that u can set the DataAdapter wizard
in VisualStudio.net to refresh the dataset upon each update. However,
I'm not nusing VS.net. -or- They say u can call SELECT @@IDENTITY
yourself. Can anyone post the raw code necessary to do this?

(I have included the post from which I read this below the code.)

Big thanks in advance to anyone who shares a solution.

Here is the code:

queryString = "SELECT * FROM MSG_Messages WHERE FromSUID = " &
intSUID
sqlCommand = New SqlCommand(queryString, sqlConnection)

dataAdapter = New SqlDataAdapter(sqlCommand)

' set data adapter parameters
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim dsMessages As DataSet = New DataSet
dataAdapter.Fill(dsMessages, "MSG_Messages")

Dim tblMessages As DataTable
tblMessages = dsMessages.Tables("MSG_Messages")
intNumRows = tblMessages.Rows.Count

Dim drMessage As DataRow
drMessage = tblMessages.NewRow()

' Add an entrys to Messages table
drMessage("FromSUID") = intSUID
drMessage("CreateDate") = DateTime.Today
drMessage("Subject") = TextBoxSubject.Text
drMessage("Body") = TextAreaBodyCreate.Value

tblMessages.Rows.Add(drMessage)

' Send changes to sql server
Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter)
dataAdapter.Update(dsMessages, "MSG_Messages")


strId = drMessage("MessageId")




-------------------------------------------------------------------------
Here is the old post:

From: David Shannon ([email protected])
Subject: Re: SqlDataAdaper.Update...
View: Complete Thread (3 articles)
Original Format
Newsgroups: microsoft.public.dotnet.framework.adonet
Date: 2002-05-14 10:23:27 PST


If you are using SQLServer, there is an option on the DataAdapter Wizard
(Advanced Options) to update or refresh the dataset row from the data
adapter after each insert or update operation. When you select that
option, the wizard creates a second SQL statement that is batched with
the insert or update statement. That second SQL statement updates your
dataset row with the fresh values from the database, including the new
autoincrement value.

If you are using MS Access -- which doesn't seem to take batched SQL
statements -- you have to call SELECT @@IDENTITY yourself to get the
most recent autoincrement value issued by the database and then you have
to apply it to your dataset row in code.

--------------------------------------
David Taylor Shannon
Tucson, Arizona
[email protected]

Hi, got a problem with the 'Update' method, here is my situation:

ASP.NET Application

DB table Struct:
- ID: bigint, pk, AutoIncrement
- field2: char(50)

instanced:
- SqlDataAdapter;
- CommandBuilder;
- Typed DataSet (based on the DB table);
- DataView(DataSet.Tables["..."])

After filling the DataSet with the DataAdapter I instanced a new
DataRowView, correctly filled the filed2 and then successfully add the
new row to the DataView.

After calling the Update method data are correctly written to the DB.
Problems occurs when I read the AutoIncrement ID because the value of
the filed now differs from the one in the DB. I realized that there is
non sync between AutoIncrement indexes in the DB and in the DataSet,
so:

1 - Create a new row in the DataSet the ID is 1
2 - Update the DB, both ID are 1
1 --> 2 more times....

- Manually delete the last 2 row2 from the DB
- restart the application
- refill the dataset
- NOW
New row ID from the dataset will be (automatically ) the
last
row ID + 1
- BUT
When you Update the DB throught the DataAdapter the ID
will
be... not the first available but the first available
considering the ID of deleted rows too (that is the correct behavior
of the DataBase Server).
- AND
the DataSet is not correctly synchronized. he only way I
found,
at the moment, is to refill the DataSet with an obvious lost of
performance.

The other problem is that if I create a relation in the DataSet with
another table this could lead to constrain problems

Any idea..

I remain at your disposal for any further information,
Thanks in advance.

 

[Michael Lang]

I have an SQL table with the primary key set as autoincrement.
My goal is to insert a new row using a dataset and then retrieve the
primary key of the row just created.

My code works some of the time, but the primary key I get back from the
dataset is not always in sync with the SQL server's primary key.

I have read in a number of posts that u can set the DataAdapter wizard
in VisualStudio.net to refresh the dataset upon each update. However,
I'm not nusing VS.net. -or- They say u can call SELECT @@IDENTITY
yourself. Can anyone post the raw code necessary to do this?

(I have included the post from which I read this below the code.)

Big thanks in advance to anyone who shares a solution.

When using the Sql provider you can use multiple statements in the same
command to get the Db assigned value. This handles the call it your self
method you are asking about...

//Do all of this when you are initially creating your data adapter
string strSqlInsert = "INSERT INTO Employee"
+ "(First, Last) VALUES (@First, @Last)"
+ "; set @EmployeeID = SCOPE_IDENTITY()";
SqlCommand cmdInsert = new SqlCommand(strSqlinsert, sqlCon);
//Add the 3 parameters named in the CommandText
// (ask if you need details)
sqlDA.InsertCommand = cmdInsert;

If you are not using the Sql provider, but instead the OleDb or ODbc
providers you can't use multiple statements in a single command. Instead
you subscribe to the RowUpdated event of the DataAdapter. Ask if you
want details.

You can find both methods implemented in the context of an application in
samples that I provided on the "database app code generator" site in my
signature.

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

 

[Barley T.]

When using the Sql provider you can use multiple statements in the
same command to get the Db assigned value. This handles the call it
your self method you are asking about...

//Do all of this when you are initially creating your data adapter
string strSqlInsert = "INSERT INTO Employee"
+ "(First, Last) VALUES (@First, @Last)"
+ "; set @EmployeeID = SCOPE_IDENTITY()";
SqlCommand cmdInsert = new SqlCommand(strSqlinsert, sqlCon);
//Add the 3 parameters named in the CommandText
// (ask if you need details)
sqlDA.InsertCommand = cmdInsert;

If you are not using the Sql provider, but instead the OleDb or ODbc
providers you can't use multiple statements in a single command.
Instead you subscribe to the RowUpdated event of the DataAdapter. Ask
if you want details.

You can find both methods implemented in the context of an application
in samples that I provided on the "database app code generator" site
in my signature.

Michael,
thanks for the quick post. That is good info which I'm sure will be
useful. However, not sure how to apply it here when performing an update
via a DataSet. The dataAdapter.Update command issues all the SQL behiend
the scenes.

 

[Severin]

If you use an incremental autonumber for the primary field -- I use a global
function to return the last primary key in the table... Works well for many
types of things.

Public Function GetLastKey() as Integer
SelectCommand.CommandText = "SELECT [ID] FROM

ORDER BY [ID]
DESC;"
Return SelectCommand.ExecuteScalar()
End Function

Will ALWAYS return last entered increment autonumber primary key.
Check value of GetLastKey after any insert statement.

Severin

I have an SQL table with the primary key set as autoincrement.
My goal is to insert a new row using a dataset and then retrieve the
primary key of the row just created.

My code works some of the time, but the primary key I get back from the
dataset is not always in sync with the SQL server's primary key.

I have read in a number of posts that u can set the DataAdapter wizard
in VisualStudio.net to refresh the dataset upon each update. However,
I'm not nusing VS.net. -or- They say u can call SELECT @@IDENTITY
yourself. Can anyone post the raw code necessary to do this?

(I have included the post from which I read this below the code.)

Big thanks in advance to anyone who shares a solution.

Here is the code:

queryString = "SELECT * FROM MSG_Messages WHERE FromSUID = " &
intSUID
sqlCommand = New SqlCommand(queryString, sqlConnection)

dataAdapter = New SqlDataAdapter(sqlCommand)

' set data adapter parameters
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Dim dsMessages As DataSet = New DataSet
dataAdapter.Fill(dsMessages, "MSG_Messages")

Dim tblMessages As DataTable
tblMessages = dsMessages.Tables("MSG_Messages")
intNumRows = tblMessages.Rows.Count

Dim drMessage As DataRow
drMessage = tblMessages.NewRow()

' Add an entrys to Messages table
drMessage("FromSUID") = intSUID
drMessage("CreateDate") = DateTime.Today
drMessage("Subject") = TextBoxSubject.Text
drMessage("Body") = TextAreaBodyCreate.Value

tblMessages.Rows.Add(drMessage)

' Send changes to sql server
Dim objCommandBuilder As New SqlCommandBuilder(dataAdapter)
dataAdapter.Update(dsMessages, "MSG_Messages")


strId = drMessage("MessageId")




-------------------------------------------------------------------------
Here is the old post:

From: David Shannon ([email protected])
Subject: Re: SqlDataAdaper.Update...
View: Complete Thread (3 articles)
Original Format
Newsgroups: microsoft.public.dotnet.framework.adonet
Date: 2002-05-14 10:23:27 PST


If you are using SQLServer, there is an option on the DataAdapter Wizard
(Advanced Options) to update or refresh the dataset row from the data
adapter after each insert or update operation. When you select that
option, the wizard creates a second SQL statement that is batched with
the insert or update statement. That second SQL statement updates your
dataset row with the fresh values from the database, including the new
autoincrement value.

If you are using MS Access -- which doesn't seem to take batched SQL
statements -- you have to call SELECT @@IDENTITY yourself to get the
most recent autoincrement value issued by the database and then you have
to apply it to your dataset row in code.

--------------------------------------
David Taylor Shannon
Tucson, Arizona
[email protected]

Hi, got a problem with the 'Update' method, here is my situation:

ASP.NET Application

DB table Struct:
- ID: bigint, pk, AutoIncrement
- field2: char(50)

instanced:
- SqlDataAdapter;
- CommandBuilder;
- Typed DataSet (based on the DB table);
- DataView(DataSet.Tables["..."])

After filling the DataSet with the DataAdapter I instanced a new
DataRowView, correctly filled the filed2 and then successfully add the
new row to the DataView.

After calling the Update method data are correctly written to the DB.
Problems occurs when I read the AutoIncrement ID because the value of
the filed now differs from the one in the DB. I realized that there is
non sync between AutoIncrement indexes in the DB and in the DataSet,
so:

1 - Create a new row in the DataSet the ID is 1
2 - Update the DB, both ID are 1
1 --> 2 more times....

- Manually delete the last 2 row2 from the DB
- restart the application
- refill the dataset
- NOW
New row ID from the dataset will be (automatically ) the
last
row ID + 1
- BUT
When you Update the DB throught the DataAdapter the ID
will
be... not the first available but the first available
considering the ID of deleted rows too (that is the correct behavior
of the DataBase Server).
- AND
the DataSet is not correctly synchronized. he only way I
found,
at the moment, is to refill the DataSet with an obvious lost of
performance.

The other problem is that if I create a relation in the DataSet with
another table this could lead to constrain problems

Any idea..

I remain at your disposal for any further information,
Thanks in advance.

 

[Joe Fallon]

This is for Parent Child updates so you get 2 for the price of 1.
=================================================
The solution is to set up cascade update in your dataset so that when the
new Identity value is returned from the database you update the master table
and cascade the change to the child.
You have to trap the RowUpdated event and then post the new identity value.

For SQL Server, there is a way to send 2 statements separated by a
semi-colon.
The first is your Parent update statement the second is Select
@@Scope_Identity.
(I am not 100% sure about this though.) (See below for the method I really
use.)

For Access and Oracle (and SQL Server if you don't use multiple statements):
You have to trap the RowUpdated event and then post the new identity value.

================================================================
In my update method I have some code like this:

'handle the RowUpdated event to get the Identity value back from
SQL Server
'w/o the real Identity value, the child records won't be added to
SQL Server.
AddHandler da_Eimhdr.RowUpdated, AddressOf da_Handle_RowUpdated

'parent table
da_Eimhdr.Update(NewEimhdrRecords)

'child table
da_Eimln.Update(NewEimlnRecords)
================================================================
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetIdentity(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
================================================================
Private Function GetIdentity(ByRef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================





For Oracle - I use this code:
================================================================

Private Sub da_Handle_OracleRowUpdated(ByVal sender As Object, ByVal e As
OracleRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetOracleSequence(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub

Private Function GetOracleSequence(ByRef cnn As OracleConnection) As
Integer
Dim oCmd As New OracleCommand("SELECT SEQ_EIMHDR_EIMKEY.CURRVAL FROM
DUAL", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================

 

[Barley T.]

This is for Parent Child updates so you get 2 for the price of 1.
=================================================
The solution is to set up cascade update in your dataset so that when
the new Identity value is returned from the database you update the
master table and cascade the change to the child.
You have to trap the RowUpdated event and then post the new identity
value.

For SQL Server, there is a way to send 2 statements separated by a
semi-colon.
The first is your Parent update statement the second is Select
@@Scope_Identity.
(I am not 100% sure about this though.) (See below for the method I
really use.)

For Access and Oracle (and SQL Server if you don't use multiple
statements): You have to trap the RowUpdated event and then post the
new identity value.

================================================================
In my update method I have some code like this:

'handle the RowUpdated event to get the Identity value back
from
SQL Server
'w/o the real Identity value, the child records won't be
added to
SQL Server.
AddHandler da_Eimhdr.RowUpdated, AddressOf
da_Handle_RowUpdated

'parent table
da_Eimhdr.Update(NewEimhdrRecords)

'child table
da_Eimln.Update(NewEimlnRecords)
================================================================
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUpdated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetIdentity(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
================================================================
Private Function GetIdentity(ByRef cnn As SqlConnection) As
Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================





For Oracle - I use this code:
================================================================

Private Sub da_Handle_OracleRowUpdated(ByVal sender As Object, ByVal
e As
OracleRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetOracleSequence(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub

Private Function GetOracleSequence(ByRef cnn As OracleConnection)
As
Integer
Dim oCmd As New OracleCommand("SELECT SEQ_EIMHDR_EIMKEY.CURRVAL
FROM
DUAL", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
================================================================

Joe,
Thanks for the post.
I will try it.
- B.T.

 

[Barley T.]

If you use an incremental autonumber for the primary field -- I use a
global function to return the last primary key in the table... Works
well for many types of things.

Public Function GetLastKey() as Integer
SelectCommand.CommandText = "SELECT [ID] FROM

ORDER BY
[ID]
DESC;"
Return SelectCommand.ExecuteScalar()
End Function

Will ALWAYS return last entered increment autonumber primary key.
Check value of GetLastKey after any insert statement.

Severin

Severin,
This looks like it would work.
However, I would be concerned that someone might insert a row just before
the GetLastKey() ran. I might get the primary key of their row by mistake.

thx.
b.t.

 

[Joe Fallon]

With SQL Server 2000 it is recommended you do not even use Select @@Identity
anymore because the result wasn't guaranteed to be what you thought it
should be. (Basically triggers can interfere with the result.)

You should use Select Scope_Identity() which is the latest identity *on that
connection*.
--
Joe Fallon

If you use an incremental autonumber for the primary field -- I use a
global function to return the last primary key in the table... Works
well for many types of things.

Public Function GetLastKey() as Integer
SelectCommand.CommandText = "SELECT [ID] FROM

ORDER BY
[ID]
DESC;"
Return SelectCommand.ExecuteScalar()
End Function

Will ALWAYS return last entered increment autonumber primary key.
Check value of GetLastKey after any insert statement.

Severin

Severin,
This looks like it would work.
However, I would be concerned that someone might insert a row just before
the GetLastKey() ran. I might get the primary key of their row by mistake.

thx.
b.t.

 

[Severin]

You can include the call to that function immediately after the
ExecuteNonQuery Insert Command - would be 1 in 10million chances to get
another primary key I think, maybe not even possible cause of connection
pooling, I could be wrong about that though...

If you use an incremental autonumber for the primary field -- I use a
global function to return the last primary key in the table... Works
well for many types of things.

Public Function GetLastKey() as Integer
SelectCommand.CommandText = "SELECT [ID] FROM

ORDER BY
[ID]
DESC;"
Return SelectCommand.ExecuteScalar()
End Function

Will ALWAYS return last entered increment autonumber primary key.
Check value of GetLastKey after any insert statement.

Severin

Severin,
This looks like it would work.
However, I would be concerned that someone might insert a row just before
the GetLastKey() ran. I might get the primary key of their row by mistake.

thx.
b.t.