Tricky insert question

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am using a hierarchical DataSet (DataTables linked by DataRelations
on one key, "QtyID"), which is in turn bound to an Infragistics
UltraWinGrid. The idea is that binding this way allows the grid to
display "child" tables as "child" bands. In my app, "QtyID" is what
links the "master" quote table to "child" quote tables.

The problem comes when I have to insert a new quote record. No data
has been pulled from SQL Server on a new quote, so I generate the
QtyID key for the bands, and the user edits the grid accordingly.
Since my QtyID key value is generated, it maps to *no* existing QtyID
key (identity column) in the master quote database table. My original
plan was to discard my generated key, do an INSERT for the master
table, grab the QtyID identity value, and use that in subsequent
INSERTs on the child tables. Since the DataAdapter automatically
calls the InsertCommand for each record in the table, my original idea
seems like it won't work - won't be able to grab the identity
generated QtyID value and use it in a param for the other
InsertCommands.

Do I need to manually go to each record in the master DataTable,
perform an insert, get the identity QtyID value, then go to each each
linked record in the child DataTables, and perform an insert for the
child record using the identity QtyID value? It doesn't seem like
I'll be able to do this with a SQLDataAdapter, since I need to get the
identity QtyID value and replace the old values with it.


Thanks,

Mike
 
David Sceppa's book on ADO.Net is a *must* read.
One of the best written and clearest books on programming.

Here is a tip:

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_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
================================================================
 
Back
Top