RecordCount returns -1 even if supports adApproxPosition... 2

[IEAN]

Hi all,

When I do a RecordCount on my recordset I get a -1 value as return.... It does supports adApproxPosition, and the recordset object is opened... I don't know why it returns a -1 value. Please advice.

Thanks!

 

[Veep]

This will explain:

http://www.aspfaq.com/show.asp?id=2193

 

[IEAN]

Thanks Veep! Bingo again!

Could you please explain briefly what the line in red means? It is working but I would just like to know how did the codes fixed the problem. (Sorry...by eager to learn newbie)

<%
numrecords = 0
set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;<connection string>&quot;
sql = &quot;SELECT COUNT(field) FROM table WHERE [...]&quot;
set rs = conn.execute(sql)
if not rs.eof then numrecords = rs(0)
sql = &quot;SELECT whatever FROM table WHERE [...]&quot;
set rs = conn.execute(sql)
response.write(&quot;There were &quot; & numrecords & &quot; matches.&quot

 

[Veep]

You're using the SQL function COUNT() to get the number of records based on the condition in your SQL statement. This query is only going to return 1 field/resultset. You're gonna find that a lot of stuff is zero based (like this example). rs(0) is the 1 returned value.

If you did something like:
sql=&quot;SELECT firstname, lastname from myTable Where[...]&quot;
set rs=conn.execute(sql)
If not rs.eof then
fName=rs(0)
lName=rs(1)
end if

rs(0) will be the first item selected and rs(1) will be the second. Hope I'm making sense, I attained brain-dead about 45 minutes ago.

 

[IEAN]

Yes it made perfect sense! Thanks Veep! You've really gotta teach me how to explain things correctly even when I am in a state of brain-deadness. Thx again!

 

[medic]

I also notice this problem of the RecordCount property. You can make it return the correct figure if you use a client-side cursor (regardless of what type of cursor you use):

set rs = server.CREATEOBJECT(&quot;ADODB.Recordset&quot
rs.cursorlocation = 3 'adUseClient

Just my 2 cents.

Medic

 

[IEAN]

Thanks Medic!