Description:
When I try to set the value of a string type field to this:
vbSingleQuote = Chr(39)
strTest = vbSingleQuote & "test" & vbSingleQuote
fld.Value = strTest
recset.Update
The update fails to occur. I'm testing below with a field of type CHAR(10) but it seems to be the same for any string type field. This same test works fine in MyODBC 3.51.14
It doesn't seem to matter where in the string the single quote occurs, so this will fail the same way...
strTest = "foo" & vbSingleQuote & "test" & vbSingleQuote & "bar"
No error is generated, it just doesn't update the server.
How to repeat:
SETUP:
mysql> create database ado;
mysql> drop table if exists ado_test;
mysql> create table ado_test (C1 INT primary key, C2 CHAR(10));
mysql> insert into ado_test values (1, 'FOO'), (2, 'BAR');
VB CODE:
Private Sub Test()
On Error GoTo EH
Dim strSQL, strConn, vbSingleQuote, strTest As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim fld As Field
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
vbSingleQuote = Chr(39)
strTest = vbSingleQuote & "test" & vbSingleQuote
MsgBox strTest
strSQL = "SELECT * FROM ado_test"
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=3;"
' strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;DATABASE=ado" & _
";USER=root;PASSWORD=mypass;OPTION=3;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseClient
connection1.Open
recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText
recset1.MoveFirst
While Not recset1.EOF
For Each fld In recset1.Fields
If fld.Name = "C2" Then
fld.Value = strTest
recset1.Update
End If
Next
recset1.MoveNext
Wend
Exit Sub
EH:
Debug.Print Err.Description & " " & Err.Number & vbNewLine
End Sub
Description: When I try to set the value of a string type field to this: vbSingleQuote = Chr(39) strTest = vbSingleQuote & "test" & vbSingleQuote fld.Value = strTest recset.Update The update fails to occur. I'm testing below with a field of type CHAR(10) but it seems to be the same for any string type field. This same test works fine in MyODBC 3.51.14 It doesn't seem to matter where in the string the single quote occurs, so this will fail the same way... strTest = "foo" & vbSingleQuote & "test" & vbSingleQuote & "bar" No error is generated, it just doesn't update the server. How to repeat: SETUP: mysql> create database ado; mysql> drop table if exists ado_test; mysql> create table ado_test (C1 INT primary key, C2 CHAR(10)); mysql> insert into ado_test values (1, 'FOO'), (2, 'BAR'); VB CODE: Private Sub Test() On Error GoTo EH Dim strSQL, strConn, vbSingleQuote, strTest As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim fld As Field Set connection1 = New ADODB.Connection Set recset1 = New ADODB.Recordset vbSingleQuote = Chr(39) strTest = vbSingleQuote & "test" & vbSingleQuote MsgBox strTest strSQL = "SELECT * FROM ado_test" strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;OPTION=3;" ' strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;DATABASE=ado" & _ ";USER=root;PASSWORD=mypass;OPTION=3;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseClient connection1.Open recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText recset1.MoveFirst While Not recset1.EOF For Each fld In recset1.Fields If fld.Name = "C2" Then fld.Value = strTest recset1.Update End If Next recset1.MoveNext Wend Exit Sub EH: Debug.Print Err.Description & " " & Err.Number & vbNewLine End Sub