May I have some help about Recordset question?

[WinMacBear]

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!

 

[Musashi]

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.

[mLipok]

You should try my ADO.au3 UDF.

[GokAy]

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 November 8, 2020 by GokAy

[WinMacBear]

Hi GokAy!

 

Thank you so much for your reply! You are awesome!

That's exactly what I wanted!

[WinMacBear]

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!

[WinMacBear]

Hi GokAy

I have just test with your way ...but .. i got an error in the end..

$oADORecordset.Close
$oADORecordset^ ERROR

Do you have any idea? Thanks!

 

 

[WinMacBear]

expandcollapse popup

#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

 

[WinMacBear]

test.xls

[WinMacBear]

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. 

[mLipok]

New

ADO_EXAMPLE_WinMacBear_Test_XLS.au3

expandcollapse popup

#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

Edited November 9, 2020 by mLipok

[Zedna]

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 November 9, 2020 by Zedna

[GokAy]

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.

[WinMacBear]

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

 

expandcollapse popup

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

 

 

[Musashi]

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

 

[mLipok]

Excel is not needed as ADO is used.

[Musashi]

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.

[mLipok]

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 November 11, 2020 by mLipok

[mLipok]

ADO_EXAMPLE_WinMacBear_Test2_XLS.au3

expandcollapse popup

#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 November 11, 2020 by mLipok