用Recordset锁定后。其他人也就无法使用了。是不是?
>>要看锁类型而定。如果你用独占锁,而其他人也用了独占锁或更新锁,那他就无法访问此记录了,如果他不使用锁定,那么还是可以访问此记录的。rs.open sql conn...
rs.addnew
rs!("0")...
rs.update
如果两个人同时执行,那么会不会一个人出错。或者其他什么的?
>>对于Addnew方法,两个人都可以执行。如果用INSERT语句,怎么锁定啊?
>>用Insert语句,SQL Server自带会有一个事务。事务如何锁定?
>>开始事务后,任何对表的写入都会以锁定相应的记录(锁类型为xLock)。给你一个例子,把以下内容另存为Form.frm
==========================================================
VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 2730
ClientLeft = 60
ClientTop = 345
ClientWidth = 4740
LinkTopic = "Form1"
ScaleHeight = 2730
ScaleWidth = 4740
StartUpPosition = 3 '窗口缺省
Begin VB.CommandButton Command3
Caption = "ADO保守式锁定"
Height = 375
Left = 1560
TabIndex = 2
Top = 1680
Width = 1575
End
Begin VB.CommandButton Command2
Caption = "锁定一行"
Height = 375
Left = 1560
TabIndex = 1
Top = 1080
Width = 1575
End
Begin VB.CommandButton Command1
Caption = "锁定一个表"
Height = 375
Left = 1560
TabIndex = 0
Top = 480
Width = 1575
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'==================================================================
'注意: 只有建了主键的表才能锁定记录
'
'建立主键的方法:
'1. 打开企业管理器
'2. 用鼠标右击表名,点击[设计表]菜单
'3. 选定要建立主键的字段名,点击工具条上的钥匙图标就行了
'4. 注意: 建立主键的字段在表中不能有重复的值
'5. 提示: 可以用自动编号自段作为主键(字段设为int型,把它的identity属性设为yes)
'
'我测试时用到的表(test)的表结构:
' id int (自动编号,主键)
' groupname varchar(50)
' caption varchar(50)
'==================================================================Private cn As New ADODB.Connection
Private rs As New ADODB.Recordset
Private Sub Form_Load()
cn.ConnectionString = "Driver={SQL Server};Server=Develop;Uid=SA;Pwd=;Database=Test"
cn.Open
cn.Execute "set lock_timeout 1" '设置锁定超时的时间为1秒
End Sub
Private Sub Form_UnLoad(Cancel As Integer)
On Error Resume Next
cn.Close
Set cn = Nothing
Set Form1 = Nothing
End Sub
Private Sub Command1_Click()
Dim blnLocked As Boolean
On Error Resume Next
If Command1.Caption = "锁定一个表" Then
cn.BeginTrans
cn.Execute "select * from smenu with (tablockx)"
Select Case Err.Number
Case 0
'提示: 这里不要立即提交事务,否则就释放锁定了
blnLocked = True
MsgBox "锁定成功!"
Case -2147217871, -2147217887, -2147217900
cn.RollbackTrans
MsgBox "不能锁定数据!", vbExclamation, "提示"
blnLocked = False
Case Else
cn.RollbackTrans
MsgBox Err.Description, vbExclamation, "提示"
Unload Me
End Select
If blnLocked Then
Command1.Caption = "释放锁定"
Command2.Enabled = False
Command3.Enabled = False
End If
Else
Command1.Caption = "锁定一个表"
Command2.Enabled = True
Command3.Enabled = True
cn.CommitTrans ' or cn.RollbackTrans(回滚,即取消事务)
End If
End Sub
Private Sub Command2_Click()
Dim blnLocked As Boolean
On Error Resume Next
If Command2.Caption = "锁定一行" Then
cn.BeginTrans
cn.Execute "select * from smenu with (xlock) where id=1"
Select Case Err.Number
Case 0
'提示: 这里不要立即提交事务,否则就释放锁定了
blnLocked = True
MsgBox "锁定成功!"
Case -2147217871, -2147217887, -2147217900
cn.RollbackTrans
MsgBox "不能锁定数据!", vbExclamation, "提示"
blnLocked = False
Case Else
cn.RollbackTrans
MsgBox Err.Description, vbExclamation, "提示"
Unload Me
End Select
If blnLocked Then
Command2.Caption = "释放锁定"
Command1.Enabled = False
Command3.Enabled = False
End If
Else
Command2.Caption = "锁定一行"
Command1.Enabled = True
Command3.Enabled = True
cn.CommitTrans ' or cn.RollbackTrans(回滚,即取消事务)
End If
End Sub
Private Sub Command3_Click()
Dim strResult As String
Dim blnLocked As Boolean
On Error Resume Next
If Command3.Caption = "ADO保守式锁定" Then
rs.CursorLocation = adUseServer
rs.LockType = adLockPessimistic
rs.Open "select * from smenu where id=1", cn, adOpenKeyset
strResult = rs(0).Value '对Recordset一进行读/写,就会锁定记录
Select Case Err.Number
Case 0 '锁定成功
blnLocked = True
MsgBox "锁定成功!"
Case -2147217900, -2147467259, 3704 '不能锁定数据
rs.Close
MsgBox "不能锁定数据!", vbExclamation, "提示"
blnLocked = False
Case 3021 'BOF or EOF
rs.Close
MsgBox "记录不存在!", vbExclamation, "提示"
blnLocked = False
Case Else
rs.Close
MsgBox Err.Description, vbExclamation, "提示"
Unload Me
End Select
If blnLocked Then
Command3.Caption = "释放锁定"
Command1.Enabled = False
Command2.Enabled = False
End If
Else
rs.Close
Command3.Caption = "ADO保守式锁定"
Command1.Enabled = True
Command2.Enabled = True
End If
End Sub
>>要看锁类型而定。如果你用独占锁,而其他人也用了独占锁或更新锁,那他就无法访问此记录了,如果他不使用锁定,那么还是可以访问此记录的。rs.open sql conn...
rs.addnew
rs!("0")...
rs.update
如果两个人同时执行,那么会不会一个人出错。或者其他什么的?
>>对于Addnew方法,两个人都可以执行。如果用INSERT语句,怎么锁定啊?
>>用Insert语句,SQL Server自带会有一个事务。事务如何锁定?
>>开始事务后,任何对表的写入都会以锁定相应的记录(锁类型为xLock)。给你一个例子,把以下内容另存为Form.frm
==========================================================
VERSION 5.00
Begin VB.Form Form1
Caption = "Form1"
ClientHeight = 2730
ClientLeft = 60
ClientTop = 345
ClientWidth = 4740
LinkTopic = "Form1"
ScaleHeight = 2730
ScaleWidth = 4740
StartUpPosition = 3 '窗口缺省
Begin VB.CommandButton Command3
Caption = "ADO保守式锁定"
Height = 375
Left = 1560
TabIndex = 2
Top = 1680
Width = 1575
End
Begin VB.CommandButton Command2
Caption = "锁定一行"
Height = 375
Left = 1560
TabIndex = 1
Top = 1080
Width = 1575
End
Begin VB.CommandButton Command1
Caption = "锁定一个表"
Height = 375
Left = 1560
TabIndex = 0
Top = 480
Width = 1575
End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'==================================================================
'注意: 只有建了主键的表才能锁定记录
'
'建立主键的方法:
'1. 打开企业管理器
'2. 用鼠标右击表名,点击[设计表]菜单
'3. 选定要建立主键的字段名,点击工具条上的钥匙图标就行了
'4. 注意: 建立主键的字段在表中不能有重复的值
'5. 提示: 可以用自动编号自段作为主键(字段设为int型,把它的identity属性设为yes)
'
'我测试时用到的表(test)的表结构:
' id int (自动编号,主键)
' groupname varchar(50)
' caption varchar(50)
'==================================================================Private cn As New ADODB.Connection
Private rs As New ADODB.Recordset
Private Sub Form_Load()
cn.ConnectionString = "Driver={SQL Server};Server=Develop;Uid=SA;Pwd=;Database=Test"
cn.Open
cn.Execute "set lock_timeout 1" '设置锁定超时的时间为1秒
End Sub
Private Sub Form_UnLoad(Cancel As Integer)
On Error Resume Next
cn.Close
Set cn = Nothing
Set Form1 = Nothing
End Sub
Private Sub Command1_Click()
Dim blnLocked As Boolean
On Error Resume Next
If Command1.Caption = "锁定一个表" Then
cn.BeginTrans
cn.Execute "select * from smenu with (tablockx)"
Select Case Err.Number
Case 0
'提示: 这里不要立即提交事务,否则就释放锁定了
blnLocked = True
MsgBox "锁定成功!"
Case -2147217871, -2147217887, -2147217900
cn.RollbackTrans
MsgBox "不能锁定数据!", vbExclamation, "提示"
blnLocked = False
Case Else
cn.RollbackTrans
MsgBox Err.Description, vbExclamation, "提示"
Unload Me
End Select
If blnLocked Then
Command1.Caption = "释放锁定"
Command2.Enabled = False
Command3.Enabled = False
End If
Else
Command1.Caption = "锁定一个表"
Command2.Enabled = True
Command3.Enabled = True
cn.CommitTrans ' or cn.RollbackTrans(回滚,即取消事务)
End If
End Sub
Private Sub Command2_Click()
Dim blnLocked As Boolean
On Error Resume Next
If Command2.Caption = "锁定一行" Then
cn.BeginTrans
cn.Execute "select * from smenu with (xlock) where id=1"
Select Case Err.Number
Case 0
'提示: 这里不要立即提交事务,否则就释放锁定了
blnLocked = True
MsgBox "锁定成功!"
Case -2147217871, -2147217887, -2147217900
cn.RollbackTrans
MsgBox "不能锁定数据!", vbExclamation, "提示"
blnLocked = False
Case Else
cn.RollbackTrans
MsgBox Err.Description, vbExclamation, "提示"
Unload Me
End Select
If blnLocked Then
Command2.Caption = "释放锁定"
Command1.Enabled = False
Command3.Enabled = False
End If
Else
Command2.Caption = "锁定一行"
Command1.Enabled = True
Command3.Enabled = True
cn.CommitTrans ' or cn.RollbackTrans(回滚,即取消事务)
End If
End Sub
Private Sub Command3_Click()
Dim strResult As String
Dim blnLocked As Boolean
On Error Resume Next
If Command3.Caption = "ADO保守式锁定" Then
rs.CursorLocation = adUseServer
rs.LockType = adLockPessimistic
rs.Open "select * from smenu where id=1", cn, adOpenKeyset
strResult = rs(0).Value '对Recordset一进行读/写,就会锁定记录
Select Case Err.Number
Case 0 '锁定成功
blnLocked = True
MsgBox "锁定成功!"
Case -2147217900, -2147467259, 3704 '不能锁定数据
rs.Close
MsgBox "不能锁定数据!", vbExclamation, "提示"
blnLocked = False
Case 3021 'BOF or EOF
rs.Close
MsgBox "记录不存在!", vbExclamation, "提示"
blnLocked = False
Case Else
rs.Close
MsgBox Err.Description, vbExclamation, "提示"
Unload Me
End Select
If blnLocked Then
Command3.Caption = "释放锁定"
Command1.Enabled = False
Command2.Enabled = False
End If
Else
rs.Close
Command3.Caption = "ADO保守式锁定"
Command1.Enabled = True
Command2.Enabled = True
End If
End Sub
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货