多用户更新sql2000同一个表时出错, “运行时错误 -2147467259 [Microsoft][ODBC SQL Server Driver][SQL Server]"
但是,我一倆个用户就没有问题。附程式:
Private Sub AutoInput1_Click()Dim LineIn(1000000), NDS_in(1000000), ChipID_in(1000000), FDate_in(1000000) As String
Dim Date_in As String
Dim IDi, dID, ID_out, wID As Long
Dim wLineIn As String
Dim N, M, Len_in As Integer
Dim ReadData, ReadHead As String
Const Delete1 = "----"'Dim Week1, Week4, Week1_1 As String
Dim wNDS, wChipID, wDate, FwDate As String
Dim Date1, Date2, FDate1, FDate2, CompareDate As Variant
'*************** For auto input test ************************
Const TNDSh = "453100000"
Dim NDSn As Long
Dim iReadData, Test1, Tstart As Long
'***************************************************'NDSin1.Visible = False
Label1.Visible = True
IDi = 0'Status1.Caption = "Input Data"
'*************** auto input ************************
Tstart = Trim(Val(Start1))
iReadData = 10000000 + Tstart 'for test
For IDi = 0 To Val(txtStop1) - 1 'for test
iReadData = iReadData + 1
NDS_in(IDi) = TNDSh & Trim(Str(iReadData))'***************************************************
NDS4 = NDS_in(IDi)
PN3 = Str(iReadData)
HD5 = Str(iReadData)
DoEvents
Text2 = IDi
Save2
Next
End SubPrivate Sub Save2()
Dim UpdateString As String
On Error GoTo SQLError
Set RSTest1 = New ADODB.Recordset
StrSql1 = "select NDS_No, HardDisk_No,Pico_No, HD_date from Pallet1 Where NDS_No like " & "'" & NDS4 & "'"
RSTest1.Open StrSql1, Conn1, adOpenForwardOnly
Set DataGrid1.DataSource = Nothing
Set DataGrid1.DataSource = RSTest1
With RSTest1
If .RecordCount = 1 Then '这NDS_No在另一个用户已输入
If IsNull(!HardDisk_No) Or IsNull(!Pico_No) Then
Conn1.Execute ("Update Pallet1 set HardDisk_No =" & HD5 & ", Pico_No =" & PN3 & " Where NDS_No = '" & NDS4 & "'")
‘==========================================
' Conn1.BeginTrans
' .AddNew
' !NDS_No = NDS4
' !HardDisk_No = HD5
' !HD_Date = Format(Now, "ddddd ttttt")
' !Pico_No = PN3
' RS2.Update
' Conn1.CommitTrans
’=======================================
Else
msg = "这NDS码" & NDS4 & " 有硬盘"
title = "DOUBLE"
return_value = MsgBox(msg, 16, title)
End If
' End With
'RS2.Close
'Set RS2 = Nothing
Else
msg = "没有这NDS码" & NDS4
title = "DOUBLE"
return_value = MsgBox(msg, 16, title)
End If
End With
RSTest1.Close
Set RSTest1 = Nothing
Cancel1.Visible = True ReadBox = ""
NDS_Have = 0
HD_Have = 0
PC_Have = 0
Ch_letter = 0
NDS4 = ""
HD5 = ""
PN3 = "" Label8.Caption = "请输入NDS码:"
DoEvents
ReadBox.SetFocus
'================================
Exit SubSQLError:
Dim sError As String
sError = "Error Number" & Err.Number & vbCrLf
sError = sError & Err.Description
MsgBox (sError)
If Err.Number = -2147467259 Then
msg = "不能连接主机(SERVER)!请通知 TE。"
title = "ERROR"
return_value = MsgBox(msg, 16, title)
End
End If
End Sub
但是,我一倆个用户就没有问题。附程式:
Private Sub AutoInput1_Click()Dim LineIn(1000000), NDS_in(1000000), ChipID_in(1000000), FDate_in(1000000) As String
Dim Date_in As String
Dim IDi, dID, ID_out, wID As Long
Dim wLineIn As String
Dim N, M, Len_in As Integer
Dim ReadData, ReadHead As String
Const Delete1 = "----"'Dim Week1, Week4, Week1_1 As String
Dim wNDS, wChipID, wDate, FwDate As String
Dim Date1, Date2, FDate1, FDate2, CompareDate As Variant
'*************** For auto input test ************************
Const TNDSh = "453100000"
Dim NDSn As Long
Dim iReadData, Test1, Tstart As Long
'***************************************************'NDSin1.Visible = False
Label1.Visible = True
IDi = 0'Status1.Caption = "Input Data"
'*************** auto input ************************
Tstart = Trim(Val(Start1))
iReadData = 10000000 + Tstart 'for test
For IDi = 0 To Val(txtStop1) - 1 'for test
iReadData = iReadData + 1
NDS_in(IDi) = TNDSh & Trim(Str(iReadData))'***************************************************
NDS4 = NDS_in(IDi)
PN3 = Str(iReadData)
HD5 = Str(iReadData)
DoEvents
Text2 = IDi
Save2
Next
End SubPrivate Sub Save2()
Dim UpdateString As String
On Error GoTo SQLError
Set RSTest1 = New ADODB.Recordset
StrSql1 = "select NDS_No, HardDisk_No,Pico_No, HD_date from Pallet1 Where NDS_No like " & "'" & NDS4 & "'"
RSTest1.Open StrSql1, Conn1, adOpenForwardOnly
Set DataGrid1.DataSource = Nothing
Set DataGrid1.DataSource = RSTest1
With RSTest1
If .RecordCount = 1 Then '这NDS_No在另一个用户已输入
If IsNull(!HardDisk_No) Or IsNull(!Pico_No) Then
Conn1.Execute ("Update Pallet1 set HardDisk_No =" & HD5 & ", Pico_No =" & PN3 & " Where NDS_No = '" & NDS4 & "'")
‘==========================================
' Conn1.BeginTrans
' .AddNew
' !NDS_No = NDS4
' !HardDisk_No = HD5
' !HD_Date = Format(Now, "ddddd ttttt")
' !Pico_No = PN3
' RS2.Update
' Conn1.CommitTrans
’=======================================
Else
msg = "这NDS码" & NDS4 & " 有硬盘"
title = "DOUBLE"
return_value = MsgBox(msg, 16, title)
End If
' End With
'RS2.Close
'Set RS2 = Nothing
Else
msg = "没有这NDS码" & NDS4
title = "DOUBLE"
return_value = MsgBox(msg, 16, title)
End If
End With
RSTest1.Close
Set RSTest1 = Nothing
Cancel1.Visible = True ReadBox = ""
NDS_Have = 0
HD_Have = 0
PC_Have = 0
Ch_letter = 0
NDS4 = ""
HD5 = ""
PN3 = "" Label8.Caption = "请输入NDS码:"
DoEvents
ReadBox.SetFocus
'================================
Exit SubSQLError:
Dim sError As String
sError = "Error Number" & Err.Number & vbCrLf
sError = sError & Err.Description
MsgBox (sError)
If Err.Number = -2147467259 Then
msg = "不能连接主机(SERVER)!请通知 TE。"
title = "ERROR"
return_value = MsgBox(msg, 16, title)
End
End If
End Sub
解决方案 »
- 监视指定盘符,文件夹,监视注册表,对修改的或者增加的的注册表键值时弹出确认对话框,允许,拒绝.生成log.
- datareport 的问题?????关于根据设定的条件来显示报表
- 退出程序时,关闭FTP总是死机
- 请教
- 谁能教教我这样的DLL用VB怎么写
- 我做的一个纯DHTM的游戏,htm格式的,大家来看原代码!!
- 苦恼呀,给别人打工的程序员!
- 开一个书与mp3的ftp有兴趣的上来看看,都是PDF格式的书请大家多多up
- 在WIN2000下tcpclient.SendData时会报错,为什么?
- 在设计中能不能设定DATAGRID的COLUMN列数?
- 求助高手解决一个VB加法同时运算的问题 (补充一下)
- 关于渐开线的检测
rs.Open "update biao set 字段='aa' where 字段2=1", cn, adOpenKeyset, adLockOptimistic
If .RecordCount = 1 Then
Set RS2 = New ADODB.Recordset
StrSql1 = "select NDS_No, HardDisk_No,Pico_No, HD_date from Pallet1 Where NDS_No like " & "'" & NDS4 & "'"
RS2.Open StrSql1, Conn1, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = Nothing
Set DataGrid1.DataSource = RS2
With RS2
If .RecordCount = 1 Then
Conn1.BeginTrans
!NDS_No = NDS4
!HardDisk_No = HD5
!HD_Date = Format(Now, "ddddd ttttt")
!Pico_No = PN3
RS2.Update
Conn1.CommitTrans
Else
msg = "这NDS码" & NDS4 & " 有硬盘"
title = "DOUBLE"
return_value = MsgBox(msg, 16, title)
End If
End With
RS2.Close
Set RS2 = Nothing
Else
msg = "没有这NDS码" & NDS4
title = "DOUBLE"
return_value = MsgBox(msg, 16, title)
End If
为什么不用事务更新,另外写表代码统一用下面的形式,试试
rs.Open "update biao set 字段='aa' where 字段2=1", cn, adOpenKeyset, adLockOptimistic
我用以下的方法, 结果一样。 如果只有一个用户就没有问题。
Set RSTest1 = New ADODB.Recordset
StrSql1 = "Update Pallet1 set HardDisk_No =" & HD5 & ", Pico_No =" & PN3 & " Where NDS_No = '" & NDS4 & "'"
RSTest1.Open StrSql1, Conn1, adOpenKeyset, adLockOptimistic
begin tran。
on error goto rollback
commit tran。:rollback
rollback tran
begin tran。
on error goto rollback
commit tran。 :rollback
rollback tran
'======================这只是不更新这个记录, 但其它用户被锁定这表而查询运行出错。
查询的时候不用加事务,只是在修改操作前后增加。
你指的“事务” 是那个指令呢? 可以在我的程式指出吗? 谢谢你
conn.BeginTrans
strsql = "insert into mytable ……
conn.Execute strsql
conn.CommitTrans
Exit SuberrDo:
conn.RollbackTrans
End Sub
你好, 谢谢你的代码,我不是要添加而是要更新记录。添加是不出错,两个以上用户同时更新不同的记录都出错 请帮忙。
谢谢你的帮忙,我现在就是用这个事务 “Conn1.BeginTrans ” 如下程式。我在测试时 2~6个用户大约2秒在同一个表更新一个不同的记录,每个用户都更新不同的记录,大概更新几百个记录就有用户出现“运行时错误 ‘-2147467259’”的问题。但如果一个用户就没有问题。请再帮忙。 RS2.Open StrSql1, Conn1, adOpenDynamic, adLockOptimistic
Set DataGrid1.DataSource = Nothing
Set DataGrid1.DataSource = RS2
With RS2
If .RecordCount = 1 Then
Conn1.BeginTrans
!NDS_No = NDS4
!HardDisk_No = HD5
!HD_Date = Format(Now, "ddddd ttttt")
!Pico_No = PN3
RS2.Update
Conn1.CommitTrans