SQL Query RecordCount

[fcjoe]

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

[PsaltyDS]

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.

[fcjoe]

PsaltyDS,

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

Thank you,

Joe

[PsaltyDS]

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.

[ChrisL]

In my signature there is an ABODB SQL udf it will make your life easier (probably!)