Description:
The code below reads a bit field from one record, and assigns that same value to the bit
field of a different record and attempts to update the record.
In the case of a record set opened as adUseClient the error received on update is:
"Row cannot be located for updating. Some values may have been changed since it was last
read. IDispatch error #3128"
In the case of adUseServer the error received on update is:
"Query-based update failed because the row to update could not be found."
How to repeat:
SETUP
CREATE TABLE testtable (C1 INT PRIMARY KEY AUTO_INCREMENT, C2 BIT(28))
INSERT INTO testtable VALUES (1, b'11',0), (2,b'1000010010001101111010011')
Sub Test()
Dim strSQL, oConn, oRs, fld, arrBin
Dim testval, bPass, arrTest(2,3), b
bPass = cBool(True)
Set oConn = CreateObject("ADODB.Connection")
Set oRS = CreateObject("ADODB.RecordSet")
strSQL = "SELECT * FROM testtable"
MakeArray 0,0,0,3,arrTest,0
MakeArray 1,9,27,211,arrTest,1
' open connection
oConn.ConnectionString = connstr
oConn.CursorLocation = adUseClient
oConn.Open
' open record set
oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText
' read the bit field from the 2nd record, write it back to the 1st one
ors.MoveNext
arrBin = oRs.Fields("C2").Value
oRs.Close
oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText
oRs.Fields("C2").Value = arrBin
oRs.Update
' Verify value against original test values array
oRs.Close
oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText
Set fld = oRs.Fields("C2")
arrBin = fld.Value
For b = 1 To LenB(arrBin)
testval = AscB(MidB(arrBin, b, 1))
If testval <> arrTest(1,b-1) Then
bPass = cBool(False)
End If
Next
Call Cleanup (oConn, oRs)
End Sub
Sub MakeArray(a, b, c, d, byref arr, i)
arr(i,0)=a
arr(i,1)=b
arr(i,2)=c
arr(i,3)=d
End Sub
Description: The code below reads a bit field from one record, and assigns that same value to the bit field of a different record and attempts to update the record. In the case of a record set opened as adUseClient the error received on update is: "Row cannot be located for updating. Some values may have been changed since it was last read. IDispatch error #3128" In the case of adUseServer the error received on update is: "Query-based update failed because the row to update could not be found." How to repeat: SETUP CREATE TABLE testtable (C1 INT PRIMARY KEY AUTO_INCREMENT, C2 BIT(28)) INSERT INTO testtable VALUES (1, b'11',0), (2,b'1000010010001101111010011') Sub Test() Dim strSQL, oConn, oRs, fld, arrBin Dim testval, bPass, arrTest(2,3), b bPass = cBool(True) Set oConn = CreateObject("ADODB.Connection") Set oRS = CreateObject("ADODB.RecordSet") strSQL = "SELECT * FROM testtable" MakeArray 0,0,0,3,arrTest,0 MakeArray 1,9,27,211,arrTest,1 ' open connection oConn.ConnectionString = connstr oConn.CursorLocation = adUseClient oConn.Open ' open record set oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText ' read the bit field from the 2nd record, write it back to the 1st one ors.MoveNext arrBin = oRs.Fields("C2").Value oRs.Close oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText oRs.Fields("C2").Value = arrBin oRs.Update ' Verify value against original test values array oRs.Close oRS.Open strSQL, oConn, adOpenKeyset, adLockOptimistic, adCmdText Set fld = oRs.Fields("C2") arrBin = fld.Value For b = 1 To LenB(arrBin) testval = AscB(MidB(arrBin, b, 1)) If testval <> arrTest(1,b-1) Then bPass = cBool(False) End If Next Call Cleanup (oConn, oRs) End Sub Sub MakeArray(a, b, c, d, byref arr, i) arr(i,0)=a arr(i,1)=b arr(i,2)=c arr(i,3)=d End Sub