ODBC Object Fails WIth Certain Methods

[WestCBear]

Hi All,

 

I'm trying to connect to a MS SQL database through ODBC.  But the second line of code gives me an error. 

$oDatabase = objCreate("ADODB.Connection")
$oDatabase.Provider("SQLOLEDB")
$oDatabase.Open ("DSN=ABC;UID=Yes;Password=Sure")

When I take the second line out, I do not get an error message.   It doesn't seem to like the key word "Provider", as if it doesn't exist.  (I can find out the State of the object.  It returns that.)

Also, if I try something like...

$oDatabase.Execute("SELECT * FROM Table_Name")

...I also get the same error message.

The error message is "The requested action with this object has failed.:"

I feel like I'm missing an important step like including an external fle, or something along those lines.

Any help or a direction on where to find help would be appreciated.

Thank you!

WestCBear

[mLipok]

;~ https://www.autoitscript.com/forum/topic/180596-odbc-object-fails-with-certain-methods/#comment-1296503

Example()

Func Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
    $oDatabase = ObjCreate("ADODB.Connection")
    $oDatabase.Provider("SQLOLEDB")
    $oDatabase.Open("DSN=ABC;UID=Yes;Password=Sure")
EndFunc    ;==>Example

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc    ;==>_ErrFunc

Console output:

Spoiler

Quote

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "Z:\TOOLs\Macro\PROGRAMY\__TEST\TEST_ADO_180596.au3" /UserParams    
+>02:36:06 Starting AutoIt3Wrapper v.15.920.938.4 SciTE v.3.6.2.0   Keyboard:00000415  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0415)  CodePage:65001  utf8.auto.check:4    # detect ascii high characters and if none found set default encoding to UTF8 and do not add BOM
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\user\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\user\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.15.1)  from:C:\Program Files (x86)\AutoIt3  input:Z:\TOOLs\Macro\PROGRAMY\__TEST\TEST_ADO_180596.au3
+>02:36:06 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "Z:\TOOLs\Macro\PROGRAMY\__TEST\TEST_ADO_180596.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
TEST_ADO_180596.au3 (10) : ==> COM Error intercepted !
    err.number is:         0x8002000E
    err.windescription:    Nieprawidłowa liczba parametrów. (Incorect number of parameters)

    err.description is:     
    err.source is:         
    err.helpfile is:     
    err.helpcontext is:     
    err.lastdllerror is:     0
    err.scriptline is:     10
    err.retcode is:     0x00000000

TEST_ADO_180596.au3 (11) : ==> COM Error intercepted !
    err.number is:         0x80020009
    err.windescription:    Wystąpił wyjątek. (exception occured)

    err.description is:     [Microsoft][Menedżer sterowników ODBC] Nie można odnaleźć nazwy źródła danych, a nie ma podanego sterownika domyślnego (Can not find the name of the data source, and there is no specified default driver)
    err.source is:         Microsoft OLE DB Provider for ODBC Drivers
    err.helpfile is:     
    err.helpcontext is:     0
    err.lastdllerror is:     0
    err.scriptline is:     11
    err.retcode is:     0x80004005

+>02:36:06 AutoIt3.exe ended.rc:0
+>02:36:06 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 0.9912
 

 

 

 

 

Edited February 13, 2016 by mLipok

[mLipok]

second attempt:

 

;~ https://www.autoitscript.com/forum/topic/180596-odbc-object-fails-with-certain-methods/#comment-1296503

Example()

Func Example()
    ; Error monitoring. This will trap all COM errors while alive.
    ; This particular object is declared as local, meaning after the function returns it will not exist.
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

    Local $oDatabase = ObjCreate("ADODB.Connection")
    ; $oDatabase.Provider("SQLOLEDB")
    $oDatabase.Open("Provider=SQLOLEDB; DSN=ABC; UID=Yes; Password=Sure;")
EndFunc    ;==>Example

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    _ConsoleOut(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc    ;==>_ErrFunc


Func _ConsoleOut($string = '')
    ConsoleWrite(BinaryToString(StringToBinary($string & @CRLF, 4), 1) & @CRLF)
EndFunc    ;==>_ConsoleOut

 

Third:

$oDatabase.Provider="SQLOLEDB"
    $oDatabase.Open("DSN=ABC;UID=Yes;Password=Sure;")

 

Edited February 13, 2016 by mLipok

[WestCBear]

Hi mLipok,

 

You've been a tremendous help! Thank you. 

 

WestCBear

[mLipok]

?? this was only 2 post and 2 edit .....
How it could happend

mLipok

 

[WestCBear]

I ended up saying screw it to the ".Provider" part, and just sent a connection string instead.  Et voila!  It worked.