Jump to content

Recommended Posts

Maybe I'm doing it wrong, but i get an error.
(I'm ratter new whit ADO :))

==> Variable must be of type "Object".:

Local $sDriver = 'SQL Server'
            Local $sDatabase = 'DB name'
            Local $sServer = 'X.X.X.X'
            Local $sUser = 'root'
            Local $sPassword = ''

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

            ; Create connection object
            Local $oConnection = _ADO_Connection_Create()
            Local $sQuery = Read_from_ini("settings",$cqueryset,"filialen","err_no_IP")
            ; Open connection with $sConnectionString
            _ADO_Connection_OpenConString($oConnection, $sConnectionString)
            If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

            ; Executing some query directly to Array of Arrays (instead to $oRecordset)
            Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

            Local $iColumnsCount = $aRecordset.Fields.Count
            Local $iRowsCount = $aRecordset.RecordCount
            MsgBox($MB_SYSTEMMODAL, "Test", "$iRowsCount = " & $iRowsCount & "$iColumnsCount = " & $iColumnsCount)

            ; Clean Up
            _ADO_Connection_Close($oConnection)
            $oConnection = Null

            ; Display Array Content with column names as headers
            _ADO_Recordset_Display($aRecordset, 'Recordset content')

 

as finishing touch god created the dutch

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

; Executing some query directly to Array of Arrays (instead to $oRecordset)
            ; Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

            ; Executing some query directly to $oRecordset
            Local $oRecordset = _ADO_Execute($oConnection, $sQUERY, False)

            Local $iColumnsCount = $oRecordset.Fields.Count
            Local $iRowsCount = $oRecordset.RecordCount

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites
On ‎4‎/‎21‎/‎2016 at 4:19 AM, mLipok said:

Show your script, or at least snippet.

#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>

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

; Uncomment one of the following examples
;
;~ _Example_MSAccess()
 _Example_MSExcel()
;~ _Example_MSSQL()
;~ _Example_MySQL()
;~ _Example_PostgreSQL()

Func _Example_MSAccess()

    Local $sMDB_FileFullPath = 'Here put FileFullPath to your Access File'
    Local $sDriver = Default
    Local $sUser = Default
    Local $sPassword = Default

    Local $sConnectionString = _ADO_ConnectionString_Access($sMDB_FileFullPath, $sUser, $sPassword, $sDriver)

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc    ;==>_Example_MSAccess

Func _Example_MSExcel()

    Local $sFileFullPath = Default  ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider , $sExtProperties , $HDR , $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc    ;==>_Example_MSAccess

Func _Example_MSSQL()

    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'YourBASENAME'
    Local $sServer = 'localhost\SQLExpress'
    Local $sUser = 'YourUserName'
    Local $sPassword = '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

Func _Example_MySQL()
    ; Link to Windows MySQL ODBC drivers
    ; https://dev.mysql.com/downloads/connector/odbc/

    Local $sDriver = 'MySQL ODBC 5.3 ANSI Driver' ; 'MySQL ODBC 5.3 UNICODE Driver'
    Local $sServer = 'localhost' ; change this string to YourServerLocation
    Local $sDatabase = 'world' ; change this string to YourDatabaseName
    Local $sPort = '3306' ; change this string to If your Server use non standard PORT
    Local $sUser = 'AutoIt' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    ; Local $sConnectionString = 'Driver={' & $sDriver & '};SERVER=' & $sServer & ';PORT=' & $sPort & ';DATABASE=' & $sDatabase & ';User=' & $sUser & ';Passwd=' & $sPassword & ';'
    Local $sConnectionString = _ADO_ConnectionString_MySQL($sUser , $sPassword , $sDatabase, $sDriver , $sServer , $sPort )

    _Example_1_RecordsetToConsole($sConnectionString, "SELECT * FROM city ")
    _Example_2_RecordsetDisplay($sConnectionString, "SELECT * FROM country WHERE Continent = 'Europe'")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc    ;==>_Example_PostgreSQL

Func _Example_PostgreSQL()
    Local $sDSN = 'PostgreSQL35W'
    Local $sDatabase = 'YourBASENAME'
    Local $sServer = ''
    Local $sPort = ''
    Local $sUser = 'YourUserName'
    Local $sPassword = 'YourPassword'

    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc    ;==>_Example_PostgreSQL
#Region Common / internal

Func _Example_1_RecordsetToConsole($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetArray)

    ; Go through the array variable (Recorset Conent)
    Local $iColumn_count = UBound($aRecordsetContent, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordsetContent) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordsetContent[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc    ;==>_Example_1_RecordsetToConsole

Func _Example_2_RecordsetDisplay($sConnectionString, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc    ;==>_Example_2_RecordsetDisplay

Func _Example_3_ConnectionProperties($sConnectionString)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Get all connection properties to Array
    Local $aProperties = _ADO_Connection_PropertiesToArray($oConnection)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Show connection properties
    _ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")

EndFunc    ;==>_Example_3_ConnectionProperties

Func _Example_4_MSSQL($sServer, $sDatabase, $sUser, $sPassword, $sQUERY)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID')
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query directly to Array of Arrays (instead to $oRecordset)
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; Clean Up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    ; Display Array Content with column names as headers
    _ADO_Recordset_Display($aRecordset, 'Recordset content')

EndFunc    ;==>_Example_4_MSSQL
#EndRegion  Common / internal

 

Link to post
Share on other sites

And what you see in SciTE console output pane ?
Please copy it here.

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

oops

I see bug here:

Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath = Default, $sProvider , $sExtProperties , $HDR , $IMEX)

it should be like this:

Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath, $sProvider, $sExtProperties, $HDR, $IMEX)

 

Edited by mLipok

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

That looks to have fixed it, thought I even tried that already.

Now I just need to figure out how to take those results and work with them.

It looks like a standard array display so there should be an array I can work with somewhere, but its not something I have found yet.

I should be able to figure it out looking at the RecordsetToConsole code and reversing parts of it.

Link to post
Share on other sites
Local $aRecordsetAsArray = _ADO_Execute($oConnection, $sQUERY1, True)
_ADO_Recordset_Display($aRecordsetAsArray)
Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray)
For $iRecord_idx = 0 To UBound($aRecordsetContent) - 1
  ....
  ....
  ....
  ....
