How to Count of Columns in a recordset ?

[royalcheese]

Hey all

I have a recordset and want to know how many columns are in the returned query.

This is what I have already

'connection String to ALPHA server
cst = "Driver={SQL Native Client};Server=ALPHA;Database=HPR;UID=Admin;PWD=wooyay;"

'Set connection as ADODB
Set cn = CreateObject("ADODB.Connection")

'Open Connection
cn.Open cst

'Set the recordset
Set RsHPR = New ADODB.Recordset

'SQL statement
which = "Select * from [test_table] order by pk asc"
RsHPR.Open which, cn, adOpenKeyset, adLockOptimistic

'Counts Rows and will insert data 
a = 0
If Not RsHPR.EOF And Not RsHPR.BOF Then
Do While (RsHPR.EOF = False)
'Loads all the information from the first field of the record until the EOF
a = a + 1
RsHPR.MoveNext
Loop

Is anything i can do to calculate it ?


Mnay thanks in advance


Chriso the Simple III

 

[Glasgow]

Try rshpr.fields.count perhaps?

 

[royalcheese]

Perfect

Told You i was simple

 

[BobRodes]

More concise (and more efficient) is

With RsHPR
    If Not (.EOF And .BOF) Then
        Do Until .EOF
            'Loads all the information from the first field of the record until the EOF
            a = a + 1
            .MoveNext
        Loop
    End If
End With

The with block adds efficiency because you only have to evaluate the object pointer once for the entire with block, instead of once for each method call.

Bob