Return Record count?! 1

[PogoWolf]

Hey all!

with an Access/SQL Statment:
"SELECT Count(*) AS RowCnt FROM Users;"

How can I display the return, using ADO and the RecordSet
Object?

I thought it would be something like:
Response.Write RowCnt
or
Response.Write rs!RowCnt

But those two don't seem to work?


******
Darkness... Bleakness...
and Plastic forks...
--The PogoWolf
******
Darkness... Bleakness...
and Plastic forks...
--The PogoWolf

 

[JuanitaC]

After you execute the SQL, the syntax for the recordset is:

Response.Write(rs("RowCnt"))

Where rs is your recordset... hope this helps

 

[PogoWolf]

That was it! thank you!!!

along the same lines though..
Isn't RS("RowCnt&quot the same as RS!RowCnt?

and if so, the why didn't my older code work?
and if not.. what's the differance? =)

Thank you again for the help!
******
Darkness... Bleakness...
and Plastic forks...
--The PogoWolf

 

[vasah20]

you're referencing the RS!RowCnt like it was VB. unfortunately this is VBScript, so the ! isn't recognized in the language.

One of thouse subtle things you eventually learn to live with

leo

 

[PogoWolf]

well, poop.. hehehe =) but, at least I know now why the old code didn't work!! thanks Vasah20 ******
Darkness... Bleakness...
and Plastic forks...
--The PogoWolf

 

[Gamera9]

Well I could never make RowCount work either so I was going to suggest my time-proven:
<%
i = 0
rs.MoveFirst
do while not rs.EOF
i = i+1
rs.MoveNext
loop
Response.Write(&quot;<p># of Records -->&quot; & i)
%>
It's only 7 lines longer and additional 2% processor resources!!
I think I'll try your ideas from now on.

 

[vasah20]

Gamera-
in order to make rowcount work, you need to specify the correct cursor. Or else, rowcount returns -1, since the connection is a firehose type (forward only, read-only).

typically you don't want to loop through the recordset unnecessarily, since you want to save the processor resources for more intensive stuff. (I know it's only 2%, but every little bit helps )

Anyhow, if you want the Rs.Rowcount to work, you need to open the recordset with either and adOpenStatic or adOpenKeyset cursor (i don't remember if adOpenDynamic works).

ex:
Rs.Open sqlstring, conn, adOpenKeyset, adLockReadOnly
Response.write RS.RecordCount

This will return the number of records in the recordset, and also assumes that you have #include the adovbs.inc file

hope this helps
leo

 

[Gamera9]

Thanks Leo
I was being facetious when I said &quot;2% processor resources&quot; - actually I don't want to cycle recordsets anymore than absolutely necessary as you can imagine. I was always getting a -1 return value just like you said.
One of the reasons I I was counting recordsets for one particular app was to detect a null recordset - my isnull(rs) or isnull(&quot;rs&quot wasn't working?!
Thanks for the help;
Nelson

 

[vasah20]

IsNull won't work for a recordset because you explicitly create it.

IsNull checks to see if the variable in question has been initialized or not.

ex:
dim notInit, rsInit
set rs = server.createobject(&quot;adodb.recordset&quot

If IsNull(notInit) then
Response.write &quot;notInit is null<br>&quot;
Else
Response.write &quot;notInit is not null<br>&quot;
end if

If IsNull(rsInit) then
Response.write &quot;rsInit is null<br>&quot;
Else
Response.write &quot;rsInit is not null<br>&quot;
end if

the output for this would be:
notInit is null
rsInit is not null

IsNull works for any value, not just objects, and pretty just checks to make sure that the variable contains something.

Anyhow -
The way that I check for a null recordset is by checking for RS.BOF and RS.EOF at the same time. Since a Null recordset is pretty much empty, I just see if it's empty. I do it this way because you can use the default cursor, which gives you the best performance.

Hope this helps-
leo

 

[PogoWolf]

-vasah20

IF I may ask soemthing here about something you said above:
&quot;in order to make rowcount work, you need to specify the correct cursor. Or else, rowcount returns -1, since the connection is a firehose type (forward only, read-only). &quot;

I'm running IISs 5.0 with FP Extenstions making this connection:

Dim RS, Conn
Set Conn = Server.CreateObject(&quot;ADOdb.Connection&quot
Conn.Open &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ= URL\Foodb.mdb;&quot;
Set RS = Server.CreateObject(&quot;ADOdb.recordset&quot

Dim Query
Query = &quot;Select COunt(*) AS RowCnt FROM Users&quot;
RS.Open Query, Conn


and this works with example above, but I'm not setting a cursor type. I assume that either ADO or the Front Page Extensions (which I really think have NOTHING to do with anything here) create the cursor type automaticly? ******
Darkness... Bleakness...
and Plastic forks...
--The PogoWolf

 

[Gamera9]

Leo

Please write a short example of checking a recordset for BOF and EOF &quot;at the same time&quot;. I know what you're saying but I can't visualize the argument.

Nelson

 

[vasah20]

Pogo -
oh yeah - that'll work.
Just reference it like RS(&quot;RowCnt&quot and that'll return the number of rows in Users.

The rowcount that I'm in reference to is really RecordCount, and it's a property of the recordset object. If you are going to use the property, then you need to specify the correct cursor. But that doesn't seem to apply to your situation.

Nelson -
In order to check for BOF and EOF, just throw it into an if statement.

If RS.BOF and RS.EOF then 'empty recordset
Response.write &quot;Recordset is empty&quot;
Else
'do something here
End If


hope I was able to answer some of your questions
leo

 

[Gamera9]

Thanks very much Leo; that was a great help
Sincerely
Nelson

 

[PogoWolf]

Works for me! =) thanks, Leo!

--Matt
******
Darkness... Bleakness...
and Plastic forks...
--The PogoWolf