Jump to content

Recommended Posts

Cześć == Hi.

If you are using MS SQL try this way:

Select * from [TEST]
Select * from [TEST 123]

or even:

Select * from [catalog].[dbo].[TEST]
Select * from [catalog].[dbo].[TEST 123]

In PostgreSQL you should be able to use double quotes in both following cases:

Select * from "TEST"
Select * from "TEST 123"

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2021-10-17

Link to post
Share on other sites
  • Replies 529
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

I want to present BETA Version of my ADO.au3 UDF. This is modifed version of _sql.au3 UDF.   For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH   T

I hope soon I find time to provide new features and examples based on the knowledge which I gather thanks to this above mentioned book.   btw. Today I also buy this one:   I hope

Remark 1: Wiki page are talking about ADO in general - I mean not related to my UDF. Wiki examples for ADO are very similar as I changed many of them to use the same variable names as in my ADO

Posted Images

In PostgreSQL syntax

Select * from "TEST"

result

###############################
ADO.au3 v.2.1.15 BETA (1286) : ==> COM Error intercepted !
$oADO_Error.description is:     B£¥D: relacja "TEST" nie istnieje;
Error while executing the query
$oADO_Error.windescription:     Wyst¹pi³ wyj¹tek.

$oADO_Error.number is:  80020009
$oADO_Error.lastdllerror is:    0
$oADO_Error.scriptline is:  1286
$oADO_Error.source is:  Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:    
$oADO_Error.helpcontext is:     0
###############################

The server PostgreSQL works like this

look results select in psql.exe 

bazaviszna=# select * from "TEST";
BŁĄD:  relacja "TEST" nie istnieje
LINIA 1: select * from "TEST";
                       ^

bazaviszna=# select * from TEST;
 id |                url                |        name         | description | rel
----+-----------------------------------+---------------------+-------------+-----
  1 | http://www.postgresqltutorial.com | PostgreSQL Tutorial |             |
(1 wiersz)


bazaviszna=# select * from "TEST 123";
    pesel    |                       nr_zam
-------------+----------------------------------------------------
 abc         | 1234
(1 wiersz)


bazaviszna=# select * from TEST 123;
BŁĄD:  błąd składni w lub blisko "123"
LINIA 1: select * from TEST 123;
                            ^

Solution
Remember to use " " in tablename 2 words and space
Remember to NOT use " " in tablename 1 word

:)

Link to post
Share on other sites
  • 1 month later...

hi guys  in particolar @mLipok , i have a little problem with Ado  last version  i have a  remote machin with db mysql  i try to connect by remote , with HeidiSQL  work and  i can see all db table change  etcc..  but  i tryed to connect with ado  and return me always  this error

###############################
ADO.au3 v.2.1.15 BETA (1100) : ==> COM Error intercepted !
$oADO_Error.description is:     [MySQL][ODBC 5.3(w) Driver]Access denied for user 'xxxxx'@'192.168.0.%' to database 'gestionale'
$oADO_Error.windescription:     Exception occurred.

$oADO_Error.number is:     80020009
$oADO_Error.lastdllerror is:     0
$oADO_Error.scriptline is:     1100
$oADO_Error.source is:     Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:     
$oADO_Error.helpcontext is:     0
###############################

my code

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>
#include <WinAPIFiles.au3>
#include "ADO.au3"


Global $StatusBar1, $oRecordset, $oConnection, $sConnectionString, $aRecordsetContent
 
 


Global $sDriver = "MySQL ODBC 5.3 Unicode Driver"
Global $sDatabase = "gestionale"
Global $sServer = "192.168.0.103"
Global $sPort = "3306"
Global $sUser = "xxxxx"
Global $sPassword = "xxxx"

; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_ConnectDB_gest() ;  decomment for test connection

Func _ConnectDB_gest()

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';PORT=' & $sPort & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    ConsoleWrite($sDriver & "---" & $sServer & "---" & $sPort & "---" & $sDatabase & "---" & $sUser & "---" & $sPassword & @CRLF)
    ;_GUICtrlStatusBar_SetText($StatusBar1, "Connecting DB")
    ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 82))

    $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
        ;   _GUICtrlStatusBar_SetText($StatusBar1, "Offline DB")
        ;   _GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 80))
    Else

        ;_GUICtrlStatusBar_SetText($StatusBar1, "Online DB")
        ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 81))
    EndIf

EndFunc   ;==>_ConnectDB_gest

you have some suggest ??

thankz at all

Link to post
Share on other sites

First, confirm 32 or 64 bit ODBC, and how do you select that driver? 

 

My guess is that there is a problem with the username. It may be the way the connection string is scripted. Try wrapping the username in "double quotes". 

Try

ConsoleWrite( $sConnectionString & @CRLF)

What happens if you try to connect from the console/command line? 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites

@Skysnake suggestion is good.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2021-10-17

Link to post
Share on other sites

thankz, i been resolved a problem to connection , but  now  i not can display a  row  of table   , probably i do some error

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>
#include <WinAPIFiles.au3>
#include "ADO.au3"


Global $StatusBar1, $oRecordset, $oConnection, $sConnectionString, $aRecordsetContent
 
 


Global $sDriver = "MySQL ODBC 5.3 Unicode Driver"
Global $sDatabase = "gestionale"
Global $sServer = "192.168.0.103"
Global $sPort = "3306"
Global $sUser = "xxxxx"
Global $sPassword = "xxxx"

; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_ConnectDB_gest() ;  decomment for test connection

Func _ConnectDB_gest()

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';PORT=' & $sPort & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    ConsoleWrite($sDriver & "---" & $sServer & "---" & $sPort & "---" & $sDatabase & "---" & $sUser & "---" & $sPassword & @CRLF)
    ;_GUICtrlStatusBar_SetText($StatusBar1, "Connecting DB")
    ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 82))

    $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
        ;   _GUICtrlStatusBar_SetText($StatusBar1, "Offline DB")
        ;   _GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 80))
    Else

        ;_GUICtrlStatusBar_SetText($StatusBar1, "Online DB")
        ;_GUICtrlStatusBar_SetIcon($StatusBar1, 0, _WinAPI_ExtractIcon($DLL_LOCATE, 81))
    EndIf

Local $SQLCode = "SELECT * FROM Anagrafica ORDER BY Ana_ID DESC LIMIT 1;"
Local $aRecordset = _ADO_Execute($oConnection, $SQLCode, True)
    ;_ArrayDisplay($aRecordset)
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
    _ADO_Recordset_Display($aRecordset, 'Recordset content')
    
EndFunc   ;==>_ConnectDB_gest

dont have  error but not  display nothing  o_O

Link to post
Share on other sites

try this:

....
Local $aRecordset = _ADO_Execute($oConnection, $SQLCode, True)
If @error Then MsgBox($MB_ICONERROR, 'ERROR', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
....

 

Edited by mLipok

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2021-10-17

Link to post
Share on other sites

hi @mLipok i try tyo resolve problem  of script , i sobstituted your  ado udf  with  classical  attach at odbc  , (in this mode  work)

i have 2 scritpi to include   one is  data of db like user pass and more , and second is udf for connect and much more ..  i publish follow

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.6.1
    Author:         faustf

    Script Function:
    Gestionale  per ufficio

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

#include <MySQL.au3>

Global $Nome ,  $TableContents , $SQLInstance , $SQLCode
 

Dim $UserName = "xxxxxxx"
Dim $Password = "xxxxxxxx"
Dim $Database = "azienda"
Dim $MySQLServerName = "192.168.0.102"

second

#include-once

#cs
    Function Name:    _MySQLConnect
    Description:      Initiate a connection to a MySQL database.
    Parameter(s):     $username - The username to connect to the database with.
    $password - The password to connect to the database with. $Database - Database to connect to.
    $server - The server your database is on.
    $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}"
    Requirement(s):   Autoit 3 with COM support
    Return Value(s):  On success returns the connection object for subsequent functions. On failure returns 0 and sets @error
    @Error = 1
    Error opening connection
    @Error = 2
    MySQL ODBC Driver not installed.
    Author(s):        cdkid
