Jump to content
Sign in to follow this  
DarkMatter

ADODB & AutoIT

Recommended Posts

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.

[sub]Quantum mechanics: The dreams stuff is made of[/sub]

Share this post


Link to post
Share on other sites
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')")

Share this post


Link to post
Share on other sites
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


[sub]Quantum mechanics: The dreams stuff is made of[/sub]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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


[sub]Quantum mechanics: The dreams stuff is made of[/sub]

Share this post


Link to post
Share on other sites
DarkMatter

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]

Share this post


Link to post
Share on other sites
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 by DaRam

Share this post


Link to post
Share on other sites
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


[sub]Quantum mechanics: The dreams stuff is made of[/sub]

Share this post


Link to post
Share on other sites
DarkMatter

I have it working by doing the following:

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?


[sub]Quantum mechanics: The dreams stuff is made of[/sub]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.