Next

or like you say from example: RecordsetToConsole

; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, False)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray)

    ; Go through the array variable (Recorset Conent)
    Local $iColumn_count = UBound($aRecordsetContent, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordsetContent) - 1
        ....
        ....
        ....
    Next

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

Hi.

I'm using ADO.au3 in my script for inserting rows from website to Oracle:

...
; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
...
For $x=0 To UBound($arez) - 1
$sSQL = "insert into mytab_oper values(to_date('" & $arez[$x][0] & "','DD.MM.YYYY HH24:MI')," & _
                                             "'" & StringStripWS($arez[$x][1],1) & "'," & _
                                             "'" & StringStripWS($arez[$x][2],1) & "'," & _
                                             "to_date('" & $arez[$x][3] & "','DD.MM.YYYY')," & _
                                             "to_number('" & StringStripWS($arez[$x][4],8) & "')," & _
                                             "'" & StringRegExpReplace(StringStripWS($arez[$x][5],1),"'","''") & "'," & _
                                             "to_number('" & StringStripWS($arez[$x][6],8) & "')," & _
                                             "'" & StringRegExpReplace(StringStripWS($arez[$x][7],1),"'","''") & "'," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][8],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][9],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][10],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][11],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][12],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][13],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][14],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][15],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][16],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][17],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][18],",",".") & "')," & _
                                             "to_number('" & StringRegExpReplace($arez[$x][19],",",".") & "')," & _
                                              "'" & StringStripWS($arez[$x][20],1) & "')"
;ConsoleWrite('$sSQL = '&$sSQL & @CRLF)
_ADO_Execute($oConnection, $sSQL)
Next
...

and receive an error message:

