Using a RecordSet twice 1

[jon24422531]

Using ASP I make a SQL query to a database table and then return the recordset to a formatted table.

set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={SQL Server};Server=FTLive;Database=WorkLog;Uid=app;Pwd=;" 

Set rsData = Server.CreateObject("ADODB.Recordset")

strSQL = "select FaultNo, Fault, Resolution, UserID, FaultType, .......... etc

This works fine but I would also like to count the number of records returned. Searching the Web gave me this:

<%
Dim strCount 
strCount = 0
While NOT RSData.EOF AND NOT RSData.BOF
strCount = strCount + 1
RSData.MoveNext()
Wend
%>

However, when I use this then it appears that the recordet cannot be used again as the table is empty although the count is > 1

So is there any way I can use it more than once, or will I have to make two queries to SQL, one for data and one for the count of that data?

Many thanks.

Jonathan

http://www.telecom-stuff.com

 

[johnwm]

Your data pointer is at the end of the recordset. Use RSData.MoveFirst to reset it to the start

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[jon24422531]

Johnwm

Many thanks. That works a treat.

Have a little star.

Jonathan

http://www.telecom-stuff.com