Problems with SQL select and RecordCount

<i><b>Originally posted by : Clueless (aspfree@anwaltonline.com)</b></i><br /><br />I would like to select a certain number of records from a list with a SQL select.<br />After this I would like to know how many records are in this selection. For some reason this turns to be -1. What do I need to change?<br /><br />The lines are:<br />strSQL = "SELECT * FROM links WHERE CatID = " & CatID & " AND Member = " & MemberShow & " ORDER BY Name ASC; "<br />Set objRecord = objConn.Execute (strSQL)<br /><br />A recordcont on objRecord turns -1.<br /><br />Any help is appreciated.<br />

<i><b>Originally posted by : </b></i><br /><br />Try fetching records through recordset & put cursortype as adOpenStatic<br /><br />------------<br />Clueless at 12/18/2000 7:20:46 AM<br /><br /><br />I would like to select a certain number of records from a list with a SQL select.<br />After this I would like to know how many records are in this selection. For some reason this turns to be -1. What do I need to change?<br /><br />The lines are:<br />strSQL = "SELECT * FROM links WHERE CatID = " & CatID & " AND Member = " & MemberShow & " ORDER BY Name ASC; "<br />Set objRecord = objConn.Execute (strSQL)<br /><br />A recordcont on objRecord turns -1.<br /><br />Any help is appreciated.<br />

A recordset that was just opened has no defined cursor position. It also does not know how many recordss are in the recordset unless you jumpt to it's end (sounds logic, no?) Hence, you need to:

rs.MoveLast
rs.MoveFirst

msgbox rs.recordcount

This gives the correct result

Use the cursor type as adOpenKeySet.

This will get the number of records in the table.

But if you cannot change the cusrsor type for any reason follow the given alternative:-
x=0
Do while rs.eof = false
Your ASP code
rs.MoveNext
x = x + 1
Loop
Once you come out of the Loop then the variable x will contain the number of records