ADODB & AutoIT

[DarkMatter]

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.

[weaponx]

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')")

[DarkMatter]

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

[weaponx]

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.

[DarkMatter]

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

[DarkMatter]

I've looked over the SQL statement over and over and I'm not seeing any syntax errors in it.

[DaRam]

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

[weaponx]

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

[DarkMatter]

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

[DarkMatter]

I have it working by doing the following:

expandcollapse popup

Global 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?

[ResNullius]

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