#ce

Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 5.3 Unicode Driver}", $iPort=3306)
    Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v)
    If @error or $val = "" Then
        SetError(2)
        Return 0
    EndIf
    $ObjConn = ObjCreate("ADODB.Connection")
    $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort)
    If @error Then
        SetError(1)
        Return 0
    Else
        Return $ObjConn
    EndIf
EndFunc   ;==>_MySQLConnect

#cs
    Function name: _Query
    Description:     Send a query to the database
    Parameter(s):  $oConnectionObj - As returned by _MySQLConnect. $query - The query to execute
    Return Value(s):On success returns the query result. On failure returns 0 and sets @error to 1
    Requirement(s):Autoit3 with COM support
    Author(s):        cdid
#ce


Func _Query($oConnectionObj, $sQuery)
    If IsObj($oConnectionObj) Then
        Return $oConnectionobj.execute ($sQuery)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf

EndFunc   ;==>_Query

#cs
    Function name: _MySQLEnd
    Description:      Closes the database connection (see notes!)
    Parameter(s):   $oConnectionObj - The connection object as returned by _MySQLConnect()
    Requirement(s):Autoit 3 with COM support
    Return Value(s):On success returns 1. On failure returns 0 and sets @error to 1
    Author(s):         cdkid
#ce

Func _MySQLEnd($oConnectionObj)
    If IsObj($oConnectionObj) Then
        $oConnectionObj.close
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_MySQLEnd

#cs
    Function name: _AddRecord
    Description:     Adds a record to the specified table
    Note(s):           to add to multiple columns use an array with one blank element at the end as the $sColumn, and $value parameter
    Parameter(s):   $oConnectionObj - As returned by _MySQL Connect. $sTable - The table to put the record in
                    $row - The row to put the record in. $value - The value to put into the row
                    $vValue - OPTIONAL default will be default for the column (will not work with array, see notes)
    Requirement(s): Autoit 3 with COM support
    Return value(s): On success returns 1. If the connectionobj is not an object returns 0 and sets @error to 2. If there is any other error returns 0 and sets @error to 1.
    Author(s): cdkid
#ce

Func _AddRecord($oConnectionObj, $sTable, $vRow, $vValue = "")
    If IsObj($oConnectionObj) Then
        $query = "INSERT INTO " & $sTable & " ("

        If IsArray($vRow) Then
            For $i = 0 To UBound($vRow, 1) - 1
                If $i > 0 And $i <> UBound($vRow, 1) - 1 Then
                    $query = $query & "," & $vRow[$i] & ""
                ElseIf $i = UBound($vRow, 1) - 1 And $vRow[$i] <> "" Then
                    $query = $query & "," & $vRow[$i] & ") VALUES("
                ElseIf $i = 0 Then
                    $query = $query & "" & $vRow[$i] & ""
                ElseIf $vRow[$i] = "" Then
                    $query = $query & ") VALUES("
                EndIf
            Next
        EndIf
        If Not IsArray($vRow) And Not IsArray($vValue) And Not IsInt($vValue) Then
            $oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')")
            return 1
        ElseIf IsInt($vValue) And Not IsArray($vRow) And Not IsArray($vValue) Then
            $oconnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES(" & $vValue & ")")
            return 1
        EndIf

        If IsArray($vValue) Then
            For $i = 0 To UBound($vValue, 1) - 1
                If $i > 0 And $i <> UBound($vValue, 1) - 1 And Not IsInt($vValue[$i]) Then
                    $query = $query & ",'" & $vValue[$i] & "'"
                ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" And Not IsInt($vValue[$i]) Then
                    $query = $query & ",'" & $vValue[$i] & "');"
                ElseIf $i = 0 And Not IsInt($vValue[$i]) Then
                    $query = $query & "'" & $vValue[$i] & "'"
                ElseIf $vValue[$i] = "" Then
                    $query = $query & ");"
                ElseIf IsInt($vValue[$i]) And $vValue[$i] <> "" Then
                    $query = $query & "," & $vValue[$i]
                EndIf
            Next
        EndIf
        If StringRight($query, 2) <> ");" Then
            $query = $query & ");"

        EndIf
        $oconnectionobj.execute ($query)
    EndIf
    If Not IsObj($oConnectionObj) Then
        SetError(2)
        Return 0
    EndIf
    If @error And IsObj($oConnectionObj) Then
        Return 0
        SetError(1)
    Else
        Return 1
    EndIf

EndFunc   ;==>_AddRecord


#cs
    Function name: _DeleteRecord
    Description:     Deletes a record from the specified table
    Parameter(s):  $oConnectionObj - As returned by _MySQLConnect. $sTable - The table to delete from.
    $sColumn - The column to check value (see the example in the next post) $vRecordVal -
    The value to check in $sColumn (see example).
    $iLimit (optional) - the max number of record to delete if multiple match the criteria (default 1)
    Return Value(s): On success returns 1. If there $oConnectionObj is not an object returns 0 and sets @error to 1. If there are any other errors returns 0 and sets @error to 2
    Requirement(s): Autoit 3 with COM support
#ce

Func _DeleteRecord ($oConnectionObj, $sTable, $sColumn, $vRecordVal, $iLimit = 1)
    If IsObj($oConnectionObj) And Not IsInt($vRecordVal) Then
        $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vRecordVal & "' LIMIT " & $iLimit & ";")
    ElseIf IsInt($vRecordVal) Then
        $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = " & $vRecordVal & " LIMIT " & $iLimit & ";")
        If Not @error Then
            Return 1
        ElseIf Not IsObj($oConnectionObj) Then
            SetError(1)
            Return 0
        ElseIf @error And IsObj($oConnectionObj) Then
            SetError(2)
            Return 0
        EndIf
    EndIf
EndFunc   ;==>_DeleteRecord

#cs
    Function name: _CreateTable()
    Description: Creates a table
    Parameters: $oConnectionObj - as returned by _MySQLConnect, $sTbl - The name of the table to create, $sPrimeKey - The name of the
    primary key column. $keytype - The datatype of the primary key (default is integer), $sNotNull - "yes" = must be filled out whenever
    a record is added "no" does not need to be filled out ("yes" default). $keyautoinc - "yes" = Auto incrememnts "no" = does not.
    $sType - The table type (default is InnoDB)
    Requirements: Autoit V3 with COM support
    Return value(s): on success returns 1 on failure sets @error to 1 and returns 0
    Author: cdkid
#ce

