Concurrency issue on Update

[Earl]

Not sure what I'm overlooking here, but banging my head against the wall for
several hours is long enough. Hopefully someone can see what is causing this
concurrency exception.

I do NOT get a concurrency error if I do an Update without any prior events
occuring (Delete or Insert). However, if I add a new record and immediately
do an Update on the same record, I then get a DBconcurrency exception.
Indeed, if I add a new record, do an Update on a different row, then come
back and do an update on the new row, I STILL get a concurrency exception.

All this led me to believe the stored procedure was not returning the
previous identity value on the Insert. Nope, that looks correct. I even
examined the parameters collection and trimmed that down to where I was just
comparing to ID and timestamp in the SP, with the same exception.


**********************************************
Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
BindingContext(ds.Tables("dtOilLeases")).EndCurrentEdit()
Dim strSQLServer As New SqlConnection(strConnString)
strSQLServer.Open()
Try
Dim da As New SqlDataAdapter
da.UpdateCommand = OilLeaseUpdate()
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters
da.Update(ds, "dtOilLeases")
Catch e_InsertException As System.Exception
MsgBox(e_InsertException.Message)
Throw e_InsertException
Finally
strSQLServer.Close()
End Try
End Sub

Private Function OilLeaseUpdate() As SqlCommand
Dim strSQLServer As New SqlConnection(strConnString)
Dim cmd As New SqlCommand("OilLeaseUpdate", strSQLServer)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As SqlParameterCollection = cmd.Parameters
pc.Add(New System.Data.SqlClient.SqlParameter("@OilFieldID",
System.Data.SqlDbType.Int, 4, "OilFieldID"))
pc.Add(New System.Data.SqlClient.SqlParameter("@OilLeaseName",
System.Data.SqlDbType.VarChar, 50, "OilLeaseName"))
pc.Add(New System.Data.SqlClient.SqlParameter("@OilLeaseLocation",
System.Data.SqlDbType.VarChar, 50, "OilLeaseLocation"))
pc.Add(New System.Data.SqlClient.SqlParameter("@Original_OilLeaseID",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False,
CType(0, Byte), CType(0, Byte), "OilLeaseID",
System.Data.DataRowVersion.Original, Nothing))
pc.Add(New System.Data.SqlClient.SqlParameter("@Original_Tstamp",
System.Data.SqlDbType.VarBinary, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Tstamp",
System.Data.DataRowVersion.Original, Nothing))
pc.Add(New System.Data.SqlClient.SqlParameter("@OilLeaseID",
System.Data.SqlDbType.Int, 4, "OilLeaseID"))
Return cmd
End Function

************************************************
ALTER PROCEDURE dbo.OilLeaseUpdate
(
@OilFieldID int,
@OilLeaseName varchar(50),
@OilLeaseLocation varchar(50),
@Original_OilLeaseID int,
@Original_Tstamp timestamp,
@OilLeaseID int
)
AS
SET NOCOUNT OFF;
UPDATE OilLeases
SET OilFieldID = @OilFieldID,
OilLeaseName = @OilLeaseName,
OilLeaseLocation = @OilLeaseLocation
WHERE (OilLeaseID = @Original_OilLeaseID)
AND (Tstamp = @Original_Tstamp OR @Original_Tstamp IS NULL AND Tstamp IS
NULL);
SELECT OilLeaseID, Tstamp, OilFieldID, OilLeaseName, OilLeaseLocation
FROM OilLeases WHERE (OilLeaseID = @OilLeaseID)

 

[Cor Ligthert [MVP]]

Earl,

I could not find it in your code, however maybe is this simple answer
enough.
An autoincrement key in the dataset is seldom the same as the key in the
database.

An update does update the database, not the datatable.

And therefore using an autoincrement key is in my opinion not the best
choise in ADONET.
Use a guid for the key.

Maybe this answer helps you to find your solution.

Cor

 

[Miha Markic [MVP C#]]

Hi Earl,

You are probably not reading timestamp of newly inserted record (for Insert
there is no check of concurrency, while for update there is).

 

[Jim Rand]

With the help of this group, I've worked through the issues. See the
Subject: GetChanges, Merge - returning autoincrement key.

The dataAdapter insert used this SQL:

INSERT INTO Customer (CustomerOldID, CompanyName, ContactName, ContactTitle,
Address, City, Region, PostalCode, Country, Phone, Fax) VALUES
(@CustomerOldID, @CompanyName, @ContactName, @ContactTitle, @Address, @City,
@Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CAST(TS AS
INT) AS TS FROM Customer WHERE (CustomerID = @@IDENTITY)

The dataset table treats the timestamp TS as an Int.

The dataset primary key is set as:

AutoIncrement: true
AutoIncrementSeed: -1
AutoIncrementStep: -1

The one gotcha which I never would have guessed in a million years was this
event handler to avoid duplicating rows during the merge:

private void sqlDACustomer_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert) e.Status =
UpdateStatus.SkipCurrentRow;
}

 

[Earl]

Thanks Cor. I do understand about the autoincrement issue. I'll dig into
that resolution some more today.