Retrieving Identity After Dataset update

[Justin]

I have created a dataset with two tables and an insert command, I need to be
able to retreive the Key Identity after inserting into table "A" for use in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.

[Ken Cox [Microsoft MVP]]

Hi Justin,

I think you want to use ADO.NET's InsertCommand for this.

You might want to check this:

http://msdn.microsoft.com/library/de...anidcrisis.asp

Retrieving Identity or Autonumber Values

http://msdn.microsoft.com/library/de...mberValues.asp
"The following code example shows how to return the auto-incremented value
as the output parameter and specify it as the source value for the
CategoryID column in the DataSet.

[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection(" Data
Source=localhos t;Integrated Security=SSPI;I nitial Catalog=northwi nd")

Dim catDA As SqlDataAdapter = New SqlDataAdapter( "SELECT CategoryID,
CategoryName FROM Categories", nwindConn)

catDA.InsertCom mand = New SqlCommand("Ins ertCategory", nwindConn)
catDA.InsertCom mand.CommandTyp e = CommandType.Sto redProcedure

catDA.InsertCom mand.Parameters .Add("@Category Name", SqlDbType.NChar , 15,
"CategoryNa me")

Dim myParm As SqlParameter = catDA.InsertCom mand.Parameters .Add("@Identity ",
SqlDbType.Int, 0, "CategoryID ")
myParm.Directio n = ParameterDirect ion.Output

nwindConn.Open( )

Dim catDS As DataSet = New DataSet
catDA.Fill(catD S, "Categories ")

Dim newRow As DataRow = catDS.Tables("C ategories").New Row()
newRow("Categor yName") = "New Category"
catDS.Tables("C ategories").Row s.Add(newRow)

catDA.Update(ca tDS, "Categories ")

nwindConn.Close ()
"Justin" <Ju****@discuss ions.microsoft. com> wrote in message
news:C4******** *************** ***********@mic rosoft.com...

I have created a dataset with two tables and an insert command, I need to
be
able to retreive the Key Identity after inserting into table "A" for use
in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.

[Justin]

I have tried that code, what I can't figure out is exactly what variable
holds the identity. If its "myParam" then how do I cast it to an int variable?

Thanks, Justin.

"Ken Cox [Microsoft MVP]" wrote:

Hi Justin,

I think you want to use ADO.NET's InsertCommand for this.

You might want to check this:

http://msdn.microsoft.com/library/de...anidcrisis.asp

Retrieving Identity or Autonumber Values

http://msdn.microsoft.com/library/de...mberValues.asp
"The following code example shows how to return the auto-incremented value
as the output parameter and specify it as the source value for the
CategoryID column in the DataSet.

[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection(" Data
Source=localhos t;Integrated Security=SSPI;I nitial Catalog=northwi nd")

Dim catDA As SqlDataAdapter = New SqlDataAdapter( "SELECT CategoryID,
CategoryName FROM Categories", nwindConn)

catDA.InsertCom mand = New SqlCommand("Ins ertCategory", nwindConn)
catDA.InsertCom mand.CommandTyp e = CommandType.Sto redProcedure

catDA.InsertCom mand.Parameters .Add("@Category Name", SqlDbType.NChar , 15,
"CategoryNa me")

Dim myParm As SqlParameter = catDA.InsertCom mand.Parameters .Add("@Identity ",
SqlDbType.Int, 0, "CategoryID ")
myParm.Directio n = ParameterDirect ion.Output

nwindConn.Open( )

Dim catDS As DataSet = New DataSet
catDA.Fill(catD S, "Categories ")

Dim newRow As DataRow = catDS.Tables("C ategories").New Row()
newRow("Categor yName") = "New Category"
catDS.Tables("C ategories").Row s.Add(newRow)

catDA.Update(ca tDS, "Categories ")

nwindConn.Close ()
"Justin" <Ju****@discuss ions.microsoft. com> wrote in message
news:C4******** *************** ***********@mic rosoft.com...

I have created a dataset with two tables and an insert command, I need to
be
able to retreive the Key Identity after inserting into table "A" for use
in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.

[Joe Fallon]

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_Identit y.

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_Hdr.RowUpdat ed, AddressOf da_Handle_RowUp dated

'parent table
da_Hdr.Update(N ewHdrRecords)

'child table
da_Ln.Update(Ne wLnRecords)
=============== =============== =============== =============== ====
'this is how to handle the insert of each row:

Private Sub da_Handle_RowUp dated(ByVal sender As Object, ByVal e As
SqlRowUpdatedEv entArgs)
If e.Status = UpdateStatus.Co ntinue And e.StatementType
=StatementType. Insert Then
e.Row("key") = GetIdentity(e.C ommand.Connecti on)
e.Row.AcceptCha nges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.Sk ipCurrentRow
End If
End Sub
=============== =============== =============== =============== ====
Private Function GetIdentity(ByR ef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SEL ECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteSca lar()
Return CInt(x)
End Function
=============== =============== =============== =============== ====

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

Private Sub da_Handle_Oracl eRowUpdated(ByV al sender As Object, ByVal e As
OracleRowUpdate dEventArgs)
If e.Status = UpdateStatus.Co ntinue And e.StatementType
=StatementType. Insert Then
e.Row("key") = GetOracleSequen ce(e.Command.Co nnection)
e.Row.AcceptCha nges()

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

Private Function GetOracleSequen ce(ByRef cnn As OracleConnectio n) As
Integer
Dim oCmd As New OracleCommand(" SELECT SEQ_EIMHDR_EIMK EY.CURRVAL FROM
DUAL", cnn)
Dim x As Object = oCmd.ExecuteSca lar()
Return CInt(x)
End Function
=============== =============== =============== =============== ====

--
Joe Fallon


"Justin" <Ju****@discuss ions.microsoft. com> wrote in message
news:C4******** *************** ***********@mic rosoft.com...

I have created a dataset with two tables and an insert command, I need to
be
able to retreive the Key Identity after inserting into table "A" for use
in
table "B".

Should I use ExecuteScalar() or is there a better solution?

Thanks, Justin.