Rowversion/Timestamp for checking change in row

[johnpapa]

I use VS 2012 and SQl Server 2012.
I first tried to use Timestamp and apparently it has been replaced by Rowversion. In SQL Server 2012 I cannot find a Rowversion datatype, but there is a Timestamp datatype.
Can someone suggest which datatype should be used (I guess Timestamp, since Rowversion is not there).
I want to use Rowversion/Timestamp to check whether a specific row has changed, after the row has been modified by the user. I use VB.net and would appreciate it if Someone could provide a real example.
Thanks, John

 

[diamondgeezer1974]

Hi John, I'm a bit of a newbie at all this VB.NET stuff but I can relate to this. I'm putting an app together that uses VS 2012 and SQL Server 2008.

I am also using a column called Rowversion but that uses the timestamp data type in SQL Server. It's a great way to see if rows have changed since you last retrieved them as the binary values in the column changes with each update.

Regards

DG

 

[johnpapa]

Hi DG,

Did you define in SQL Server a field with datatype Timestamp?

Also would be so kind to post sample code as to how you check Rowversion/Timestamp?

Many thanks,
John

 

[diamondgeezer1974]

Hi John,

Yes definitely Timestamp as the datatype in SQL Server.
Try creating a column in a test table with this datatype, and you'll see the field gets auto-populated with a binary value like '0x00000000000036CF' which will change each time the row is updated.

I'll have a look to see what VB.MET code I can dig up - gimme a min.

 

[johnpapa]

Many thanks DG,
Looking forward to your reply.
John

 

[diamondgeezer1974]

Ok looking at my project, the table adapter on my table to which my data was hel had an UPDATE command that looked like this. :

UPDATE [Main Log] SET [Account] = @Account, [Capability] = @Capability, [Type] = @Type, [Title] = @Title, [Department] = @Department, [Description] = @Description, [Action] = @Action, [Owner] = @Owner, [Logged By] = @Logged_By, [Action Date] = @Action_Date, [Date Logged] = @Date_Logged, [RAG Status] = @RAG_Status, [Roll Over] = @Roll_Over WHERE (([ID] = @Original_ID) AND ([Rowversion] = @Original_Rowversion));

I think the important bit is the WHERE clause here.... in that when the TableAdapter tries to do the update it checks to make sure the Rowversion is still equal to the original Rowversion when the data was retrieved. If the rowversion has changed then we know the row has been updated, and I think a Concurrency exception gets raised.

Which is when I then used this code to catch that error :

Try
Me.TableAdapterManager.UpdateAll(Me.SofteDataset)
Catch ex AsDBConcurrencyException
Using cnn AsNew SqlClient.SqlConnection(My.Settings.dbConnectionString)


Dim cmd AsNew SqlClient.SqlCommand("SELECT [Logged By] FROM [Main Log] WHERE ID = " & newRow.ID, cnn)

cnn.Open()


Dim ConflictUser AsString = cmd.ExecuteScalar


MessageBox.Show("Your changes cannot be saved as this record has just been changed by user '" & ConflictUser & "'. The data table will now be refreshed to reflect the current changes." + vbNewLine + vbNewLine + "Please try again.", "Cannot Save Changes - Your Data Conflicts With Another Users' Data", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

UpdateMainLogData()

DataUpdated =
True


EndUsing


EndTry

 

[johnpapa]

Thanks DG,
In my case I do updates on a per record basis. I do not allow the user to update more than 1 record at a time since this would cause potential conflicts. It is just simpler.
Regards,
John