Microsoft KB Archive/309489

= HOW TO: Use a TimeStamp Column to Detect Update Collisions in ADO.NET with Visual Basic .NET =

Article ID: 309489

Article Last Modified on 9/3/2003

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q309489



For a Microsoft Visual C# version of this article, see 317095.

This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.SqlClient

IN THIS TASK
SUMMARY
 * Create the SQL Server Tables
 * Create the Visual Basic .NET Application
 * Test the Visual Basic .NET Application
 * Additional Information

REFERENCES



SUMMARY
This step-by-step article uses a Microsoft SQL Server TimeStamp field to detect update collisions in order to reduce the amount of data sent to the server. By default, the CommandBuilder object (both SQLClient and OLEDB) builds collision detection based on all field values. By using a custom UpdateCommand, we can specify only the columns that we wish to use.

back to the top

Create the SQL Server Tables
Use the following script to create the test table and insert some test records: CREATE TABLE [tblQ309489] ( [ID] int IDENTITY (1,1) NOT NULL, [CharData] varchar (10) NOT NULL, [TimeStampCol] timestamp NULL) ALTER TABLE [tblQ309489] WITH NOCHECK ADD CONSTRAINT [PK_tblQ309489] PRIMARY KEY ([ID]) INSERT INTO tblQ309489 ([CharData]) VALUES ('AAA') INSERT INTO tblQ309489 ([CharData]) VALUES ('BBB') INSERT INTO tblQ309489 ([CharData]) VALUES ('CCC') back to the top

Create the Visual Basic .NET Application
 Create a new Visual Basic .NET Windows Application. Add the following controls to the form from the Windows Forms tab in the toolbox:  Button: cmdUpdate. TextBoxes (3): txtID, txtCharData, and txtTimeStamp. (Set txtID and txtTimeStamp to ReadOnly to show that these fields cannot be updated.) DataGrid.   Double-click the form to go to the code window and then add the following code to the very top (above the &quot;Public Class Form1&quot; statement): Imports System.Data.SqlClient </li>  Add the following declarations to the class (under &quot;Inherits System.Windows.Forms.Form&quot;): Private con As New SqlConnection Private da As SqlDataAdapter Private ds As New DataSet Private daUpdateCommand As SqlCommand Private dt As DataTable </li>  Add the following code to the Form_Load event: con.ConnectionString = &quot;Server=(local);Database=pubs;Trusted_Connection=yes;&quot; con.Open da = New SqlDataAdapter(&quot;SELECT * FROM tblQ309489 ORDER BY ID&quot;, con) daUpdateCommand = New SqlCommand(&quot;UPDATE tblQ309489 SET CharData = @pCharData WHERE TimeStampCol = @pTimeStamp&quot;, _   da.SelectCommand.Connection) With daUpdateCommand 'This is the field that we are updating. .Parameters.Add(New SqlParameter(&quot;@pCharData&quot;, SqlDbType.VarChar, 10)) .Parameters(&quot;@pCharData&quot;).SourceVersion = DataRowVersion.Current .Parameters(&quot;@pCharData&quot;).SourceColumn = &quot;CharData&quot;

'We will use the TimeStamp to locate our row. .Parameters.Add(New SqlParameter(&quot;@pTimeStamp&quot;, SqlDbType.Binary)) .Parameters(&quot;@pTimeStamp&quot;).SourceVersion=DataRowVersion.Original .Parameters(&quot;@pTimeStamp&quot;).SourceColumn = &quot;TimeStampCol&quot; End With da.UpdateCommand = daUpdateCommand 'Fetch the data. da.FillSchema(ds, SchemaType.Source, &quot;tblQ309489&quot;) da.Fill(ds, &quot;tblQ309489&quot;) dt=ds.Tables(&quot;tblQ309489&quot;) 'Show the data in the text boxes. With dt.Rows(0) Me.txtID.Text = .Item(0) Me.txtCharData.Text =.Item(1) Me.txtTimeStamp.Text= .Item(2).ToString End With </li> Modify the ConnectionString property (the first line of code in step 5) to correspond to your SQL Server computer connection information. Make sure that you are connecting to the database where you ran the SQL script to create the test table.</li>  Add the following code to the cmdUpdate_Click event: dt.Rows(0)(&quot;CharData&quot;) = Me.txtCharData.Text Try da.Update(dt) MessageBox.Show(&quot;Update was successful&quot;) Catch dbException As System.Data.DBConcurrencyException MessageBox.Show(dbException.Message.ToString) Dim dsModified As DataSet DsModified = ds.GetChanges(DataRowState.Modified) DataGrid1.DataSource = dsModified.Tables(0) DataGrid1.CaptionText = &quot;Modified Rows&quot; ds.RejectChanges Catch genException As Exception MessageBox.Show(genException.Message & vbcrlf & genException.GetType.ToString) End Try con.Close </li></ol>

back to the top

Test the Visual Basic .NET Application

 * 1) To run the application (the data is fetched in the form_load event), click Start on the Debug menu.
 * 2) Go to Query Analyzer (or Enterprise Manager) and update the CharData field in record #1 to a new value.
 * 3) Return to the application and change CharData to a different value.
 * 4) Click the button on the form and note the exception.

back to the top

Additional Information
A TimeStamp field in SQL Server is a binary value that is updated every time that row is updated. This is guaranteed to be a unique value database-wide. The value is not related to the date and time that the changes occurred. For further information on TimeStamp fields, see the &quot;Using Special Data&quot; topic in SQL Server Books Online.

back to the top

<div class="references_section">