DarkMatter Posted May 28, 2008 Share Posted May 28, 2008 Does anyone know why I would be getting a 80020009 error on the $oConn.Execute statement? Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") Dim $oConn $oConn = ObjCreate("ADODB.Connection") $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=Updates.mdb") $oConn.Execute("INSERT INTO Updates (Update, Date, LogFile) VALUES ('Install Update DB', '" & $Date & "', 'NONE'") $oConn.Close() Func MyErrFunc() $HexNumber = Hex($oMyError.Number, 8) $strMSG = "Error Number: " & $HexNumber & @CRLF $strMsg &= "WinDescription: " & $oMyError.WinDescription & @CRLF $strMsg &= "Script Line: " & $oMyError.ScriptLine & @CRLF ConsoleWrite($strMSG) SetError(1) Endfunc The MyErrFunc() writes this to the console: Error Number: 80020009 WinDescription: ??. Script Line: 22 I have checked the database and those fields exist. [sub]Quantum mechanics: The dreams stuff is made of[/sub] Link to comment Share on other sites More sharing options...
weaponx Posted May 28, 2008 Share Posted May 28, 2008 Badly formatted SQL: Wrong: $oConn.Execute("INSERT INTO Updates (Update, Date, LogFile) VALUES ('Install Update DB', '" & $Date & "', 'NONE'") Correct: $oConn.Execute("INSERT INTO Updates (Update, Date, LogFile) VALUES ('Install Update DB', '" & $Date & "', 'NONE')") Link to comment Share on other sites More sharing options...
DarkMatter Posted May 28, 2008 Author Share Posted May 28, 2008 Just changed it to this: $oConn.Execute("INSERT INTO Updates (Update, Date, LogFile) VALUES ('Install Update DB', '" & $Date & "', 'NONE')") My error handler is still staying: Error Number: 80020009 WinDescription: ??. Script Line: 22 [sub]Quantum mechanics: The dreams stuff is made of[/sub] Link to comment Share on other sites More sharing options...
weaponx Posted May 28, 2008 Share Posted May 28, 2008 Start with a better COM Error handler: Func ComErrHandler() $HexNumber = Hex($oMyError.number, 8) MsgBox(0, "COM Error", "COM Error Details:" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & $HexNumber & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext _ ) SetError(1) EndFunc ;==>ComErrHandler You will will have to change "MyErrFunc" to "ComErrHandler" on line 1. Link to comment Share on other sites More sharing options...
DarkMatter Posted May 28, 2008 Author Share Posted May 28, 2008 Here is the Com Error Return: COM Error Details: err.description is: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. err.windescription: ㆽ矄. err.number is: 80020009 err.lastdllerror is: 0 err.scriptline is: 21 err.source is: Microsoft OLE DB Provider for ODBC Drivers err.helpfile is: err.helpcontext is: 0 [sub]Quantum mechanics: The dreams stuff is made of[/sub] Link to comment Share on other sites More sharing options...
DarkMatter Posted May 28, 2008 Author Share Posted May 28, 2008 I've looked over the SQL statement over and over and I'm not seeing any syntax errors in it. [sub]Quantum mechanics: The dreams stuff is made of[/sub] Link to comment Share on other sites More sharing options...
DaRam Posted May 28, 2008 Share Posted May 28, 2008 (edited) I've looked over the SQL statement over and over and I'm not seeing any syntax errors in it.Possible reasons....1. You have not declared or assigned a value to $date (as far as I can see).2. Try replacing the '" & $Date & "' part with '05/28/2008' and see if it solves the problem.3. What is the structure/field types of the table 'Updates' ? Edited May 28, 2008 by DaRam Link to comment Share on other sites More sharing options...
weaponx Posted May 28, 2008 Share Posted May 28, 2008 (edited) Where is $Date defined?EDIT: Also GEOSoft has an Access UDF which may provide some help:http://dundats.mvps.org/autoit/udf_code.aspx?udf=access Edited May 28, 2008 by weaponx Link to comment Share on other sites More sharing options...
DarkMatter Posted May 29, 2008 Author Share Posted May 29, 2008 Dim $Date = @Mon & "/" & @MDAY & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC There is the declaration for $Date. The table has 4 fields: ID - AutoNumber Update - Text Date - Text LogFile - Text [sub]Quantum mechanics: The dreams stuff is made of[/sub] Link to comment Share on other sites More sharing options...
DarkMatter Posted May 29, 2008 Author Share Posted May 29, 2008 I have it working by doing the following: expandcollapse popupGlobal Const $adLockPessimistic = 2 Global Const $adOpenStatic = 3 Global $oMyError = ObjEvent("AutoIt.Error","ComErrHandler") Dim $oConn Dim $Date = @Mon & "/" & @MDAY & "/" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC Dim $oRS $oConn = ObjCreate("ADODB.Connection") $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=Updates.mdb") $oRS = ObjCreate("ADODB.Recordset") $oRS.Open("SELECT * FROM Updates", $oConn, $adOpenStatic, $adLockPessimistic) $oRS.AddNew With $oRS .Fields("Update").Value = "Install Update DB" .Fields("Date").Value = $Date .Fields("LogFile").Value = "NONE" .Update .Close EndWith $oConn.Close() Func ComErrHandler() Local $HexNumber Local $ErrMsg $HexNumber = Hex($oMyError.number, 8) $ErrMsg = "COM ERROR DETAILS:" & @CRLF $ErrMsg &= "Description is: " & $oMyError.description & @CRLF $ErrMsg &= "WinDescription: " & StringReplace($oMyError.windescription,@CRLF, "") & @CRLF $ErrMsg &= "Number is: " & $HexNumber & @CRLF $ErrMsg &= "LastDLLError is: " & $oMyError.lastdllerror & @CRLF $ErrMsg &= "ScriptLine is: " & $oMyError.scriptline & @CRLF $ErrMsg &= "Source is: " & $oMyError.source & @CRLF $ErrMsg &= "Helpfile is: " & $oMyError.helpfile & @CRLF $ErrMsg &= "HelpContext is: " & $oMyError.helpcontext & @CRLF & @CRLF ConsoleWrite($ErrMsg) SetError(1) EndFunc I would still like to know why my Execute statement was failing... Anyone have any idea? [sub]Quantum mechanics: The dreams stuff is made of[/sub] Link to comment Share on other sites More sharing options...
ResNullius Posted May 29, 2008 Share Posted May 29, 2008 I would still like to know why my Execute statement was failing... Anyone have any idea?You have a couple of problems here due to the peculiarities of Access's implementation of SQL. 1) the field names "Update" & "Date" are reservered SQL terms and can't be used without double quoting them (single quotes won't work): INSERT INTO Updates ("Update","Date",LogFile) 2) back and forward slashes are treated as special characters in an Access SQL statement. I can't remember right now how you have to deal with them, but in your scenario, changing them to dashes works: INSERT INTO Updates ("Update","Date",LogFile) VALUES ('Install Update DB', '05-29-2008 06:43:05', 'NONE') Obviously you need to rework your code to accomodate the new mix of quotes Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now