Func _CreateTable($oConnectionObj, $sTbl, $sPrimeKey, $keytype = "INTEGER", $sNotNull = "yes", $keyautoinc = "yes", $sType = "InnoDB")
    If IsObj($oConnectionObj) And Not @error Then
        $str = "CREATE TABLE " & $sTbl & " " & "(" & $sPrimeKey & " " & $keytype & " UNSIGNED"
        If $sNotNull = "yes" Then
            $str = $str & " NOT NULL"
        EndIf

        If $keyautoinc = "yes" Then
            $str = $str & " AUTO_INCREMENT,"
        EndIf

        $str = $str & " PRIMARY KEY (" & $sPrimeKey & " )" & " ) " & "TYPE = " & $sType & ";"
        $oConnectionObj.execute ($str)
        Return 1


    ElseIf @error Then
        Return 0
        SetError(1)
    EndIf

EndFunc   ;==>_CreateTable

#cs
    Function Name: _CreateColumn
    Description: Creates a column in the given table
    Requirements: AutoitV3 with COM support
    Parameters: $oConnectionObj - as returned by _MySQLConnect. $sTable - the name of the table to add the column to.
    $sAllowNull - if 'yes' then does not add 'NOT NULL' to the SQL statement (default 'yes') $sDataType - The data type of the column
    default('VARCHAR(45)').     $sAutoInc - if 'yes' adds 'AUTO_INCREMENT' to the MySQL Statement (for use with Integer types)
    default('no').      $sUnsigned - if 'yes' adds 'UNSIGNED' to the MySQL statement. default('no') $vDefault - the default value of the column
    default('')
    Author: cdkid
#ce

Func _CreateColumn($oConnectionObj, $sTable, $sColumn, $sAllowNull = "no", $sDataType = "VARCHAR(45)", $sAutoInc = "no", $sUnsigned = "no", $vDefault = '')
    If IsObj($oConnectionObj) And Not @error Then
        $str = "ALTER TABLE `" & $sTable & "` ADD COLUMN `" & $sColumn & "` " & $sDataType & " "
        If $sAllowNull = "yes" Then
            $str = $str & "NOT NULL "
        EndIf
        If $sAutoInc = 'yes' Then
            $str = $str & "AUTO_INCREMENT "
        EndIf
        If $sUnsigned = 'yes' Then
            $str = $str & "UNSIGNED "
        EndIf
        $str = $str & "DEFAULT '" & $vDefault & "';"
        $oConnectionObj.execute ($str)
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf

EndFunc   ;==>_CreateColumn

#cs
    Function Name: _DropCol()
    Description: Delete a column from the given table
    Requirements: AutoitV3 with COM support
    Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - The name of the table to delete the column from
    $sColumn - THe name of the column to delete
    Author: cdkid
#ce

Func _DropCol($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) & Not @error Then
        $oConnectionObj.execute ("ALTER TABLE " & $sTable & " DROP COLUMN " & $sColumn & ";")
        Return 1
    ElseIf @error Then
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_DropCol

#cs
    Function Name: _DropTbl()
    Description: Deletes a table from the database
    Requirements: AutoitV3 with COM support
    Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to delete
    Author: cdkid
#ce

Func _DropTbl($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) And Not @error Then
        $oConnectionObj.execute ("DROP TABLE " & $sTable & ";")
        Return 1
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_DropTbl


#cs
    Function name: _CountRecords()
    Description: Get the number of records in the specified column
    Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table that the column is in
    $value - If not = "" then it is put in the select statement in the WHERE clause (default "")
    Return value(s): On success returns the number of records. On failure sets @error to 1 and returns 0
    Author: cdkid
#ce
Func _CountRecords($oConnectionObj, $sTable, $sColumn, $vValue = '')
    If IsObj($oConnectionObj) And Not @error Then

        If $sColumn <> "" And $vValue <> "" And Not IsInt($vValue) Then
            $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vValue & "'"
        ElseIf $sColumn <> "" And $vValue = '' And Not IsInt($vValue) Then
            $constr = "SELECT " & $sColumn & " FROM " & $sTable
        ElseIf IsInt($vValue) And $sColumn <> '' And $vValue <> '' Then
            $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = " & $vValue
        EndIf
        $sql2 = ObjCreate("ADODB.Recordset")
        $sql2.cursorlocation = 3
        $sql2.open ($constr, $oConnectionObj)
        With $sql2
            $ret = .recordcount
        EndWith
        $sql2.close
        Return $ret
    Else
        SetError(1)
        Return 0
    EndIf
EndFunc   ;==>_CountRecords

#cs
    Function name: _CountTables
    Description: Counts the number of tables in the database
    Parameter(s): $oConnectionObj - As returned by _MySQLConnect
    Return value(s): if error - returns 0 and sets @error to 1. on success returns the number of tables in the database
    Author: cdkid
#ce

Func _CountTables($oConnectionObj)
    If IsObj($oConnectionObj) Then
        $quer = $oConnectionObj.execute ("SHOW TABLES;")
        $i = 0
        With $quer
            While Not .EOF
                $i = $i + 1
                .MoveNext
            WEnd
        EndWith
        Return $i
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf

EndFunc   ;==>_CountTables

#cs
    Function name: _GetColNames
    Description: Get's the names of all columns in a specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to get the column names from
    Return values: On success returns an array where $array[0] is the number of elements in the array and all the rest are column names.
    On failure returns 0 and sets @error to 1
    Author: cdkid
#ce
Func _GetColNames($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) And Not @error Then
        Dim $ret[1], $rs

        $rs = $oConnectionObj.execute ("SHOW COLUMNS FROM " & $sTable & ";")

        With $rs
            While Not .EOF

                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = $rs.Fields (0).Value
                .MoveNext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
    If @error Then
        Return 0
        SetError(1)
    EndIf
EndFunc   ;==>_GetColNames


#cs
    Function name: _GetTblNames
    Description: Gets the names of all tables in the database
    Parameters: $oConnectionObj - As returned by _MySQLConnect
    Return value(s): On success returns an array where $array[0] is the number of tables and $array[n] is the nth table's name
    on failure - returns 0 and sets @error to 1
    Author: cdkid
#ce

Func _GetTblNames($oConnectionObj)
    If IsObj($oConnectionObj) Then
        Dim $ret[1]
        $quer = $oConnectionObj.execute ("SHOW TABLES;")
        With $quer
            While Not .eof
                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = .fields (0).value
                .movenext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
EndFunc   ;==>_GetTblNames

#cs
    Function name: _GetColVals
    Description: Gets all of the values of a specified column in a specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect(), $sTable - the table that the column is in
    $sColumn - the column to get values from.
    Return value(s): On success returns an array where $array[0] is the number of values and $array[n] is the Nth value
    On failure sets @error to 1 and returns 0
    Author: cdkid
#ce

Func _GetColVals($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) Then
        Dim $ret[1]
        $quer = $oConnectionObj.execute ("SELECT " & $sColumn & " FROM " & $sTable & ";")
        With $quer
            While Not .EOF
                ReDim $ret[UBound($ret, 1) + 1]
                $ret[UBound($ret, 1) - 1] = .Fields (0).value
                .MoveNext
            WEnd
        EndWith
        $ret[0] = UBound($ret, 1) - 1
        Return $ret
    EndIf
EndFunc   ;==>_GetColVals

#cs
    Function name: _GetColCount
    Description: Gets the number of columns in the specified table
    Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table to count the columns in
    Return Value(s): On success returns the number of columns in the table. On failure returns -1 and sets @error to 1
    Author: cdkid
#ce
Func _GetColCount($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) Then
        $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable)
        With $quer
            $i = 0
            While Not .eof
                $i = $i + 1
                .movenext
            WEnd
        EndWith
        Return $i
    EndIf
    If @error Then
        Return -1
        SetError(1)
    EndIf

