Jump to content

May I have some help about Recordset question?


Recommended Posts

Example

Global $oADOConnection = ObjCreate("ADODB.Connection")

...

...

_FunctionA()

 

Func _FunctionA()

$oADOConnection.Open($sADOConnectionString)

$sADOSQL  = "Select * from DB"

$oADORecordset.Open($sADOSQL, $oADOConnection, $iCursorType, $iLockType)

 

With $oADORecordset

While Not .EOF

If TEST1 = 0 Then

Return 0

Else 

Return 1

Endif

WEND

ENDWITH

 

    $oADORecordset.Close ; Close the recordset
    $oADORecordset = 0 ; Release the recordset object
    $oADOConnection.Close ; Close the connection
    $oADOConnection = 0 ; Release the connection object

 

ENDFUNC

 

The script can be executed successfully. But I believe the script will end at position "Return 0" or "Return 1".

So which means the close connection won't be run. Am I right here?

And any problem will be happen here? Thanks!

 

Link to post
Share on other sites
54 minutes ago, WinMacBear said:

The script can be executed successfully

No, not the script you provide here ;).

58 minutes ago, WinMacBear said:

But I believe the script will end at position "Return 0" or "Return 1". So which means the close connection won't be run. Am I right here?

Yes.

Musashi-C64.png

"In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move."

Link to post
Share on other sites

You should try my ADO.au3 UDF.

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

Hey,

That looks like a mixture of  AutoIt and VBA. 

You could use a variable to hold the return value. First assign it to the "all passed" value, and only change it when your fail condition is met. Also exit loop if only one instance of condition = true (TEST1 = 0 in this case) is necessary to return 0.

Local $iReturn = 1

With $oADORecordset
  While Not .EOF
    If TEST1 = 0 Then
    	$iReturn = 0
        ExitLoop ; no need to continue further if condition is met
    Else 
    	; Unless you need other return values, this part is unnecessary now
        ; $iReturn = 1
    Endif
  WEND
ENDWITH

$oADORecordset.Close ; Close the recordset
$oADORecordset = 0 ; Release the recordset object
$oADOConnection.Close ; Close the connection
$oADOConnection = 0 ; Release the connection object

Return $iReturn

 

Edited by GokAy
Link to post
Share on other sites
#include <Date.au3>
Global Const $iCursorType = 0
Global Const $iLockType = 2
Global Const $iOptions = 2

Global $oADOConnection = ObjCreate("ADODB.Connection")
Global $oADOConnectionEvent = ObjCreate($oADOConnection, "$oADOConnection_")
Global $oADORecordset = ObjCreate("ADODB.Recordset")
Global $sFilename = @ScriptDir & "\test.xls"
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=Yes;"'


Global Const $querryid = 2

checkExcelCell()

Func checkExcelCell()

    $oADOConnection.Open($sADOConnectionString)
    Global $sADOSQL = "Select * FROM [testable$]"

    $oADORecordset.Open($sADOSQL, $oADOConnection, $iCursorType, $iLockType)

    With $oADORecordset

        While Not .EOF
            If  .Fields(0).Value = $querryid Then

                .Fields(3).Value = _NowCalc()
                ExitLoop ; Terminate a While/Do/For loop.
            Else

                .Fields(4).Value = "not found"


            EndIf
            .MoveNext
        WEnd

    EndWith


    $oADORecordset.Close ; Close the recordset
    $oADORecordset = 0 ; Release the recordset object
    $oADOConnection.Close ; Close the connection
    $oADOConnection = 0 ; Release the connection object
EndFunc

 

Link to post
Share on other sites

New

ADO_EXAMPLE_WinMacBear_Test_XLS.au3

#AutoIt3Wrapper_UseX64=N
#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>
#include "ADO.au3"

; SetUP ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_Example_MSExcel()

Func _Example_MSExcel()

    Local $sFileFullPath = @ScriptDir & '\ADO_EXAMPLE_WinMacBear_TEST.xls' ; Here put FileFullPath to your Access File or use Default to open FileOpenDialog
    Local $sProvider = 'Microsoft.Jet.OLEDB.4.0'
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

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

    _Example_2_RecordsetDisplay($sConnectionString, "Select * FROM [testable$]")

