如果真正在工程中用,请不要使用objRs.recordcount,除了要使用本地游标,经实践证明recordcount有时就是得不到正确的结果甚至出错。 一定要得到记录集数目,请使用 select count(*) from ... 的方法
假设conn为连接对象 conn.CursorLocation = adUseClient
算了,我还是帖出来给大家看吧。Public Sub UpdateInfor(Board)Dim objCon As ADODB.Connection Dim strSql As StringDim ctn, pcs, pcs1Set objCon = New ADODB.ConnectionIf objCon.State = adStateOpen Then objCon.Close
OpenDbconnection objCon, 1
If objCon.State = adStateOpen Then Dim objRs As ADODB.Recordset Set objRs = New ADODB.Recordset
strSql = "select * from scan_data where board='" & Board & "'" objRs.CursorLocation = adUseClient objRs.Open strSql, objCon, 3, 1
MsgBox objRs.RecordCount, vbInformation, "xx" If Not objRs.EOF And Not objRs.BOF Then ctn = objRs.RecordCount
For i = 0 To objRs.RecordCount - 1 pcs1 = CInt(Mid(objRs("box"), 5, 3)) pcs = CInt(pcs) + CInt(pcs1) objRs.MoveNext Next End If objRs.Close
objRs.Open "select board from infor where board = '" & Board & "'", objCon, 1, 1 If Not objRs.EOF And Not objRs.BOF Then objCon.Execute "update infor set ctn = '" & ctn & "', pcs = '" & pcs & "' where board = '" & Board & "'" Else objCon.Execute "insert into infor (ctn, pcs, board) values ('" & ctn & "', '" & pcs & "','" & Board & "')" End If objRs.Close Set objRs = Nothing Else MsgBox "数据库连接错误", vbInformation, "提示" Exit Sub End If objCon.Close Set objCon = Nothing End Sub
你的意思是指?我把数据导到SQL里,用查询分析器查出来的结果没有问题呀!一条都不少。
objRs.Open strSql, objCon, 1, 1
一定要得到记录集数目,请使用
select count(*) from ...
的方法
conn.CursorLocation = adUseClient
Dim strSql As StringDim ctn, pcs, pcs1Set objCon = New ADODB.ConnectionIf objCon.State = adStateOpen Then objCon.Close
OpenDbconnection objCon, 1
If objCon.State = adStateOpen Then
Dim objRs As ADODB.Recordset
Set objRs = New ADODB.Recordset
strSql = "select * from scan_data where board='" & Board & "'"
objRs.CursorLocation = adUseClient
objRs.Open strSql, objCon, 3, 1
MsgBox objRs.RecordCount, vbInformation, "xx"
If Not objRs.EOF And Not objRs.BOF Then
ctn = objRs.RecordCount
For i = 0 To objRs.RecordCount - 1
pcs1 = CInt(Mid(objRs("box"), 5, 3))
pcs = CInt(pcs) + CInt(pcs1)
objRs.MoveNext
Next
End If
objRs.Close
objRs.Open "select board from infor where board = '" & Board & "'", objCon, 1, 1
If Not objRs.EOF And Not objRs.BOF Then
objCon.Execute "update infor set ctn = '" & ctn & "', pcs = '" & pcs & "' where board = '" & Board & "'"
Else
objCon.Execute "insert into infor (ctn, pcs, board) values ('" & ctn & "', '" & pcs & "','" & Board & "')"
End If
objRs.Close
Set objRs = Nothing
Else
MsgBox "数据库连接错误", vbInformation, "提示"
Exit Sub
End If
objCon.Close
Set objCon = Nothing
End Sub
objRs.Open strSql, objCon, 3, 1
因为VB是一直往下执行的,如你insert 后立即 select ,VB发select时不会等你数据库insert一定成功才开始。因此你这样哪怕用count(*)恐怕也不能得到正确结果。
你可以在insert前求出记录数iCount,insert成功后传入参数iCount+1就可以代替recordcount了还是那句话,最好不要用recordcount
这个条件是否正确呀?