EndFunc   ;==>_GetColCount

#cs
    Function name: _GetColType
    Description: Gets the DATA TYPE of the specified column
    Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table that the column is in. $sColumn - the column
    to retrieve the data type from.
    Return value(s): On success returns the data type of the column. On failure returns 0 and sets @error to 1
    Author: cdkid
#ce
Func _GetColType($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) Then
        $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable)
        With $quer
            $i = 0
            While Not .eof
                If .fields (0).value = $sColumn Then
                    $ret = .fields (1).value
                EndIf
                .MoveNext
            WEnd
        EndWith
        Return $ret
    EndIf
    If @error Then
        Return 0
        SetError(1)
    EndIf
EndFunc   ;==>_GetColType

#cs
    Function: _GetDBNames
    Description: Get a count and list of all databases on current server.
    Parameters: $oConObj - As returned by _MySQLConnect
    Return Value(s): Success - An array where $array[0] is the number of databases and $array[n] is the nth database name.
    Failure - -1 and sets @error to 1
    Author: cdkid
#ce
Func _GetDBNames($conobj)
    If IsObj($conobj) Then
        Local $arr[1], $m
        $m = $conobj.Execute ("SHOW DATABASES;")
        With $m
            While Not .eof
                ReDim $arr[UBound($arr, 1) + 1]
                $arr[UBound($arr, 1) - 1] = .Fields (0).Value
                .MoveNext
            WEnd
        EndWith
        $arr[0] = UBound($arr, 1) - 1
        Return $arr
    Else
        SetError(1)
        Return -1
    EndIf
EndFunc   ;==>_GetDBNames

#cs
    Function: _ChangeCon
    Description: Change your connection string
    Parameters:
    $oConnectionObj
    As returned by _MySQLConnect
    $username
    OPTIONAL: the new username to use
    If omitted, the same username will be used.
    $password
    OPTIONAL: the new password to use
    If omitted, the same password will be used.
    $database
    OPTIONAL: the new database to connect to
    If omitted, the same database will be used.
    $driver
    OPTIONAL: the new driver to use
    If omitted, the MySQL ODBC 3.51 DRIVER will be used.
    $server
    OPTIONAL: the new server to connect to
    If omitted, the same server will be used.
    $iPort
    OPTIONAL: the new port to be used to connect
    if omitted, the default port (3306) will be used
    Return Value:
    On success, a new connection object for use with subsequent functions.
    On failure, -1 and sets @error to 1
    Author: cdkid
#ce

Func _ChangeCon($oConnectionObj, $username = "", $password = "", $database = "", $driver = "", $server = "", $iPort = 0)
    Local $constr, $db, $usn, $pwd, $svr
    If IsObj($oConnectionObj) Then
        $constr = $oConnectionObj.connectionstring
        $constr = StringReplace($constr, 'Provider=MSDASQL.1;Extended Properties="', '')
        $constr = StringSplit($constr, ";")
        For $i = 1 To $constr[0]
            If StringLeft($constr[$i], 3) = "UID" Then
                If $username <> "" Then
                    $usn = $username
                Else
                    $usn = StringMid($constr[$i], 5)
                EndIf
                $usn = StringTrimRight($usn, 1)
            EndIf
            If StringLeft($constr[$i], 3) = "PWD" Then
                If $password <> "" Then
                    $pwd = $password
                Else
                    $pwd = StringMid($constr[$i], 5)
                EndIf
            EndIf
            If StringLeft($constr[$i], 8) = "DATABASE" Then
                If $database <> "" Then
                    $db = $database
                Else
                    $db = StringMid($constr[$i], 10)
                EndIf
            EndIf
            If StringLeft($constr[$i], 6) = "SERVER" Then
                If $server <> "" Then
                    $svr = $server
                Else
                    $svr = StringMid($constr[$i], 8)
                EndIf
            EndIf
            If StringLeft($constr[$i], 6) = "DRIVER" Then
                If $driver <> "" Then
                    $dvr = $driver
                Else
                    $dvr = "{MySQL ODBC 3.51 DRIVER}"
                EndIf
            EndIf
            If StringLeft($constr[$i], 4) = "PORT" Then
                if $iport <> 0 Then
                    $port = $iport
                Else
                    $port = 3306
                EndIf
            EndIf
        Next
        $oConnectionObj.close
        $oConnectionObj.Open ("DATABASE=" & $db & ";DRIVER=" & $dvr & ";UID=" & $usn & ";PWD=" & $pwd & ";SERVER=" & $svr & ";PORT=" & $port & ";")
        Return $oConnectionObj
    Else
        SetError(1)
        Return -1
    EndIf
EndFunc   ;==>_ChangeCon

i substitute your code   with   this

(in  top  insert  a new include  of  2 script  that i published)

your code 

