ADO.au3 UDF - BETA - Support Topic

[mLipok]

Make a smaller testing script , only try to connect with SQL Server.
I think you have error in some other places.

[Valnurat]

Is it not what I'm trying? Just to connect with a SQL server by using ADO.au3?

[Valnurat]

I deleted all white space before all lines and I don't get the previous error, but now I get the attached errors.

The code looks like this now.

#include "ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>



Func _Example_MSSQL()

Local $sDriver = 'SQL Server'
Local $sDatabase = 'StaffMembers' ; change this string to YourDatabaseName
Local $sServer = '400' ; change this string to YourServerLocation
Local $sUser = 'User' ; change this string to YourUserName
Local $sPassword = 'n3' ; change this string to YourPassword
Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
_Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
_Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
_Example_3_ConnectionProperties($sConnectionString)
_Example_4_MSSQL($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")
EndFunc   ;==>_Example_MSSQL

 

new 9.txt

Edited February 17, 2017 by Valnurat

[Valnurat]

I tried something else.

#include "ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDriver = 'SQL Server'
Global $sDatabase = 'SM' ; change this string to YourDatabaseName
Global $sServer = 'WS0400' ; change this string to YourServerLocation
Global $sUser = 'SMU' ; change this string to YourUserName
Global $sPassword = 'pw' ; change this string to YourPassword

_Example1()

Func _Example1()
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    Local $oConnection = _ADO_Connection_Create()
    ConsoleWrite('=' & @CRLF)
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    ConsoleWrite('==' & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc

But that didn't work either.

"C:\Users\DKSOKVK\Documents\AutoIT\Include\ADO.au3" (769) : ==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

 

I can access my database on the $sServer = 'WS0400' when I use delphi. Then connectionstring is like this=

Provider=SQLOLEDB.1;Password=pw;Persist Security Info=True;User ID=SMU;Initial Catalog=SM;Data Source=WS0400

I guess the "$sDriver" is the same as "Provider", right?

I then tried to change the $sDriver to Global $sDriver = 'SQL Server', but that didn't help.

Global $sDriver = 'SQLOLEDB.1'

 

Do I do something wrong here?

Edited February 17, 2017 by Valnurat
More readable

[mLipok]

For example:

Global $sServer = '192.168.1.100\SQLExpress'

 

[Valnurat]

I tried only with IP address not the "\SQLExpress", but it still give me the same error.

[mLipok]

Which ADO.au3 Version you have downloaded ?

You should see:

; #INDEX# ========================================================================
; Title .........: ADO.au3
; AutoIt Version : 3.3.10.2++
; Language ......: English
; Description ...: A collection of Function for use with an ADO database like MS SQL, MS Access ...
; Author ........: Chris Lambert, mLipok
; Modified ......: eltorro, Elias Assad Neto, CarlH
; URL ...........: http://www.autoitscript.com/forum/index.php?showtopic=180850
; Date ..........: 2016/03/18
; Version .......: 2.1.13 BETA - Work in progress
; ================================================================================

 

EDIT:
Aha.. and add this to your example:
 

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

 

Edited February 17, 2017 by mLipok

[Valnurat]

; #INDEX# ========================================================================
; Title .........: ADO.au3
; AutoIt Version : 3.3.10.2++
; Language ......: English
; Description ...: A collection of Function for use with an ADO database like MS SQL, MS Access ...
; Author ........: Chris Lambert, mLipok
; Modified ......: eltorro, Elias Assad Neto, CarlH
; Version .......: 2.1.5 BETA - Work in progress 2016/02/24
; ================================================================================
 

I guess I'm using a wrong one. Grrrr.

Edited February 17, 2017 by Valnurat

[mLipok]

Yes, this is an prehistoric version

And look again at my edit in my last post.

 

[Valnurat]

Added what you said.

I get the attach error.

new 10.txt

[mLipok]

So your problem is here:
 

$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'StaffMembersUser'.
$oADO_Error.windescription:     Exception occurred.

Did you use correct password ?
Do you can login to this server from this station using the ManagmentStudio ?

 

[Valnurat]

I'm login to the server from my own computer using SQL Server Management Studio and I don't have any issue with my password.

EDIT: But my username was wrong. It's working now.  Thank you.

Edited February 17, 2017 by Valnurat

[Valnurat]

How do you add the content to a tabel?

[mLipok]

8 hours ago, Valnurat said:

#include "ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDriver = 'SQL Server'
Global $sDatabase = 'SM' ; change this string to YourDatabaseName
Global $sServer = 'WS0400' ; change this string to YourServerLocation
Global $sUser = 'SMU' ; change this string to YourUserName
Global $sPassword = 'pw' ; change this string to YourPassword

_Example1()

Func _Example1()
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    Local $oConnection = _ADO_Connection_Create()
    ConsoleWrite('=' & @CRLF)
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    ConsoleWrite('==' & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc

..........

I can access my database on the $sServer = 'WS0400' when I use delphi. Then connectionstring is like this=

Provider=SQLOLEDB.1;Password=pw;Persist Security Info=True;User ID=SMU;Initial Catalog=SM;Data Source=WS0400

I guess the "$sDriver" is the same as "Provider", right?

So try it in the same way like in Delphi:
Just use this in function _Example1()

Local $sConnectionString = 'Provider=SQLOLEDB.1;Password=pw;Persist Security Info=True;User ID=SMU;Initial Catalog=SM;Data Source=WS0400'

 

btw. 
Drvier and provider is not the same things:

 

[mLipok]

2 minutes ago, Valnurat said:

How do you add the content to a tabel?

Did you solve the problem with connection ?

 

[Valnurat]

Yes, it's working now.

 

expandcollapse popup

#include "ADO.au3"
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDriver = 'SQL Server'
Global $sDatabase = 'StaffMembers' ; change this string to YourDatabaseName
Global $sServer = 'ws0400' ; change this string to YourServerLocation
Global $sUser = 'SMU' ; change this string to YourUserName
Global $sPassword = 'pw' ; change this string to YourPassword

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_Example1()

Func _Example1()
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Local $sTableName = 'SMU.ComputerAsset'
    Local $sQUERY = 'Select * from ' & $sTableName
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)
    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
    Local $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)
    Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordset_inner[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next
    ; CleanUp
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc

2 entries is shown in the ConsoleWrite

[mLipok]

I would like to know what the issue was ?

 

[mLipok]

36 minutes ago, Valnurat said:

How do you add the content to a tabel?

Local $sQUERY = 'INSERT INTO ........ '
Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

[Valnurat]

I posted it #192.

Wrong username and old ADO.au3.

[mLipok]

2 minutes ago, Valnurat said:

I posted it #192.

Wrong username and old ADO.au3.

Ah.. I see now, you edit this post ...
Ok.

Edited February 17, 2017 by mLipok