How to display the number of records in a recordset? 1

[mattboyslim]

How do I display the number of records in a recordset? The reason I ask is because I need to determine if a recordset contains only 1 record, and if it does, then display a different heading, say "Records" versus "Record".

Here is my SQL query:
==================================
<%
Dim rs_attractionscat
Dim rs_attractionscat_numRows

Set rs_attractionscat = Server.CreateObject("ADODB.Recordset")
rs_attractionscat.ActiveConnection = MM_conn_portalokoboji_STRING
rs_attractionscat.Source = "SELECT * FROM tb_attractions WHERE f_categoryID = " + Replace(Request.QueryString("cat"), "'", "''") + ""
rs_attractionscat.CursorType = 0
rs_attractionscat.CursorLocation = 2
rs_attractionscat.LockType = 1
rs_attractionscat.Open()

rs_attractionscat_numRows = 0
%>
====================================

If I just use rs_attractions.Recordcount, then I get a result of -1, but apparently that is because I'm using MSAccess.

I found some other code, which is shown here:
====================================
<%
'Long recordset counting using a simple loop by Barry Wright
Dim strCount
strCount = 0
While NOT rs_attractionscat.EOF AND NOT rs_attractionscat.BOF
strCount = strCount + 1
rs_attractionscat.MoveNext()
Wend
%>
<%
'Write the amount of records in the Recordset
Response.Write(strCount)
%>
====================================

The problem I have, is that now my recordset for rs_attractionscat no longer works when I add the above code in. Any ideas why?

Thanks in advance,
Matt

 

[onpnt]

RecordCount requires the use of a adOpenStatic Cursor and adLockOptimistic LockType (or 3,3 setting without the adovbs included in the page)

so
rs_attractionscat.CursorType = 3
rs_attractionscat.CursorLocation = 3

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[onpnt]

I pasted the wrong section of your code for the example. sorry



[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mattboyslim]

So what you have above is not correct then? CursorType and LockType need to be 3?

 

[onpnt]

CursorType and LockType need to be 3?

Yeah, that's what I said which was correct. I just grabbed the wrong sections.

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[onpnt]

btw...CursorLocation default is 2 so you can leave that out

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mattboyslim]

OK, so it works even if I leave LockType to 0, but change CursorType to 3. What are the implications of this? I'm not trying to be difficult, I swear, I'm just trying to learn as I go.

 

[onpnt]

LockType at 0 is fine if you are not going to modify any records. 0 = ReadOnly

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mattboyslim]

Sorry, I meant "even if I leave LockType to 1.

 

[onpnt]

I must be all messed up. I haven't looked at ASP in a long time. I meant 1. 0 isn't a setting

w3schools has a great reference for the types and settings. I refer to is commonly when helping out

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mattboyslim]

So in theory, could LockType set to 0 be used to prevent SQL injection attacks?

 

[mattboyslim]

I really wish you could edit posts here. I mean "So in theory, could LockType set to 1 be used to prevent SQL injection attacks?

 

[onpnt]

So in theory, could LockType set to 0 be used to prevent SQL injection attacks?

not in any way shape or form ;-)

SQL Injection is a completely different topic you need to take care of. That would be a good new thread is you wanted to. Glad to hear someone knows and wants to prevent it. If you don't want to get into another thread this link is always good on looking at it and ways to do it

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mattboyslim]

I've printed it out, and will be storing it under my keyboard to take home over the weekend, thanks.

And thanks also for all the help today, you've saved both my forehead and my keyboard a lot of unnecessary pain.

 

[mattboyslim]

onpnt, I have a question regarding what we talked about. I have moved my queries from ADODB.Recordset to ADODB.Command to prevent SQL injection, so how do I specify the CursorType and LockType now?

Here is my new SQL query:
===========================
<%
Dim rs_attractionscat
Dim rs_attractionscat_cmd
Dim rs_attractionscat_numRows

Set rs_attractionscat_cmd = Server.CreateObject ("ADODB.Command")
rs_attractionscat_cmd.ActiveConnection = MM_conn_portalokoboji_STRING
rs_attractionscat_cmd.CommandText = "SELECT * FROM tb_attractions WHERE f_categoryID = ?"
rs_attractionscat_cmd.Prepared = true
rs_attractionscat_cmd.Parameters.Append rs_attractionscat_cmd.CreateParameter("param1", 5, 1, -1, rs_attractionscat__MMColParam) ' adDouble

Set rs_attractionscat = rs_attractionscat_cmd.Execute
rs_attractionscat_numRows = 0
%>
==============================