#cs
        Local $SQLCode = "SELECT * FROM Anagrafica ORDER BY Ana_ID DESC LIMIT 1;"
        Local $aRecordset = _ADO_Execute($oConnection, $SQLCode, True)
        If @error Then MsgBox($MB_ICONERROR, 'ERROR', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
        _ADO_Recordset_Display($aRecordset, 'Recordset content')
        _ADO_Connection_Close($oConnection)
        $oConnection = Null
    #ce
new code 


Dim $TableName = "Anagrafica"
    ; azzeramento ID list server  per  verificare  anche dopo un inserimento
    $IDList = ""
    ; azzeramento ID list

    $SQLInstance = _MySQLConnect($UserName, $Password, $Database, $MySQLServerName)
    $SQLCode = "SELECT * FROM Anagrafica ORDER BY Ana_ID DESC LIMIT 1"
    $TableContents = _Query($SQLInstance, $SQLCode)
    With $TableContents
        While Not .EOF
            $IDList &= .Fields("Ana_ID").value
            .MoveNext
        WEnd
    EndWith
    _MySQLEnd($SQLInstance)

    MsgBox(0, '78', $IDList)

 

why with new code work ?  you have some idea??   thankz

 

 

Edited by faustf
Link to post
Share on other sites

hi @mLipok i try to resolve a problem  with your  ADO  i do this test ,

in ADO.au3   in function  recordset to array

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ; Error handler, automatic cleanup at end of function
    Local $oADO_COM_ErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_InternalFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COM_ErrorHandler

    __ADO_Recordset_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; save current Recordset rows position to $oRecordset_Bookmark
    Local $oRecordset_Bookmark = Null
    If $oRecordset.Supports($ADO_adBookmark) Then $oRecordset_Bookmark = $oRecordset.Bookmark

    $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
    _ArrayDisplay($aRecordset_GetRowsResult)
    If @error Then ; Trap COM error, report and return
        Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE)
    ElseIf UBound($aRecordset_GetRowsResult) Then
        Local $aResult[0]

        ; Restore Recordset row position from stored $oRecordset_Bookmark
        If $oRecordset_Bookmark = Null Then
            $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf

        Local $iColumns_count = UBound($aRecordset_GetRowsResult, $UBOUND_COLUMNS)
        Local $iRows_count = UBound($aRecordset_GetRowsResult)

        If $bFieldNamesInFirstRow Then
            ; Adjust the array to fit the column names and move all data down 1 row
            ReDim $aRecordset_GetRowsResult[$iRows_count + 1][$iColumns_count]

            ; Move all records down
            For $iRow_idx = $iRows_count To 1 Step -1
                For $y = 0 To $iColumns_count - 1
                    $aRecordset_GetRowsResult[$iRow_idx][$y] = $aRecordset_GetRowsResult[$iRow_idx - 1][$y]
                Next
            Next

            ; Add the coloumn names
            For $iCol_idx = 0 To $iColumns_count - 1 ;get the column names and put into 0 array element
                $aRecordset_GetRowsResult[0][$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult = $aRecordset_GetRowsResult

            Return SetError($ADO_ERR_SUCCESS, $iRows_count + 1, $aResult)
        Else

            ReDim $aResult[$ADO_RS_ARRAY_ENUMCOUNTR]
            Local $aFiledNames_Temp[$iColumns_count]

            For $iCol_idx = 0 To $iColumns_count - 1 ;get the column names and put into 0 array element
                $aFiledNames_Temp[$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
            $aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
            $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult
            Return SetError($ADO_ERR_SUCCESS, $iRows_count, $aResult)
        EndIf
    EndIf

    Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
EndFunc   ;==>_ADO_Recordset_ToArray

_ArrayDisplay($aRecordset_GetRowsResult) 

and return an array with my data  table 

but if i  insert an

_ArrayDisplay($aResult)

return  me an array blank with 3 raw

i hope will be help  , thankz  again

Link to post
Share on other sites

hi @mLipok  i have ia little  answer ,  why you use in ADO.au3  in line 554   in function ado_rECORDSET_to array , this code???

    $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult

when i _arraydisplay ($aResult) i have  record {ARRAY},  why you not  return  directly  $aRecordset_GetRowsResult ?

 

Link to post
Share on other sites

Take a loo for:

_ADO_RecordsetArray_GetContent()

This is intentional feature to keep 

$aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
$aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
$aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult

 

As this is much more faster to use only $oRecordset.GetRows with $bFieldNamesInFirstRow = False

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2021-10-17

Link to post
Share on other sites

but  if i use  ADO_Recordset_Display   what  i could  see  ???   a popup with  array  full of data ?? or  write in console ??

i dont  understund  how return a data

i try to  run examle but return me error

Edited by faustf
Link to post
Share on other sites
9 minutes ago, faustf said:

what  i could  see  ???   ...........YES.... a popup with  array  full of data ??

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE *

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2021-10-17

Link to post
Share on other sites

other questions , 

how pass a data  _ADO_Recordset_ToArray   to   ADO_Recordset_Display    ,  if  you dont  use a return ???

and  why if  i  display this  array

$aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult

_arraydisplay($aResult)

why return 2  the variable   $ADO_RS_ARRAY_RSCONTENT  you declare in ado constant  , and  in function you never assign  number ? o_O

Link to post
Share on other sites

other  questions @mLipok

return me  this error 

 : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)

in  function __ADO_RecordsetArray_Display 

if i comment ;  arraydisplay  go   but not  display nothing :)

whats wrong ?

 

 

Link to post
Share on other sites

i birth  i  remove   a "" because  arraydisplay  only 7  options and  your array display  have  8   becuase  a new version of autoit  change debug arraydisplay and arraydisplay  probably

(but  we really felt the need for debuggarraydisplay ?? :D )

upgrade :D

thankz again

Link to post
Share on other sites

anotheer little bit  questions in script for return my table data  in array i use  this  code

Local $aSelect = _ADO_RecordsetArray_GetContent($aRecordset)

is correct because  when i do _arraydisplay ($aSelect)  , work good

but if i do  a classical for

$Combo1 = GUICtrlCreateCombo("------", 153, 58, 145, 25, BitOR($CBS_DROPDOWN, $CBS_AUTOHSCROLL))
        For $i = 0 To UBound($aSelect) - 1
            GUICtrlSetData($Combo1, $aSelect[$i])
        Next

return me this error

 : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
GUICtrlSetData($Combo1, $aSelect[$i])

is a array rfeturned not correct ??  for your experience what do you think ?

thankz

Link to post
Share on other sites

hi @mLipok suggestion for ADO 

if possible  simplify  little bit  like   this

Local $aLReturnSQL = _DB_Sql_Execute("SELECT Ana_Nome FROM Anagrafica;", 1)


    _ArrayDisplay($aLReturnSQL)
     
        For $i = 0 To UBound($aLReturnSQL) - 1
            GUICtrlSetData($Combo1, $aLReturnSQL[$i])
        Next
        








Func _DB_Sql_Execute($SQLCode, $Close)

    Local $aRecordset = _ADO_Execute($oConnection, $SQLCode) ; $oConnection global variable
    If @error Then
        MsgBox($MB_ICONERROR, 'ERROR', '@error = ' & @error & @CRLF & '@extended = ' & @extended)
    Else
        Local $aRecordsetArray = _ADO_Recordset_ToArray($aRecordset, False)
        Local $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)
        If $Close = 1 Then
            _ADO_Connection_Close($oConnection)
            $oConnection = Null
        EndIf
        If UBound($aRecordset_inner, $UBOUND_COLUMNS) = 1 Then
            Local $aLRetData[UBound($aRecordset_inner)]
            Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
            For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
                For $iColumn_idx = 0 To $iColumn_count - 1
                    $aLRetData[$iRecord_idx] = $aRecordset_inner[$iRecord_idx][$iColumn_idx]
                Next
            Next
            Return $aLRetData
        Else
            Local $aLRetData[UBound($aRecordset_inner)]
            Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
            For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
                For $iColumn_idx = 0 To $iColumn_count - 1
                    ConsoleWrite($aRecordset_inner[$iRecord_idx][$iColumn_idx])
                Next
            Next
            Return $aRecordset_inner
        EndIf
    EndIf
EndFunc   ;==>_DB_Sql_Execute

the  func _DB_Sql_Execute ......   insert in ADO.au3 , i think is better 

i hope have give you a idea  :D thankz again for ADO  bye

 

Link to post
Share on other sites

@faustf I use it like this:

$aResult = _ADO_Execute($g_DB, $query, True, True)     ; select, expect table
        If @error <> $ADO_ERR_SUCCESS And @error <= $ADO_ERR_RECORDSETEMPTY Then
            ; a serious error
            ;ConsoleWrite("7.1 a serious error " & @error  & _ADO_UDFVersion() & @CRLF)
        Else
            ; includes SQL blank result set
            ; process the acquired data
            ;ConsoleWrite("Proceed " & @error & @CRLF)
        EndIf
        ; now populate gui :)
        Local $valdb2gui = $aResult[1][1] ;;; :)

Local $valdb2gui = $aResult[1][1] ; ==> use a For Loop to extract all the required data

Where the $aResult represents an Array returned (table). Note that this could be a blank array!

 

I posted elsewhere code on how I use the ADO for INSERT UPDATE DELETE commands, which do not expect a table returned, and this format for SELECTs that expect a table to be returned, note the two params TRUE TRUE :)

$aResult = _ADO_Execute($g_DB, $query, True, True)     ; select, expect table

 

Here is the code not expecting a table:

If Not _ADO_Execute($g_DB, $sSQL) = $ADO_ERR_SUCCESS Then     ; insert, does not expect a return
                    ;;;ConsoleWrite("An error occurred, please try again later?" & $ADO_ERR_SUCCESS & @CRLF)
                    MsgBox(0, "Error New Account", "An error occurred, please try again later?")     
                Else
                    ;;;ConsoleWrite("0. $ADO_ERR_SUCCESS Success " & $ADO_ERR_SUCCESS & @CRLF)
                EndIf

