Discussion Article

[ Home | Contents | Search | Reply | Previous | Next ]

Subject:
How to Use SQL server Lock in Visual Basic
Name:
Mahesh Gupta
Email (spam proof):
Email the originator of this post
Date:
Thursday, August 12, 2004
Hits:
56
Discussion (Terms and Conditions):
i have created the multi user application i am using the Visual baisc 6.0 and SQL server 2000. plz tell me that how can use the SQL server lock in Visual Baisc. visual baisc locking not working proper the code developed is pasted below.give me the suggetion how to use locking in correct way. --------------------Module-------------Connection------------------------------ Public CONN As New ADODB.Connection Sub main() 'Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=pubs;Data Source=DEV CONN.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=pubs;Data Source=DEV" CONN.CommandTimeout = 30 CONN.ConnectionTimeout = 30 CONN.CursorLocation = adUseClient CONN.Open Form1.Show End Sub ---------------------------------------------------------------------------------- Dim RS As New ADODB.Recordset Dim RSNAVI As New ADODB.Recordset Dim TEMP As Integer Dim SQL As String Private Sub Command1_Click() RS.Open "Select max(rollno) from Student", CONN, adOpenForwardOnly, adLockOptimistic Me.Text1.Text = RS.Fields(0) + 1 Me.Text1.Enabled = False RS.Close Me.Command2.Enabled = False Me.Command3.Enabled = True RS.Open "Student", CONN, adOpenDynamic, adLockOptimistic RS.AddNew Me.Command1.Enabled = False TEMP = 0 End Sub Private Sub Command2_Click() TEMP = 1 Me.Command1.Enabled = False Me.Command2.Enabled = False Me.Command3.Enabled = True SQL = "select * from Student WITH (ROWLOCK) where RollNo = " & Me.Text1.Text CONN.BeginTrans CONN.Execute (SQL) 'RS.Open "select * from Student WITH (ROWLOCK) where RollNo = " & Me.Text1.Text, CONN, adOpenDynamic, adLockPessimistic End Sub Private Sub Command3_Click() SQL = "Update Student set Name = '" & Me.Text2.Text & "', Course = '" & Me.Text3.Text & "' where RollNo =" & Me.Text1.Text CONN.Execute (SQL) 'RS.Fields(0) = Me.Text1.Text 'RS.Fields(1) = Me.Text2.Text 'RS.Fields(2) = Me.Text3.Text 'RS.Update MsgBox "Record Sucessfully Added", vbInformation + vbOKOnly, "Add Alert" RSNAVI.Requery Me.Command2.Enabled = True Me.Command3.Enabled = False Me.Command1.Enabled = True If TEMP = 1 Then CONN.CommitTrans End If RSNAVI.Requery End Sub Private Sub Command4_Click() If RSNAVI.RecordCount <= 0 Then MsgBox "no record found." Exit Sub Else RSNAVI.MoveFirst Me.Text1.Text = RSNAVI.Fields(0) Me.Text2.Text = RSNAVI.Fields(1) Me.Text3.Text = RSNAVI.Fields(2) End If End Sub Private Sub Command5_Click() If RSNAVI.RecordCount <= 0 Then MsgBox "No Record Found." Exit Sub Else RSNAVI.MovePrevious If RSNAVI.BOF Then RSNAVI.MoveFirst Else Me.Text1.Text = RSNAVI.Fields(0) Me.Text2.Text = RSNAVI.Fields(1) Me.Text3.Text = RSNAVI.Fields(2) End If End If End Sub Private Sub Command6_Click() If RSNAVI.RecordCount <= 0 Then MsgBox "No Record Found." Exit Sub Else RSNAVI.MoveNext If RSNAVI.EOF Then RSNAVI.MoveLast Else Me.Text1.Text = RSNAVI.Fields(0) Me.Text2.Text = RSNAVI.Fields(1) Me.Text3.Text = RSNAVI.Fields(2) End If End If End Sub Private Sub Command7_Click() If RSNAVI.RecordCount <= 0 Then MsgBox "no record found." Exit Sub Else RSNAVI.MoveLast Me.Text1.Text = RSNAVI.Fields(0) Me.Text2.Text = RSNAVI.Fields(1) Me.Text3.Text = RSNAVI.Fields(2) End If End Sub Private Sub Form_Load() TEMP = 0 Me.Command3.Enabled = False RSNAVI.Open "Select * from student order by name", CONN, adOpenForwardOnly, adLockOptimistic If RSNAVI.RecordCount > 0 Then Me.Text1.Text = RSNAVI.Fields(0) Me.Text2.Text = RSNAVI.Fields(1) Me.Text3.Text = RSNAVI.Fields(2) End If End Sub Mahesh

Sponsored by the best FTP application on the internet: