ADODB Number of Records

[DrParko]

I created a query GUI, largely based on code posted by DaRam. I have it working flawlessly but I wanted it to display the number of records returned. I have tried several different ideas with no luck. I also looked at some ADO sites with no luck. This is two snippets of the code, the connection and query sections.

Global $adoCn = ObjCreate( "ADODB.Connection" )
$adoCn.Properties("Prompt") = 2; 1=PromptAlways, 2=PromptComplete
Global $adoRs = ObjCreate( "ADODB.RecordSet" )
.
.
.
Case $ButtonQuery
            $QueryTxt = GUICtrlRead($EditSQL)
            If $adoRs.State = $adStateOpen Then $adoRs.Close
            GUICtrlSetData($EditResults, "Executing " & $QueryTxt)
            $adoRs = $adoCn.Execute($QueryTxt)
            If $adoRs.State = $adStateOpen Then
               GUICtrlSetData($RowResults, $adoRs.RecordCount)
               $ResultsTxt = ""
               $J = $adoRs.Fields.Count - 1
               For $I = 0 To $J
                     $ResultsTxt = $ResultsTxt & $adoRs.Fields($I).Name & @TAB
               Next;$I
               $ResultsTxt = $ResultsTxt & @CRLF
               $ResultsTxt = $ResultsTxt & $adoRs.GetString(2, -1, @TAB, @CRLF, "Null")
               GUICtrlSetData($EditResults, $ResultsTxt)
               $adoRs.Close
           EndIf

I tried a while loop to count records but couldn't get that to work. With this code, I get a -1 displayed in the $RowResults label. Any ideas?

Thanks in advance..

[PsaltyDS]

Depends on the exact query string you are using. For example, some cursor types (i.e. "ForwardOnly") don't support .RecordCount. Post an otherwise working example for $QueryTxt that gives you good data but no .RecordCount.

[GEOSoft]

http://dundats.mvps.org/autoit/udf_code.aspx?udf=access

Look at _accessCountRecords()

[DrParko]

  On 5/27/2010 at 7:05 PM, 'PsaltyDS said:

Depends on the exact query string you are using. For example, some cursor types (i.e. "ForwardOnly") don't support .RecordCount. Post an otherwise working example for $QueryTxt that gives you good data but no .RecordCount.

PsaltyDS,

The query is variable as it is taken from an input box, but here is one that returns a list of records:

SELECT u.userid, a.viewed_status, u.last_login

from users u, autoemail a

where u.userid = a.userid and u.last_login is not null and a.viewed_status is null

I understand that some queries do not return records and therefore may not .RecordCount. Is there a way to determine that by information within the Record Set?

[PsaltyDS]

Getting -1 for .RecordCount is exactly how it tells you that property is not supported for this recordset. You can just walk through the records in a While/WEnd loop and count them as you go. Or add an SQL query specifically for the COUNT.

[DrParko]

Thanks PsaltyDS. I was able to add a while loop and count. It looks like this now:

Case $ButtonQuery
            $QueryTxt = GUICtrlRead($EditSQL)
            If $adoRs.State = $adStateOpen Then $adoRs.Close
            GUICtrlSetData($EditResults, "Executing " & $QueryTxt)
            $adoRs = $adoCn.Execute($QueryTxt)
            If $adoRs.State = $adStateOpen Then
               $ResultsTxt = ""
               $J = $adoRs.Fields.Count - 1
                For $I = 0 To $J
                     $ResultsTxt &= $adoRs.Fields($I).Name & @TAB
                Next;$I
               $ResultsTxt &= @CRLF
               $RowCount = 0
               While Not $adoRs.EOF
                    For $I = 0 To $J
                        $ResultsTxt &= $adoRs.Fields($I).Value & @TAB
                    Next;$I
                    $ResultsTxt &= @CRLF
                    $RowCount = $RowCount + 1
                    $adoRs.MoveNext
                WEnd
               GUICtrlSetData($RowResults, $RowCount)
               GUICtrlSetData($EditResults, $ResultsTxt)
               $adoRs.Close
           EndIf

Thanks again for your help, not only on this issue, but all of the issues you have responded to. I run into your posts on a regular basis!!

[PsaltyDS]

You're welcome.