Description:
When the recordSet.Update function is called to update an adLongVarChar field,
the field is updated but the recordset is immediately lost. This is happening
with driver cursors, regardless of whether the cursor is opened optimistic or
pessimistic.
When the next update is called the below test function will exit with the
following error:
-2147467259:Query-based update failed because the row to update could not be
found.
This is also occuring the 3.51 driver and is entered as a seperate bug for
tracking purposes
How to repeat:
mysql> CREATE TABLE foo (C1 INT PRIMARY KEY, C2 TEXT, C3 INT);
mysql> INSERT INTO foo VALUES (1, 'FOO', 55), (2, 'BAR', 66);
Sub LONGTEXTBUG()
On Error GoTo EH
Dim strSQL As String
Dim strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim fld As Field
Dim fldName As String
Dim lngType As Long
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
strSQL = "SELECT * FROM foo"
' strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
' "SERVER=localhost;DATABASE=ado" & _
' ";USER=root;PASSWORD=mypass;OPTION=35;"
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=3;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseServer
connection1.Open
' open recordset
recset1.Open strSQL, connection1, adOpenForwardOnly, _
adLockOptimistic, adCmdText
If Not recset1.EOF Then
recset1.MoveFirst
End If
If recset1.Supports(adUpdate) Then
While Not recset1.EOF
For Each fld In recset1.Fields
lngType = CLng(fld.Type)
fldName = fld.Name
If lngType = adLongVarChar Then
fld.Value = "test"
recset1.Update
End If
If lngType = adInteger And fldName <> "C1" Then
fld.Value = 99
recset1.Update ' <----- fails here!
End If
Next
recset1.MoveNext
Wend
End If
recset1.Close
Set recset1 = Nothing
connection1.Close
Set connection1 = Nothing
Exit Sub
EH:
msgbox Err.Number & ":" & Err.Description
MsgBox "failed on column " & fldName
End Sub
Description: When the recordSet.Update function is called to update an adLongVarChar field, the field is updated but the recordset is immediately lost. This is happening with driver cursors, regardless of whether the cursor is opened optimistic or pessimistic. When the next update is called the below test function will exit with the following error: -2147467259:Query-based update failed because the row to update could not be found. This is also occuring the 3.51 driver and is entered as a seperate bug for tracking purposes How to repeat: mysql> CREATE TABLE foo (C1 INT PRIMARY KEY, C2 TEXT, C3 INT); mysql> INSERT INTO foo VALUES (1, 'FOO', 55), (2, 'BAR', 66); Sub LONGTEXTBUG() On Error GoTo EH Dim strSQL As String Dim strConn As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim fld As Field Dim fldName As String Dim lngType As Long Set connection1 = New ADODB.Connection Set recset1 = New ADODB.Recordset strSQL = "SELECT * FROM foo" ' strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _ ' "SERVER=localhost;DATABASE=ado" & _ ' ";USER=root;PASSWORD=mypass;OPTION=35;" strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;OPTION=3;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseServer connection1.Open ' open recordset recset1.Open strSQL, connection1, adOpenForwardOnly, _ adLockOptimistic, adCmdText If Not recset1.EOF Then recset1.MoveFirst End If If recset1.Supports(adUpdate) Then While Not recset1.EOF For Each fld In recset1.Fields lngType = CLng(fld.Type) fldName = fld.Name If lngType = adLongVarChar Then fld.Value = "test" recset1.Update End If If lngType = adInteger And fldName <> "C1" Then fld.Value = 99 recset1.Update ' <----- fails here! End If Next recset1.MoveNext Wend End If recset1.Close Set recset1 = Nothing connection1.Close Set connection1 = Nothing Exit Sub EH: msgbox Err.Number & ":" & Err.Description MsgBox "failed on column " & fldName End Sub