EndFunc   ;==>_Example_MSExcel

#Region Common / internal

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

#EndRegion Common / internal

image.png.88136ed93ba699054ef475f8d3a8e498.png

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
3 hours ago, WinMacBear said:

with ExitLoop I will get the $oADORecordset.Close error.

The excel has been updated even with the error but I have to open the test.xls file again to get the result.

without the ExitLoop I can get the result immediately without close it and open it again. 

 

change this

$oADORecordset.Close

to this

If IsObj($oADORecordset) And $oADORecordset.State > 0 Then $oADORecordset.Close

 

the same with ObjConnection ...

 

EDIT:

Quote

State: adStateClosed=0 adStateOpen=1 adStateConnecting=2 adStateExecuting=4 adStateFetching=8

 

Edited by Zedna
Link to post
Share on other sites

Hey WinMacBear,

Sorry if I confused you with the "close connection" part of my first post, should have left those 4 lines out as I merely copy pasted from your post. The important part was how to return different values, and exitloop for avoiding unnecessary computation (if that was the case).

I am an AutoIt newbie, and have very little knowledge of connecting to databases. :) Most of my posts are about the algorithm involved and usually not in AutoIt syntax. I leave the conversion to the experts and/or the person asking for advice if they deem it worthy. Please consider this as a late disclaimer.

Link to post
Share on other sites

Thanks everyone!

Hi mLipok,

I am trying to rewrite with your ADO.au3  Like this...it seems passed test. but I am not sure if it could be anything wrong..

 

#AutoIt3Wrapper_UseX64=N
#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>
#include "ADO.au3"
#include <Date.au3>

; SetUP ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

Global Const $iCursorType = 0
Global Const $iLockType = 2
Global Const $iOptions = 2

Global $oADOConnection = ObjCreate("ADODB.Connection")
Global $oADOConnectionEvent = ObjCreate($oADOConnection, "$oADOConnection_")
Global $oADORecordset = ObjCreate("ADODB.Recordset")
Global $sFilename = @ScriptDir & "\test.xls"
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=Yes;"'


Global $queryid = 20
Global $querytable = "testable"



checkExcelCell()

Func checkExcelCell()

    $oADOConnection.Open($sADOConnectionString)
    Global $sADOSQL = "Select * FROM [" & $querytable & "$] where ID =" & $queryid
    ;MsgBox(0, "",  "Select * FROM [" & $querytable & "$] where ID =" & $queryid  )


    $oADORecordset.Open($sADOSQL, $oADOConnection, $iCursorType, $iLockType)

    
    
    if __ADO_Recordset_IsNotEmpty($oADORecordset) = "Ture" Then

        With $oADORecordset

            While Not .EOF

                .Fields(3).Value = _NowCalc()

            .MoveNext
            WEnd

        EndWith

    Else

        Msgbox(0, "False" , "No record found")

    EndIf
    


    _ADO_Connection_Close($oADORecordset)
    $oADORecordset = Null ; Release the recordset object

    _ADO_Connection_Close($oADOConnection)
    $oADOConnection = Null ; Release the connection object

EndFunc

 

Maybe I missed something in your file but I can not find a function to update the file...

Is there any simply way to update the recordset? 

Like $oADORecordset.Fields(3).Value = _NowCalc()

Thank you!

 

 

Link to post
Share on other sites

A question just to ensure that the most obvious solution is not overlooked : "Is Excel installed on your computer ?"

If so, please have a look at the Excel-UDF that comes with AutoIt by default. Wiki = https://www.autoitscript.com/wiki/Excel_UDF

 

Musashi-C64.png

"In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move."

Link to post
Share on other sites

Excel is not needed as ADO is used.

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
1 hour ago, mLipok said:

Excel is not needed as ADO is used.

I know that ;). But if, contrary to expectations, an Excel installation is available, the Excel UDF would be a reasonable alternative.

As I said before, I just wanted to make sure that @WinMacBear did not overlook this option.

Musashi-C64.png

"In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move."