Note the absence of the TWO TRUE params :)

 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By dsm-sas
      Hi,
      I am looking for the possibility to extract a xls (not xlsx)-Excelfile to a csv . My problem: I do NOT have any Appliaction from MS-OFFICE. All searchings (since 3 days now) allways wants me to install ms-office or excel.
      I CAN'T DO THIS !!!
      Any help?!
      Ths'x alot
    • By Arual
      We have AutoIt code that performs a connection to an MS SQL database running SQL server 2012, tls 1.0. It has worked successfully for years.
      Today, our IT department migrated our database to a new server that is running SQL server 2016, tls 1.2. Now our connection string is no longer working.
      Here is the original that was working in SQL server 2012
      $sConnStr = "DRIVER={SQL Server};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" When that would run on the new server in SQL server 2016. We are getting the error [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
      We tried changing the connection string to 
      $sConnStr = "DRIVER={SQL Server Native Client 11.0};SERVER=servername,port;DATABASE=dbname;UID=user;PWD=pass" The new error we received is [Microsoft]
      TCP Provider: An existing connection was forcibly closed by the remote host. We can log in successfully using SSMS using the server name, port and user/pass.
      Any suggestions on what we need to change in order to get the connection to work again?
    • By mLipok
      In the past there was many questions about how to: "Automatic file upload using without user interaction"

      https://www.autoitscript.com/forum/topic/92907-ie-8-input-namenomfic-typefile-idnomfic/
      https://www.autoitscript.com/forum/topic/116899-cant-automate-input-typefile-tag-in-ie/?tab=comments#comment-815478
      https://www.autoitscript.com/forum/topic/14883-input-typefile/
      https://www.autoitscript.com/forum/topic/188708-how-to-set-the-value-of-an-input-typefile-element/
      https://www.autoitscript.com/forum/topic/91513-how-can-i-auto-set-file-path-for-input-file-in-ie/
      https://www.autoitscript.com/forum/topic/116899-cant-automate-input-typefile-tag-in-ie/
      https://www.autoitscript.com/forum/topic/169190-how-to-script-file-upload-button/
      https://www.autoitscript.com/forum/topic/145327-how-to-deal-with-ie-window-for-upload-a-fileinput-typefile/
      https://www.autoitscript.com/forum/topic/140482-internet-explorer-input-file-problem/
       
      I found solution here: 
      https://stackoverflow.com/questions/33253517/upload-a-file-via-input-input-in-html-form-with-vba
      and:
      https://www.motobit.com/tips/detpg_uploadvbsie/
      And I translate this code to AutoIt3 code:
      ; Upload file using http protocol And multipart/form-data ; v1.01 ; 2001 Antonin Foller, PSTRUH Software Global $oErrorHandler = ObjEvent("AutoIt.Error", _ErrFunc) do_vbsUpload() Func do_vbsUpload() #cs ; We need at least two arguments (File & URL) ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) If $CmdLine[0] < 2 Then InfoEcho() ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) ; Are some required objects missing? If StringInStr(CheckRequirements(), "Error") > 0 Then InfoEcho() ConsoleWrite('- ' & @ScriptLineNumber & @CRLF) Local $s_FileName, $s_DestURL, $s_FieldName $s_FieldName = "FileField" ; Default field name For $i_argCounter = 1 To $CmdLine[0] ConsoleWrite('+ '& $i_argCounter& ' >> ' & $CmdLine[$i_argCounter] & @CRLF) Select Case $i_argCounter = 1 ;~ $s_FileName = $CmdLine[$i_argCounter] $s_FileName = @ScriptFullPath Case $i_argCounter = 2 $s_DestURL = $CmdLine[$i_argCounter] Case $i_argCounter = 3 $s_FieldName = $CmdLine[$i_argCounter] EndSelect Next UploadFile($s_DestURL, $s_FileName, $s_FieldName) #ce UploadFile('http://www.dobeash.com/test.html', @ScriptFullPath, 'fileExample') EndFunc ;==>do_vbsUpload ; ******************* upload - begin ; Upload file using input type=file Func UploadFile($s_DestURL, $s_FileName, $s_FieldName) ; Boundary of fields. ; Be sure this string is Not In the source file Const $Boundary = "---------------------------0123456789012" ; Get source file As a binary data. Local $d_FileContents = GetFile($s_FileName) ; Build multipart/form-data document Local $s_FormData = BuildFormData($d_FileContents, $Boundary, $s_FileName, $s_FieldName) ; Post the data To the destination URL IEPostBinaryRequest($s_DestURL, $s_FormData, $Boundary) EndFunc ;==>UploadFile ; Build multipart/form-data document with file contents And header info Func BuildFormData($d_FileContents, $Boundary, $s_FileName, $s_FieldName) Const $s_ContentType = "application/upload" ; The two parts around file contents In the multipart-form data. Local $s_Pre = "--" & $Boundary & @CRLF & mpFields($s_FieldName, $s_FileName, $s_ContentType) Local $s_Po = @CRLF & "--" & $Boundary & "--" & @CRLF ; Build form data using recordset binary field Const $i_adLongVarBinary = 205 Local $oRS = ObjCreate("ADODB.Recordset") ; https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/append-method-ado?view=sql-server-ver15 $oRS.Fields.Append("b", $i_adLongVarBinary, StringLen($s_Pre) + BinaryLen($d_FileContents) + StringLen($s_Po)) $oRS.Open() $oRS.AddNew() ; Convert Pre string value To a binary data Local $i_LenData = StringLen($s_Pre) $oRS("b").AppendChunk(StringToMB($s_Pre) & StringToBinary(Chr(0))) $s_Pre = $oRS("b").GetChunk($i_LenData) $oRS("b") = "" ; Convert Po string value To a binary data $i_LenData = StringLen($s_Po) $oRS("b").AppendChunk(StringToMB($s_Po) & StringToBinary(Chr(0))) $s_Po = $oRS("b").GetChunk($i_LenData) $oRS("b") = "" ; Join Pre & $d_FileContents & Po binary data $oRS("b").AppendChunk($s_Pre) $oRS("b").AppendChunk($d_FileContents) $oRS("b").AppendChunk($s_Po) $oRS.Update() Local $s_FormData = $oRS("b") $oRS.Close() Return $s_FormData EndFunc ;==>BuildFormData ; sends multipart/form-data To the URL using IE Func IEPostBinaryRequest($s_URL, $s_FormData, $Boundary) ; Create InternetExplorer Local $oIE = ObjCreate("InternetExplorer.Application") ; You can uncoment Next line To see form results $oIE.Visible = True ; Send the form data To $s_URL As POST multipart/form-data request $oIE.Navigate($s_URL, '', '', $s_FormData, _ "Content-Type: multipart/form-data; boundary=" & $Boundary & @CRLF) While $oIE.Busy Wait(1, "Upload To " & $s_URL) WEnd ; Get a result of the script which has received upload ;~ On Error Resume Next Local $s_IE_InnerHTML = $oIE.Document.body.innerHTML MsgBox(0, 'TEST #' & @CRLF & @ScriptLineNumber, $s_IE_InnerHTML) $oIE.Quit() Return $s_IE_InnerHTML EndFunc ;==>IEPostBinaryRequest ; Infrormations In form field header. Func mpFields($s_FieldName, $s_FileName, $s_ContentType) Local $s_MPTemplate = _ ; template For multipart header 'Content-Disposition: form-data; name="{field}";' & _ 'FileName="{file}"' & @CRLF & _ 'Content-Type: {ct}' & @CRLF & @CRLF & _ '' Local $s_Out $s_Out = StringReplace($s_MPTemplate, "{field}", $s_FieldName) $s_Out = StringReplace($s_Out, "{file}", $s_FileName) $s_Out = StringReplace($s_Out, "{ct}", $s_ContentType) Return $s_Out EndFunc ;==>mpFields Func Wait($i_Seconds, $s_Message) MsgBox(64, '', $s_Message, $i_Seconds) EndFunc ;==>Wait ; Returns file contents As a binary data Func GetFile($s_FileName) Local $oStream = ObjCreate("ADODB.Stream") $oStream.Type = 1 ; Binary $oStream.Open() $oStream.LoadFromFile($s_FileName) Local $d_GetFile = $oStream.Read() $oStream.Close() Return $d_GetFile EndFunc ;==>GetFile ; Converts OLE string To multibyte string Func StringToMB($S) Local $I, $B For $I = 1 To StringLen($S) $B &= StringToBinary(Asc(StringMid($S, $I, 1))) Next Return $B EndFunc ;==>StringToMB ; ******************* upload - end ; ******************* Support ; Basic script info Func InfoEcho() Local $sMsg = _ "Upload file using http And multipart/form-data" & @CRLF & _ "Copyright (C) 2001 Antonin Foller, PSTRUH Software" & @CRLF & _ "use" & @CRLF & _ "[cscript|wscript] fupload.vbs file $s_URL [fieldname]" & @CRLF & _ " file ... Local file To upload" & @CRLF & _ " $s_URL ... $s_URL which can accept uploaded data" & @CRLF & _ " fieldname ... Name of the source form field." & @CRLF & _ @CRLF & CheckRequirements() & @CRLF & _ "" ConsoleWrite('! ' & $sMsg & @CRLF) EndFunc ;==>InfoEcho ; Checks If all of required objects are installed Func CheckRequirements() Local $sMsg = _ "This script requires some objects installed To run properly." & @CRLF & _ CheckOneObject("ADODB.Recordset") & @CRLF & _ CheckOneObject("ADODB.Stream") & @CRLF & _ CheckOneObject("InternetExplorer.Application") & @CRLF & _ "" Return $sMsg ; $sMsgBox $sMsg EndFunc ;==>CheckRequirements ; Checks If the one object is installed. Func CheckOneObject($sClassName) Local $sMsg ObjCreate($sClassName) If @error = 0 Then $sMsg = "OK" Else $sMsg = "Error:" & @error EndIf Return $sClassName & " - " & $sMsg EndFunc ;==>CheckOneObject ; ******************* Support - end ; User's COM error function. Will be called if COM error occurs Func _ErrFunc(ByRef $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  
      But I miss something and the code not works as intendend.
      Please join and contribute, in solving this issue, as this will be handy for entire community.
      @mLipok
       
      btw.
      I think that this may be realated to ChrB() which I simply translate to StringToBinary()
      Especialy this :
      StringToBinary(Chr(0))) could be the main issue.
      But for now I'm tired and going to sleep.
      Hope maybe tomorrow somebody solve this issue.
       
    • By robertocm
      An example of updating an excel file with a join between excel range and access tables.
      #include <Excel.au3> #include <Array.au3> #include <MsgBoxConstants.au3> ;#include <WinAPIFiles.au3> ;Permitir unha única instancia da aplicación #include <Misc.au3> ;_Singleton("ADO_Update_Excel_From_Access", 0) If _Singleton("ADO_Update_Excel_From_Access", 1) = 0 Then MsgBox($MB_SYSTEMMODAL, "Warning", "An occurrence of test is already running") Exit EndIf Opt("MustDeclareVars", 1) Opt("TrayIconDebug", 1) OnAutoItExitRegister("OnAutoItExit") #===== CONFIG ===== Global $sFilePath = @ScriptDir & "\test.xlsx" Global $sFilePath2 = @ScriptDir & "\test.mdb" Global $testmdb = "[;Database=" & $sFilePath2 & ";PWD=123]" ;~ Global $excel = "[Excel 12.0 Xml;HDR=NO;IMEX=1;DATABASE=" & $sFilePath& "]" ;Global $testsqlserver = "[odbc;Driver={SQL Server};SERVER=10.0.0.99;DATABASE=MyDatabaseName;UID=MyUser;PWD=MyPassword]" #===== ADODB ===== Global $cn, $rst, $sSQL, $SubSQL ;Help: COM Error Handling ;_ErrADODB From spudw2k ;https://www.autoitscript.com/forum/topic/105875-adodb-example/ Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB") Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable $cn = ObjCreate("ADODB.Connection") ; Create a connection object $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123" ;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123" ;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath2 & ';uid=;pwd=MyPassword;' ;~ ;Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=MyUser;Password=MyPassword;' ;~ ;Or if you’re using native client: ;~ ;stConnect = "Provider=SQLNCLI10;Data Source=... ;http://www.connectionstrings.com/ ;Xlsx files: Excel 2007 (and later) files with the Xlsx file extension ;[Also valid for] Using the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks ;cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & RutaXls & ";Extended Properties=Excel 12.0 Xml;" Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";" ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";" ;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=Excel 8.0;" ;Global $sADOConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & $sFilePath & ";ReadOnly=0;" ;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp ;A Recordset object inherits this setting from the associated Connection object. ;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object. $cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient $cn.CommandTimeout = 30 ;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode ;try Mode = adModeRead instead ;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead ;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1 ;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado" ;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode. ;https://www.w3schools.com/asp/prop_rec_mode.asp ;$cn.Mode = 1 ;Read-only $cn.Open($sADOConnectionString) ; Open the connection ;MsgBox(0, "", $cn.ConnectionString) $sSQL = "UPDATE (([Sheet1$A2:C11] a" _ & " INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID)" _ & " INNER JOIN " & $testmdb & ".[Orders] c ON b.ID = c.ID)" _ & " INNER JOIN " & $testmdb & ".[Customers] d ON c.CustomerID = d.ID" _ & " SET a.F2 = c.OrderDate, a.F3 = d.CompanyName;" $cn.Execute($sSQL, Default, 1 + 0x80) ;adCmdText = 1 , adExecuteNoRecords = 0x80 $sSQL = "SELECT F2, F3, Sum(Quantity * UnitPrice) As Amount" _ & " FROM [Sheet1$A2:C11] AS a INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID" _ & " GROUP BY F2, F3" _ & " ORDER BY F2;" $rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query If Not $rst.EOF = True Then Local $rstArray = $rst.GetRows() _ArrayDisplay($rstArray, "Test", "", $ARRAYDISPLAY_NOROW, "", "F1|F2|F3") $rst.Close $rst = 0 ;Release the recordset object ;$cmd = 0 $cn.Close ;Close the connection $cn = 0 ;Release the connection object Global $RecCount = UBound($rstArray) #===== EXCEL ===== Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. ;_DebugSetup() ;_DebugCOMError() ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True) ;Global $oAppl = _Excel_Open(True, False, False, Default, True) Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509 ;Restaurar en cada arquivo (algún Application.Run pudo cambiar) ;$oAppl.EnableEvents = False $oAppl.DisplayAlerts = False ;~ ;Arquivo non bloqueado ;~ Global $iFileExists ;~ For $j = 0 To 60 ;~ $iFileExists = FileExists($sFilePath2) ;~ If $iFileExists Then ;~ While _WinAPI_FileInUse($sFilePath2) ;~ Sleep(1000) ;~ WEnd ;~ ExitLoop ;~ Else ;~ Sleep(1000) ;~ EndIf ;~ Next ;Create a new workbook with only 1 worksheet Global $oWorkbook = _Excel_BookNew($oAppl, 1) ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") ;~ Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath5, False, True) ;~ ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ ;Create a new workbook with only 1 worksheet ;~ ;Global $oWorkbook = _Excel_BookNew($oAppl, 1) ;~ ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.") ;Sleep(3000) ;~ Global $sMessage, $sMessage2 ;~ SplashTextOn("TitleFoo", $sMessage, 580, 60, 900, 840, 1 + 4, "", 16) ;~ For $i = 1 To 10 ;~ $sMessage = $sMessage & "." ;~ $sMessage2 = @TAB & "Pausa " & $sMessage ;~ ControlSetText("TitleFoo", "", "Static1", $sMessage2) ;~ Sleep(1000) ;~ Next $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever ;Global $oSheets = $oWorkbook.Sheets Global $oSheet = $oWorkbook.ActiveSheet ;Global $oSheet = $oWorkbook.Sheets("Sheet1") ;MsgBox(0, "", $oSheet.Name) $oSheet.Range("A1:C1").Font.Bold = True $oSheet.Range("A1:A" & $RecCount + 1).NumberFormat = "dd/mm" $oSheet.Range("B1:B" & $RecCount + 1).NumberFormat = "@" Global $oPageSetup = $oSheet.PageSetup With $oPageSetup .PrintTitleRows = "$1:$1" .PrintTitleColumns = "" .PrintArea = "" .LeftHeader = "&D" .CenterHeader = "Report" .RightHeader = "&P of &N" ;.LeftFooter = "&F {&A}" .CenterFooter = "" .RightFooter = "" .LeftMargin = 28 .RightMargin = 28 .TopMargin = 28 .BottomMargin = 28 .HeaderMargin = 15 .FooterMargin = 15 .PrintHeadings = False .PrintGridlines = True .PrintComments = -4142 .CenterHorizontally = False .CenterVertically = False .Orientation = 1 ;2 .Draft = False .FirstPageNumber = -4105 .Order = 1 .BlackAndWhite = True .Zoom = 100 EndWith ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/ Global $TrstArray = $rstArray _ArrayTranspose($TrstArray) $oSheet.Range("A2:C" & $RecCount + 1).Value = $TrstArray Global $aArray2D[1][4] = [["Date", "Client", "Amount"]] _Excel_RangeWrite($oWorkbook, $oSheet, $aArray2D, $oSheet.Cells(1, 1)) ;Global $aArray1D[11] = ["ID", "Udes", "Descrip", "Matricula", "Kilos", "Proveedor", "Corredor", "Fecha", "Contrato", "Restan", "Tanque"] ;$oSheet.Range("A1:K1").value = $aArray1D ;Global $aArray2D[1][6] = [[$rstArray[$i][1], $rstArray[$i][2], $rstArray[$i][3], $rstArray[$i][4], $rstArray[$i][5], $rstArray[$i][6]]] ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, $oSheet.Cells($UltimaFila, 1).Resize(1, 6)) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_RangeWrite: " & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;Global $aArray2D[3][5] = [[11, 12, 13, 14, 15], [21, 22, 23, 24, 25], [31, 32, 33, 34, 35]] ;_Excel_RangeWrite($oWorkbook, Default, $aArray2D, "B1") ;Local $aArray1D[13] = ["Ped", "Archivo", "Abono", "NomCli", "H+I", "ACIDEZ", "CERAS", "E+U", "aa", "aa", "aa", "aa", "aa"] ;$oSheet.Range("A1:M1").value = $aArray1D ;$oSheet.Cells(1, 1).Resize(1, 13).value = $aArray1D ;_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook 51 ;$xlExcel8 56 ;_Excel_BookClose($oWorkbook, False) ;_Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ While _WinAPI_FileInUse($sFilePath5) ;~ Sleep(1000) ;~ Wend ;~ Sleep(3000) ;$oAppl.EnableEvents = True $oAppl.DisplayAlerts = True ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433 ;$oAppl.Application.Quit ;$oAppl = "" ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/ ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478 ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE) ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830 ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try: ;#include <Excel.au3> ;$oExcel = _Excel_Open(False, False, False, False, True) ;$oExcelClose = _Excel_Close($oExcel, False, True) ;~ _Excel_Close($oAppl, False, Default) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;~ Sleep(1000) ;~ ;Check excel closed ;~ Local $aProcesses = ProcessList("Excel.exe") ;~ ;_ArrayDisplay($aProcesses) ;~ If $aProcesses[0][0] > 0 Then ;~ ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191 ;~ ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :) ;~ ;For now I will use a crude workaround by closing the most recent Excel.exe instance: ;~ ProcessClose($aProcesses[$aProcesses[0][0]][1]) ;~ Sleep(100) ;just to allow some time for the process to definitely close (if it does close) ;~ EndIf Else $rst.Close $rst = 0 ; Release the recordset object $cn.Close ; Close the connection $cn = 0 ; Release the connection object ;Disconnect MsgBox(262144, "", "Empty Recordset", 5) EndIf ;This is a custom error handler Func ErrFunc() Local $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc Func _ErrADODB() Msgbox(0,"ADODB COM Error","We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $errADODB.description & @CRLF & _ "err.windescription:" & @TAB & $errADODB.windescription & @CRLF & _ "err.number is: " & @TAB & hex($errADODB.number,8) & @CRLF & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & @CRLF & _ "err.source is: " & @TAB & $errADODB.source & @CRLF & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext, 5) Local $err = $errADODB.number If $err = 0 Then $err = -1 ;Devolver datos error Local $sFilePath = @DesktopDir & "\error.txt" ;Open the file for write access. Local $hFileOpen = FileOpen($sFilePath, 2) ;If $hFileOpen = -1 Then ;MsgBox(0, "", "An error occurred when reading/writing the file.") ;EndIf FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _ "err.description is: " & @TAB & $errADODB.description & Chr(1) & _ "err.windescription:" & @TAB & $errADODB.windescription & Chr(1) & _ "err.number is: " & @TAB & hex($errADODB.number,8) & Chr(1) & _ "err.lastdllerror is: " & @TAB & $errADODB.lastdllerror & Chr(1) & _ "err.scriptline is: " & @TAB & $errADODB.scriptline & Chr(1) & _ "err.source is: " & @TAB & $errADODB.source & Chr(1) & _ "err.helpfile is: " & @TAB & $errADODB.helpfile & Chr(1) & _ "err.helpcontext is: " & @TAB & $errADODB.helpcontext _ ) ;Close the handle returned by FileOpen. FileClose($hFileOpen) $rst = 0 ;$cmd = 0 $cn.Close $cn = 0 ;Disconnect Exit EndFunc Func OnAutoItExit() $rst = 0 ;Release the recordset object If IsObj($cn) Then If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection $cn = 0 ; Release the connection object EndIf EndFunc  
      example_files.zip
    • By shino54
      Hello,
      I would like a query to know if an entry exists
      Thank you in advance.
      $sQuery = "SELECT Alger FROM garage where auto='BL1879'" $result1 = $result.Fields("Alger").Value if $result1="" Then MsgBox(0, "ERROR", "BAD not exist") Else MsgBox(0, "Success!", "OK exist") EndIf Exit  
×
×
  • Create New...