Bill Vaughn's "Identity Crisis" article

  • Thread starter Thread starter Earl
  • Start date Start date
E

Earl

I've read the article on using Identity in disconnected scenarios,
http://www.ftponline.com/reports/vsliveor/2002/09_17_02/vaughn/default.asp,
and while I got much out of it, one thing wasn't so obvious. Once you do an
Insert, and retrieve the identity IDs with the output parameters (so far, so
good), how does the row receive that ID? I have a scenario where I try to
delete from a datagrid immediately after Inserting a row. I've not resolved
how to stop the concurrency error, despite retrieval of the identity ID as
an output parameter in the stored procedure. Running the delete code when I
have not inserted a row works fine.
 
If you're using the Update method, you can choose how to fetch the updated
row values (including the new Identity) by setting the UpdateRowSource
property. This can be set to "both", "FirstReturnedRecord" (which is used
where you do a post-UPDATE SELECT), "none", or "OutputParameters".

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I'll play around with that Bill. Thanks.

William (Bill) Vaughn said:
If you're using the Update method, you can choose how to fetch the updated
row values (including the new Identity) by setting the UpdateRowSource
property. This can be set to "both", "FirstReturnedRecord" (which is used
where you do a post-UPDATE SELECT), "none", or "OutputParameters".

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Click to expand...
 
Bill, I added that property, but still get the concurrency error when I get
over to the Delete command. I tried it both with OutputParameters (which I
have set in the sproc) and also as Both. Either will execute without error
during the Insert, but still raises a concurrency exception when going
directly to the Delete command.

Dim da As New SqlDataAdapter
Dim dsChanges As DataSet
dsChanges = dsWinMatrix.GetChanges
da.InsertCommand = CreateSupplierInsert()
'still get concurrency error despite this line
da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
 
Ok, turn on the Profiler and watch what's happening on the server. Examine
the value returned from the Output parameter. Is the Parameters collection
setup correctly?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks Bill. That got me in the hunt. The delete sp is receiving valid
parameter values for ID and Tstamp when deleting an existing record, but
receiving Null values when deleting a newly added record. So looking back
over the Insert sp, I see that the two output parameters are being set to
Null. I'm not sure yet why, but since the code looks correct, I'll work with
the sp.
 
Hmmmmm ... back to being utterly baffled by what must be obvious -- but I
cannot see it. Made a minor tweak to the Insert sp and now the output
parameters (ID and Tstamp) are being returned after Insert. Yet at the
delete command, no ID or Tstamp is present to send to the delete sp and thus
the concurrency exception.

Private Sub btnDelete_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnDelete.Click

If cmSuppliers Is Nothing Then
Exit Sub
End If

'the currency manager is instantiated right after binding to the datagrid
If cmSuppliers.Count > 0 Then
cmSuppliers.RemoveAt(cmSuppliers.Position)
End If

Dim dsChanges As DataSet
dsChanges = dsWinMatrix.GetChanges

If dsWinMatrix.HasChanges Then
Dim da As New SqlDataAdapter
da.DeleteCommand = CreateSupplierDelete()
da.Update(dsChanges, "dtSuppliers")
dsWinMatrix.AcceptChanges()
End If

Private Function CreateSupplierDelete() As SqlCommand

Dim strSQLServer As New SqlConnection(strConnString)
Dim cmdSupplierDelete As New SqlCommand("SupplierDeleteCommand",
strSQLServer)
cmdSupplierDelete.CommandType = CommandType.StoredProcedure
Dim pc As SqlParameterCollection = cmdSupplierDelete.Parameters

pc.Add(New SqlParameter("@Original_SupplierID", SqlDbType.Int, 4,
ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"SupplierID", DataRowVersion.Original, Nothing))
pc.Add(New SqlParameter("@Original_Tstamp", SqlDbType.VarBinary, 8,
ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Tstamp",
DataRowVersion.Original, Nothing))

Return cmdSupplierDelete

End Function
 
Fixed the concurrency issue with some help from Yana at MS by essentially
eliminating dsChanges from the Insert and Delete code.
 
Back
Top