Link to post
Share on other sites
On 11/10/2020 at 4:59 AM, WinMacBear said:

Maybe I missed something in your file but I can not find a function to update the file...

If __ADO_Recordset_IsNotEmpty($oADORecordset) = "Ture" Then

1. typo.. True , not Ture

2. mistake not "True" but True (not string but bool).

If __ADO_Recordset_IsNotEmpty($oADORecordset) Then

3. in XLS you provided there is no ID=20

4. wrong EVENT's usage

Global $oADOConnectionEvent = ObjCreate($oADOConnection, "$oADOConnection_") ; this Your USAGE will not catch any event

 

 

btw.
I'm working on finall working example.

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

ADO_EXAMPLE_WinMacBear_Test2_XLS.au3

#AutoIt3Wrapper_UseX64=N
#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 <AutoItConstants.au3>
#include <Date.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

; SetUP ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

Global $oADOConnection = _ADO_Connection_Create()
Global $oADORecordset = _ADO_Recordset_Create()
Global $oADOConnection_EventsHandler = ObjEvent($oADOConnection, "_ADO_EventHandler_")

#Region - Work in progress
# This following line is commented out because it fires: AutoIt3.exe ended.rc:-1073741819
;~ Global $oADORecordset_EventsHandler = ObjEvent($oADORecordset, "_ADO_EventHandler_")
#EndRegion - Work in progress

Global $sFilename = @ScriptDir & "\ADO_EXAMPLE_WinMacBear_TEST.xls"
Global $sADOConnectionString = _ADO_ConnectionString_Excel($sFilename, 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0', 'Yes')

checkExcelCell("testable", 10)

Func checkExcelCell($querytable, $queryid)
    ; check - lets see what XLS contain
    _Example_2_RecordsetDisplay($sADOConnectionString, "Select * FROM [testable$]")

    Local $iResult = _ADO_Connection_OpenConString($oADOConnection, $sADOConnectionString)
    If @error Then Return SetError(@error, @extended, $iResult)

    Local $sADOSQL = "SELECT * FROM [" & $querytable & "$] WHERE ID =" & $queryid

    $oADORecordset.Open($sADOSQL, $oADOConnection, $ADO_adOpenDynamic, $ADO_adLockOptimistic)

    If __ADO_Recordset_IsNotEmpty($oADORecordset) Then
        With $oADORecordset
            While Not .EOF
                .Fields(3).Value = _NowCalc()
                .MoveNext
            WEnd
        EndWith
    Else
        MsgBox($MB_OK + $MB_TOPMOST + $MB_ICONINFORMATION, 'Information', 'No record found')
    EndIf

    ; check again - XLS content
    _Example_2_RecordsetDisplay($sADOConnectionString, "Select * FROM [testable$]")

    _ADO_Connection_Close($oADORecordset)
    $oADORecordset = Null ; Release the recordset object

    _ADO_Connection_Close($oADOConnection)
    $oADOConnection = Null ; Release the connection object

EndFunc   ;==>checkExcelCell

#Region - MISC
Func _ADO_EventHandler_FieldChangeComplete($iFields, ByRef $aFields, ByRef $oError, $i_adStatus, ByRef $oRecordset)
    ;https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/willchangefield-and-fieldchangecomplete-events-ado?view=sql-server-ver15
    #forceref $iFields, $aFields, $oError, $i_adStatus, $oRecordset
    ConsoleWrite('! ' & @ScriptLineNumber & @CRLF)
EndFunc   ;==>_ADO_EventHandler_FieldChangeComplete

Func _ADO_EventHandler_WillChangeField($iFields, ByRef $aFields, $i_adStatus, ByRef $oRecordset)
    ;https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/willchangefield-and-fieldchangecomplete-events-ado?view=sql-server-ver15
    #forceref $iFields, $aFields, $i_adStatus, $oRecordset
    ConsoleWrite('! ' & @ScriptLineNumber & @CRLF)
EndFunc   ;==>_ADO_EventHandler_WillChangeField

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

Testing files:  WinMacBear_TestingFiles.ZIP

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

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.

×
×
  • Create New...