###############################
ADO.au3 v.2.1.13 BETA (1155) : ==> COM Error intercepted !
$oADO_Error.description is:    [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column

$oADO_Error.windescription:    Ошибка.

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

How do I display only the description of an error, ie, value $ oADO_Error.description in a log file, or at least in a variable?

 

Link to post
Share on other sites
; You can use your own COMErrorHandler instead internal ADO.au3 UDF COMError Handler - _ADO_COMErrorHandler
_ADO_ComErrorHandler_UserFunction(_ErrFunc)
MsgBox(0, 'Last COM Error description:', _ErrDescription())

Func _ErrFunc($oError)
    ConsoleWrite( _
            @ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & _
            "$oError.description is: " & @TAB & $oError.description & @CRLF & _
            "$oError.windescription: " & @TAB & $oError.windescription & @CRLF & _
            "$oError.number is: " & @TAB & Hex($oError.number, 8) & @CRLF & _
            "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "$oError.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "$oError.source is : " & @TAB & $oError.source & @CRLF & _
            "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF _
            )
    _ErrDescription($oError.description) ; store description to use it outsided UDF in your own function
EndFunc   ;==>_ErrFunc

Func _ErrDescription($sDescription = Default)
    Local Static $sDescription_static = ''
    If $sDescription <> Default Then $sDescription_static = $sDescription
    Return $sDescription_static
EndFunc

 

EDIT:
Welcome to the fourm @Vitorio.

Edited by mLipok
Fixed issue in:  Func _ErrFunc($oError)

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

Nice to hear that :)

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

Hello,

I have here a snippet that is not running at the moment because (as mentioned before in this thread) $iRowsCountisn't working.(output is -1)
Maybe I'm using ADO wrong , I'm rather new at using ADO and I'm open for sugestions :)
code :

#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#Include <GuiComboBoxEx.au3> ; for _GUICtrlComboBoxEx_ResetContent
#include <Array.au3>
#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

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

$Form1 = GUICreate("Form1", 330, 86, 192, 124)
   $cmb_1 = GUICtrlCreateCombo("Combo1", 16, 16, 145, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL))
   $cmb_2 = GUICtrlCreateCombo("Combo2", 176, 16, 145, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL))
   $btn_fill = GUICtrlCreateButton("fill combo", 40, 48, 75, 25)
   $btn_clc = GUICtrlCreateButton("clear combo", 200, 48, 75, 25)
GUISetState(@SW_SHOW)


While 1
   $nMsg = GUIGetMsg()
   Switch $nMsg
      Case $GUI_EVENT_CLOSE
         Exit
      Case $btn_fill
         get_from_db()
      Case $btn_clc
         _GUICtrlComboBoxEx_ResetContent(GUICtrlGetHandle($cmb_1))
         _GUICtrlComboBoxEx_ResetContent(GUICtrlGetHandle($cmb_2))
   EndSwitch
WEnd


Func get_from_db()

   Local $sDriver = 'SQL Server'
   Local $sDatabase = "db_name"
   Local $sServer = "127.0.0.1"
   Local $sUser = "username"
   Local $sPassword = "pass"

   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 $aRecordset = _ADO_Execute($oConnection, "SELECT TOP 10 ID,username,email FROM users;", False)
   Local $aRecordsetArray = _ADO_Recordset_ToArray($aRecordset, False)
   Local $aresult = $aRecordsetArray[2]
   Local $iColumnsCount = $aRecordset.Fields.Count
   Local $iRowsCount = $aRecordset.RecordCount
   ; Clean Up
   _ADO_Connection_Close($oConnection)
   $oConnection = Null

   MsgBox($MB_SYSTEMMODAL, "Test", "$iRowsCount = " & $iRowsCount & " $iColumnsCount = " & $iColumnsCount)

   Dim $username_array[0]
   Dim $email_array[0]

   Local $r = 0
   Do
      _ArrayAdd($username_array, $aresult[$r][1])
      _ArrayAdd($email_array, $aresult[$r][2])
      $r = $r + 1
   Until $r = 10;should been $iRowsCount  but this is -1

   $username_string = _ArrayToString($username_array, "|")
   $email_string = _ArrayToString($email_array, "|")

   GuiCtrlSetData($cmb_1, $username_string)
   GuiCtrlSetData($cmb_2, $email_string)
EndFunc

Is there somebody that can help me whit making this snippet work properly?

Edited by FMS

as finishing touch god created the dutch

Link to post
Share on other sites

1. Why you are using ; char in SQL Statement ?

