How to Lock a Table in SQL SERVER Using VB.NET

The following code shows how to Lock a Table in SQL SERVER Using VB.NET

Step1:Create Window based project and select language as VB.NET
Step2:Add a button control named CmdFill set its properties Text="Fill" and a datagrid named Datagrid1
Step3:Implement the following Code

Dim txn As SqlTransaction
Dim ds As New DataSet
Dim con As New SqlConnection("Data source=sourcename;Initial catalog=Northwind;uid=gopi;pwd=gopi")
Private Sub CmdFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdFill.Click
adp.Fill(ds, "Customers")
txn = con.BeginTransaction(IsolationLevel.RepeatableRead)
adp.SelectCommand.Transaction = txn
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Customers"
End Sub

Step 4: Run the program and click the Fill Button.Datagrid will be filled with customers table.
Step 5:Go to SQL server.Select query analyzer and run this query
update customers set contactname='Gopi' where contactname='Antonio Moreno'
Step 6:You will find the query executing with out doing any changes until you close your application form.


Author: Kapil Dhawan18 Jun 2008 Member Level: Gold   Points : 2

Nice piece of code
Thanks for sharing your knowledge with us.
I hope to see more good code from your side
This code will help lots of guys
Thanks to you

Author: Alwyn Duraisingh15 Jul 2010 Member Level: Gold   Points : 1

At this time we have to use nolock
select * from TableName(nolock)
this will show the records in the Table
even if it's locked by the Transaction.

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: