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.
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.RowUpdated, AddressOf da_Handle_RowUpdated
'parent table
da_Hdr.Update(NewHdrRecords)
'child table
da_Ln.Update(NewLnRecords)
================================================== ==============
'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("key") = 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("key") = 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 Fallon
"Justin" <Ju****@discussions.microsoft.com> wrote in message
news:C4**********************************@microsof t.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.