| Bug #2071 | Error updating data using ADODB recordset | ||
|---|---|---|---|
| Submitted: | 10 Dec 2003 0:11 | Modified: | 30 May 2013 10:02 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | ODBC 3.51.06 | OS: | Windows (Windows 98) |
| Assigned to: | CPU Architecture: | Any | |
[13 Dec 2003 19:11]
Michael Widenius
Could you please provide an MyODBC debug log when this problem happens ? (How to do this is described in the MyODBC readme file)
[23 Jan 2004 8:55]
MySQL Verification Team
Could you please can to use the MYODBC debug for to create the log files and to add them using here the tab Files ?. Thanks in advance
[26 Feb 2004 0:55]
Nabil Barakat
It might be that f.Name is a MySql reserved name.
The following code will create an Insert SQL Statement based on the recordset enclosing the fieldnames with ``:
For J = 0 To RS.Fields.Count - 1
FLDN = FLDN & "`" & RS.Fields(J).Name & "`,"
If RS.Fields(J).Type = adBoolean Then
FldV = FldV & RS(J).Value & ","
ElseIf RS.Fields(J).Type = 202 Or RS.Fields(J).Type = 203 Then
'String Type --- Need to check for escape codes
If IsNull(RS(J).Value) = False Then
TFld = RS(J).Value
TFld = Replace(TFld, "\", "\\")
TFld = Replace(TFld, "'", "\'")
TFld = Replace(TFld, Chr(34), "\" & Chr(34))
TFld = Replace(TFld, vbNewLine, "\n")
TFld = Replace(TFld, vbCr, "\r")
TFld = Replace(TFld, vbNullChar, "\0")
TFld = Replace(TFld, vbTab, "\t")
TFld = Replace(TFld, Chr(26), "\z")
TFld = Replace(TFld, "%", "\%")
TFld = Replace(TFld, "_", "\_")
FldV = FldV & "'" & TFld & "',"
Else
TFld = "''"
FldV = FldV & "'" & TFld & "',"
End If
ElseIf RS.Fields(J).Type = adDate Then
FldV = FldV & "'" & Format(RS(J).Value, "yyyy-mm-dd hh:mm:ss") & "',"
Else
If IsNull(RS(J).Value) = False Then
FldV = FldV & RS(J).Value & ","
Else
FldV = FldV & "0,"
End If
End If
Next J
FLDN = Left(FLDN, Len(FLDN) - 1)
FldV = Left(FldV, Len(FldV) - 1)
cnMy.Execute "Insert Into `" & TBL.Name & "` (" & FLDN & ") Values (" & FldV & ");"
Hope it helps.
[23 Jul 2004 10:34]
[ name withheld ]
I think I have figured out the problem. The difference between Windows 98 and 2000 & XP is the drivers being used to access the data. I tried using the mdac_typ.exe v2.7 after downloading it from the Microsoft web site and that seemed to fix the problem. Thanks for all yor help.
[23 Jul 2004 10:34]
[ name withheld ]
I think I have figured out the problem. The difference between Windows 98 and 2000 & XP is the drivers being used to access the data. I tried using the mdac_typ.exe v2.7 after downloading it from the Microsoft web site and that seemed to fix the problem. Thanks for all your help.
[6 Nov 2004 5:37]
Howard Joven Macapallag
i don't have any comment but your discussions helped me a lot. it solved my problem. thanks
[30 May 2013 10:02]
Bogdan Degtyariov
Thank you for your bug report. This issue has already been fixed in the release version of that product, which you can download at http://www.mysql.com/downloads/
Description: When I try to update a mySQL 4.x database using ADODB recordsets (using Visual Basic 6.0) we get an error message stating "error near SQL .<Table Name>..." However, the same code works just fine on Windows 2000 / XP. I am reproducing a piece of the code as under: rsTO.AddNew For Each f In rsFrom.Fields rsTO(f.Name).Value = rsFrom(f.Name).Value Next rsTO.Update I get the error on rsTO.update. Please note I am using InnoDB transactional database using ODBC 3.51.06. I had noticed a similar problem reported on your mailing list stating that on Windows 2K/XP the log shows <database>.<tablename> while on Windows 98 it shows <database>..<tablename> - I gues we share a common problem. I shall be pleased to supply more information if so deried by you. Please advise. Regards, Sundeep Mahensaria integrators@mantraonline.com How to repeat: The following code sample may help you in reproducing the problem: rsTO.AddNew For Each f In rsFrom.Fields rsTO(f.Name).Value = rsFrom(f.Name).Value Next rsTO.Update I get the error on rsTO.update.