Sign in to follow this  
Followers 0
fcjoe

SQL Query RecordCount

5 posts in this topic

Hello,

I'm just learning how to run SQL queries through an ODBC connection and am running into a problem. I want to get the row count of the query so I can create an array to store the results. I thought I could do this with RecordCount but it always comes back as -1. Not sure if it matter but I'm quering a Lotus Notes Database. My test code is below. MsgBox(0,"Count",.RecordCount) always returns -1

Is there a way to get the row count a head of time?

Thanks,

Joe

Func Test()
    Local $myDB, $KeyHolderQuery, $QueryResults
    Local $Type, $Model, $InvNum, $SerialNum, $SoftIDs, $PurchaseDate
    Local $arrSoftID
    Local $Output = ""
    $myDB = ObjCreate("ADODB.Connection")
    $myDB.Open ("ITAssetDB")

    $KeyHolderQuery = "Select type, model, InventoryNumber, serial, SoftIDs, purchaseDate From Computer Where v_username = 'Laptop Keyholder - Ottawa'"
    $QueryResults = $MyDB.Execute($KeyHolderQuery)

    With $QueryResults
        If .RecordCount Then
            MsgBox(0,"Count",.RecordCount)
            While Not .EOF
                $Type = .Fields("type").Value
                $Model = .Fields("model").Value
                $InvNum = .Fields("InventoryNumber").Value
                $SerialNum = .Fields("serial").Value
                $SoftIDs = .Fields("SoftIDs").Value
                $PurchaseDate = .Fields("purchaseDate").Value
                $Output = $Output & $Type & @TAB & $Model & @TAB & $InvNum & @TAB & $SerialNum & @TAB & $SoftIDs & @TAB & $PurchaseDate & @CRLF
                .MoveNext
            WEnd
        EndIf
    EndWith

    MsgBox(0,"",$Output)
    $myDB.Close
EndFunc

Share this post


Link to post
Share on other sites



Hello,

I'm just learning how to run SQL queries through an ODBC connection and am running into a problem. I want to get the row count of the query so I can create an array to store the results. I thought I could do this with RecordCount but it always comes back as -1. Not sure if it matter but I'm quering a Lotus Notes Database. My test code is below. MsgBox(0,"Count",.RecordCount) always returns -1

Is there a way to get the row count a head of time?

Thanks,

Joe

Func Test()
    Local $myDB, $KeyHolderQuery, $QueryResults
    Local $Type, $Model, $InvNum, $SerialNum, $SoftIDs, $PurchaseDate
    Local $arrSoftID
    Local $Output = ""
    $myDB = ObjCreate("ADODB.Connection")
    $myDB.Open ("ITAssetDB")

    $KeyHolderQuery = "Select type, model, InventoryNumber, serial, SoftIDs, purchaseDate From Computer Where v_username = 'Laptop Keyholder - Ottawa'"
    $QueryResults = $MyDB.Execute($KeyHolderQuery)

    With $QueryResults
        If .RecordCount Then
            MsgBox(0,"Count",.RecordCount)
            While Not .EOF
                $Type = .Fields("type").Value
                $Model = .Fields("model").Value
                $InvNum = .Fields("InventoryNumber").Value
                $SerialNum = .Fields("serial").Value
                $SoftIDs = .Fields("SoftIDs").Value
                $PurchaseDate = .Fields("purchaseDate").Value
                $Output = $Output & $Type & @TAB & $Model & @TAB & $InvNum & @TAB & $SerialNum & @TAB & $SoftIDs & @TAB & $PurchaseDate & @CRLF
                .MoveNext
            WEnd
        EndIf
    EndWith

    MsgBox(0,"",$Output)
    $myDB.Close
EndFunc
As usual, there is more than one way to do everything. When you use .Execute on a connection object (vice .Open on a record set object) the result is always a read-only, forward-only record set that always returns -1 for .RecordCount. This is intended to make it quicker and have lighter overhead.

Create a new RecordSet object, and use .Open to run the query and populate the record set. Then you can get .RecordCount.

Another option is .GetRows which returns a 2D array of all records in the set. But I haven't tested that method with the read-only, forward-only set from an .Execute to be sure it's available.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

PsaltyDS,

I just tried GetRows and it worked perfectly, didn't know that was even an option.

Thank you,

Joe

Share this post


Link to post
Share on other sites

PsaltyDS,

I just tried GetRows and it worked perfectly, didn't know that was even an option.

Thank you,

Joe

I had meant to include a link to where all that ADO wisdom came from: w3schools.com ADO Tutorial

This particular topic has come up before, and also applies to certain WQL (WMI) queries that return forward only collection objects.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


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
Sign in to follow this  
Followers 0