concurrency violation Access data VS2005 datetime?

[SJG0526]

I'm getting concurrency violations on the following code:

            sSql = "UPDATE tblShippers SET ShipperKey = ?, " & _
                                        "Description = ?, " & _
                                        "MaxWeight = ?, " & _
                                        "TStamp = '" & Now() & "' " & _
                                        " WHERE ShipperUID = ? AND TStamp = ?"
            da.UpdateCommand = New OleDbCommand(sSql, Conn)
            da.UpdateCommand.Parameters.Add("@ShipperKey", OleDbType.Char, 10, "ShipperKey")
            da.UpdateCommand.Parameters.Add("@Description", OleDbType.Char, 100, "Description")
            da.UpdateCommand.Parameters.Add("@MaxWeight", OleDbType.Integer, 10, "MaxWeight")
            da.UpdateCommand.Parameters.Add("@oldShipperUID", OleDbType.Integer, 10, "ShipperUID").SourceVersion = DataRowVersion.Original
            da.UpdateCommand.Parameters.Add("@oldTStamp", OleDbType.Date)
            da.UpdateCommand.Parameters(4).SourceColumn = "TStamp"
            da.UpdateCommand.Parameters(4).SourceVersion = DataRowVersion.Original

I believe it has to do with the TStamp field but don't know how to fix it. TStamp is in Access 2003 as a datetime field.

Can anyone help me?

thks!

 

[chrissie1]

this should work better, never mix parameters with anything else and loose the ?.

sSql = "UPDATE tblShippers SET ShipperKey = @shipperkey, Description = @description, MaxWeight = @maxweight, TStamp = @TStamp WHERE ShipperUID = @shipperuid AND TStamp = @oldTstamp"
            da.UpdateCommand = New OleDbCommand(sSql, Conn)
            da.UpdateCommand.Parameters.Add("@ShipperKey", OleDbType.Char, 10)
            da.UpdateCommand.Parameters.Add("@Description", OleDbType.Char, 100)
            da.UpdateCommand.Parameters.Add("@MaxWeight", OleDbType.Integer, 10)
            da.UpdateCommand.Parameters.Add("@oldShipperUID", OleDbType.Integer, 10)
            da.UpdateCommand.Parameters.Add("@TStamp", OleDbType.Date)
            da.UpdateCommand.Parameters.Add("@oldTStamp", OleDbType.Date)
            da.UpdateCommand.Parameters(4).SourceColumn = "TStamp"
            da.UpdateCommand.Parameters(4).SourceVersion = DataRowVersion.Original

and where do you add the values to the parameters?? What are you trying to achieve?

Christiaan Baes
Belgium

"My new site" - Me

 

[SJG0526]

When using an Access database, you are supposed to use question marks and then the parameters must be added in order. The 1st question mark is replaced by the 1st parameter, etc.

This logic is to update the table only if the shipperUID and TStamp have not changed - in other words, it handles multi-user concurrency issues. However, even if I use your code and explicitly name the parameters, I still get concurrency violations. Its something to do with the TStamp and the way it is translated between Access and the .Net framework I think.

 

[chrissie1]

Normally a dataadapter would take care of that for you without having to code anything. Believe me I tested that.

Christiaan Baes
Belgium

"My new site" - Me