2. What you see in SciTE console output pane ?

3.

Try to use some more error checking

Local $aRecordset = _ADO_Execute($oConnection, "SELECT TOP 10 ID,username,email FROM users;", False)
   If @error Then MsgBox($MB_ICONERROR, 'EXECUTE', '@error = ' & @error & @CRLF & '@extended = ' & @extended)

   Local $aRecordsetArray = _ADO_Recordset_ToArray($aRecordset, False)
   If @error Then MsgBox($MB_ICONERROR, 'RS To ARRAY', '@error = ' & @error & @CRLF & '@extended = ' & @extended)

   Local $aresult = $aRecordsetArray[2]
   Local $iColumnsCount = $aRecordset.Fields.Count
   Local $iRowsCount = $aRecordset.RecordCount

 

Edited by mLipok

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

I made a test on my base:

Local $oRecordset = _ADO_Execute($oConnection, "SELECT TOP 10 * from [baza].dbo.sprawy;", False)
   Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
   Local $aresult = $aRecordsetArray[2]
   Local $iColumnsCount = $oRecordset.Fields.Count
   MsgBox(0, '$iColumnsCount', $iColumnsCount)
   Local $iRowsCount = $oRecordset.RecordCount
    ; Clean Up
   _ADO_Connection_Close($oConnection)
   $oConnection = Null

All works well.

You should show SciTE console output pane then I think be able to help.

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites
5 minutes ago, mLipok said:

I made a test on my base:

Local $oRecordset = _ADO_Execute($oConnection, "SELECT TOP 10 * from [baza].dbo.sprawy;", False)
   Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
   Local $aresult = $aRecordsetArray[2]
   Local $iColumnsCount = $oRecordset.Fields.Count
   MsgBox(0, '$iColumnsCount', $iColumnsCount)
   Local $iRowsCount = $oRecordset.RecordCount
    ; Clean Up
   _ADO_Connection_Close($oConnection)
   $oConnection = Null

All works well.

You should show SciTE console output pane then I think be able to help.

 

sorry sorry,....... i made a crusial typo :S
I meant that $iRowsCount has a output of -1 :(

(i edited the question sorry again)

Edited by FMS

as finishing touch god created the dutch

Link to post
Share on other sites

Trying to figure this out.

Now I found this:

https://msdn.microsoft.com/en-us/library/ms676701(v=vs.85).aspx

Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. 

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites

You can use:

Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
   Local $iRowsCount = @extended

or:

;~    Local $aresult = $aRecordsetArray[2]
;~    Local $aresult = $aRecordsetArray[$ADO_RS_ARRAY_RSCONTENT]
   Local $aresult = _ADO_RecordsetArray_GetContent($aRecordsetArray)
   Local $iColumnsCount = UBound($aresult,$UBOUND_COLUMNS)
   Local $iRowsCount = UBound($aresult,$UBOUND_ROWS)

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

Link to post
Share on other sites
16 minutes ago, mLipok said:

You can use:

Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
   Local $iRowsCount = @extended

or:

;~    Local $aresult = $aRecordsetArray[2]
;~    Local $aresult = $aRecordsetArray[$ADO_RS_ARRAY_RSCONTENT]
   Local $aresult = _ADO_RecordsetArray_GetContent($aRecordsetArray)
   Local $iColumnsCount = UBound($aresult,$UBOUND_COLUMNS)
   Local $iRowsCount = UBound($aresult,$UBOUND_ROWS)

 

THANKS!!! both working like a charm!!!!
But , correct me iff I'm wrong ;
both codes work but it's better to use the second because @extended could also something else iff a error occurs????

as finishing touch god created the dutch

Link to post
Share on other sites

Any script should always check for @error from any function that set @error

Here you have example for this case:

Local $iRowsCount = 0
Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)
If @error Then
    ....
Else    
    $iRowsCount = @extended
    ......
EndIf

 

Signature beginning:
Please remember: "AutoIt".....  Wondering who uses AutoIt and what it can be used for ?
* GHAPI UDF - modest beginning - communication with GitHub REST API Forum Rules *
Include Dependency Tree (Tool for analyzing script relations)
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  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 *

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-03-17

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...