MSSQL 2K中,需进行记录集更新!
用VB执行:select top 100 * from sms_mobile where sendflag=0后,更新记录集,sendflag=1,显示出错信息:3251 当前记录集不支持更新。这可能是提供程序的限制,也可能是选定锁定类型的限制。
但执行:select * from sms_mobile where sendflag=0后更新记录集,sendflag=1,无任何问题。
求救!
用VB执行:select top 100 * from sms_mobile where sendflag=0后,更新记录集,sendflag=1,显示出错信息:3251 当前记录集不支持更新。这可能是提供程序的限制,也可能是选定锁定类型的限制。
但执行:select * from sms_mobile where sendflag=0后更新记录集,sendflag=1,无任何问题。
求救!
while not rssql.eof
rssql.fields("sendflag").value=1
rssql.update
rssql.movenext
wend
Conn.execute("update 表 set 字段=值 where 条件")
//更新无误后,才进行读取记录的标志更换!
那也没有问题啊,可以用只读、只向前记录集,读取记录进行其他数据集的更新;
更新其他数据集后,记录下ID的范围,然后用Update语句一次更新
'Program Name: SMSMonitor
'Design: Michael Zhao, [email protected]
'Date: 2005-02-17'Part 1,frmMain
Private Sub cmdOp_Click(Index As Integer)
Select Case Index
Case 0
'hide
Case Else
'unload
'If MsgBox("Realy Quit?", vbYesNo + vbInformation, "Warning!") = vbNo Then
' Exit Sub
'End If
Unload Me
End Select
End SubPrivate Sub Form_Load()
With timerMonitor
.Interval = 2000
.Enabled = True
End With
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
timerMonitor.Enabled = False
If MsgBox("Realy Quit?", vbYesNo + vbInformation, "Warning!") = vbNo Then
Cancel = True
Exit Sub
End If
timerMonitor.Enabled = True
End SubPrivate Sub timerMonitor_Timer()
'MsgBox "Active"
Dim strSql As String
Dim strMdb As String
Dim cnSql As ADODB.Connection
Dim cnMdb As ADODB.Connection
Dim rsSql As ADODB.Recordset
Dim rsMdb As ADODB.Recordset
On Error Resume Next
Set cnSql = New ADODB.Connection
cnSql.Open SQLConnStr
If Err.Number <> 0 Then
Set cnSql = Nothing
Err.Clear
'MsgBox "cnSql"
Exit Sub
End If
’是这里有问题,去掉TOP 100,用全集的话,就不会更新记录时出错!
strSql = "Select Top 100 * From SMS_Send Where SendFlag='0'"
Set rsSql = New ADODB.Recordset
rsSql.Open strSql, cnSql, adOpenDynamic, adLockOptimistic
If Err.Number <> 0 Then
Set rsSql = Nothing
Set cnSql = Nothing
Err.Clear
'MsgBox "rsSql"
Exit Sub
End If
If rsSql.EOF And rsSql.BOF Then
Set rsSql = Nothing
Set cnSql = Nothing
'MsgBox "rsSql Empty!"
Exit Sub
End If
Set cnMdb = New ADODB.Connection
cnMdb.Open MDBConnStr
If Err.Number <> 0 Then
Set cnMdb = Nothing
Set rsSql = Nothing
Set cnSql = Nothing
'MsgBox Err.Number & "-" & Err.Description
Err.Clear
'MsgBox "cnMdb"
Exit Sub
End If
strMdb = "Select * from dfsdl where 1=0"
Set rsMdb = New ADODB.Recordset
rsMdb.Open strMdb, cnMdb, adOpenDynamic, adLockOptimistic
If Err.Number <> 0 Then
Set rsMdb = Nothing
Set cnMdb = Nothing
Set rsSql = Nothing
Set cnSql = Nothing
Err.Clear
'MsgBox "rsMdb"
Exit Sub
End If
Dim tmpMobile As String
Dim tmpContent As String
Dim tmpDeadTime As String
While Not rsSql.EOF
tmpMobile = rsSql.Fields("mobile").Value
tmpContent = rsSql.Fields("content").Value
tmpDeadTime = rsSql.Fields("deadtime").Value
rsSql.Fields("SendFlag").Value = 1
‘以下Update语名在查询包含TOP 100时会弹出错误
rsSql.Update
If Err.Number <> 0 Then
Set rsMdb = Nothing
Set cnMdb = Nothing
Set rsSql = Nothing
Set cnSql = Nothing
MsgBox "rsSql Update Error!" & Err.Number & "-" & Err.Description
Err.Clear
Exit Sub
End If
rsMdb.AddNew
rsMdb.Fields("mobile").Value = rsSql.Fields("mobile").Value
rsMdb.Fields("content").Value = rsSql.Fields("content").Value
rsMdb.Fields("deadtime").Value = rsSql.Fields("deadtime").Value
rsMdb.Update
If Err.Number <> 0 Then
Set rsMdb = Nothing
Set cnMdb = Nothing
Set rsSql = Nothing
Set cnSql = Nothing
MsgBox "rsMdb Update Error!"
Err.Clear
Exit Sub
End If
rsSql.MoveNext
Wend
End Sub
'Timer: timerMonitor
'Button: cmdOp
'SQLConnStr,MdbConnStr in modPublic