Jump to content

Recommended Posts

Posted

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!

 

Posted
  On 11/8/2020 at 7:04 AM, WinMacBear said:

The script can be executed successfully

Expand  

No, not the script you provide here ;).

  On 11/8/2020 at 7:04 AM, 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?

Expand  

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

Posted

You should try my ADO.au3 UDF.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

Posted (edited)

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
Posted

HI mLipok,

Thanks for your advice.

I am a newbie to autoit. I have read the post for your ADO.au3 before... but i do not sure how to use at that time. Maybe I should go over your ADO.au3 again.

Thanks a lot!

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

 

Posted

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. 

Posted (edited)

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 ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

Posted (edited)
  On 11/9/2020 at 7:57 AM, 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. 

Expand  

 

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

Expand  

 

Edited by Zedna
Posted

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.

Posted

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!

 

 

Posted

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

Posted

Excel is not needed as ADO is used.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

Posted
  On 11/10/2020 at 8:00 AM, mLipok said:

Excel is not needed as ADO is used.

Expand  

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

Posted (edited)
  On 11/10/2020 at 3:59 AM, WinMacBear said:

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

Expand  
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 ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

Posted (edited)

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 ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Codefor other useful stuff click the following button:

  Reveal hidden contents

Signature last update: 2023-04-24

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