RecordCount

[shank]

I switched to parameterized queries and now having issues with RecordCount.
Regardless of the records returned, RecordCount = -1
How do I get an actual count?
thanks

<%
Dim rsProdCount__OD
rsProdCount__OD = "1"
If (Request("si") <> "") Then
rsProdCount__OD = Request("si")
End If
%>
<%
Dim rsProdCount
Dim rsProdCount_cmd
Dim rsProdCount_numRows

Set rsProdCount_cmd = Server.CreateObject ("ADODB.Command")
rsProdCount_cmd.ActiveConnection = CONNSTRING
rsProdCount_cmd.CommandText = "{call admin.stpProductCount(?)}"
rsProdCount_cmd.Prepared = true
rsProdCount_cmd.Parameters.Append rsProdCount_cmd.CreateParameter("param1",
200, 1, 255, rsProdCount__OD) ' adVarChar

Set rsProdCount = rsProdCount_cmd.Execute
rsProdCount_numRows = 0
%>

 

[Bob Barrows [MVP]]

I switched to parameterized queries and now having issues with
RecordCount. Regardless of the records returned, RecordCount = -1
How do I get an actual count?
thanks

<%
Dim rsProdCount__OD
rsProdCount__OD = "1"
If (Request("si") <> "") Then
rsProdCount__OD = Request("si")
End If
%>
<%
Dim rsProdCount
Dim rsProdCount_cmd
Dim rsProdCount_numRows

Set rsProdCount_cmd = Server.CreateObject ("ADODB.Command")
rsProdCount_cmd.ActiveConnection = CONNSTRING
rsProdCount_cmd.CommandText = "{call admin.stpProductCount(?)}"
rsProdCount_cmd.Prepared = true
rsProdCount_cmd.Parameters.Append
rsProdCount_cmd.CreateParameter("param1", 200, 1, 255,
rsProdCount__OD) ' adVarChar
Set rsProdCount = rsProdCount_cmd.Execute
rsProdCount_numRows = 0
%>

You need to change the recordset's CursorType to a cursor type that supports
recordcount. The default server-side forward-only cursur you are getting by
using the .Execute method will not support that.

In addition, you are going to too much trouble to pass your parameter values
to that stored procedure, as well as using the poor coding practice of using
an implicit connection object (very bad). do this instead:

Dim cn, rs
set cn=createobject("adodb.connection")
cn.open connstring
set rs=createobject("adodb.recordset")
rs.cursorlocation=3 'adUseClient - forces a static cursor type
cn.DefaultDatabase = "admin"
cn.stpProductCount rsProdCount__OD, rs
'process the recordset. don't forget to clean up.

I would normally post a link to the relevant aspfaq article at this point
but I think the site may still be hacked.

 

[shank]

implicit connection object (very bad). <<
What's wrong with the connection? The code is generated by the newest DW
CS3. I would assume they'd know the difference.

thanks

 

[Bob Barrows [MVP]]

LOL
DW has been generating this type of atrocious code for years!

I have made many posts in this newsgroup about the evils of allowing
implicit connections to be used (which is what happens when one sets an
ActiveConnection property to a string). Here are a few of them:

<http://groups.google.com/groups?as_...81&as_maxd=20&as_maxm=6&as_maxy=2008&safe=off>

 

[Mike Brind [MVP]]

Nope. Ownership of the product moved from Macromedia to Adobe a few years
ago, but it seems they have given up developing the classic ASP text editor.
Probably never developed it beyond the first release of ASP over 10 years
ago. Given that ASP will never change, I doubt Adobe are going to be
bothered tidying up that sort of area. They are more likely to spend time
developing the ColdFusion functionality.

 

[Old Pedant]

Dim cn, rs

set cn=createobject("adodb.connection")
cn.open connstring
set rs=createobject("adodb.recordset")
rs.cursorlocation=3 'adUseClient - forces a static cursor type

REALLY???? Using a client-side cursor *ALSO* forces a static cursor???

I've always done both
rs.cursorlocation = 3 ' adUseClient
rs.cursortype = 3 ' adOpenStatic

The ADO docs say nothing about this (example:
http://msdn.microsoft.com/en-us/library/ms677542(VS.85).aspx
) but I agree that the implication makes sense. After all, if ADO is
managing the cursor and not the DB, then what else would make sense.

So you *can* teach an old dog new tricks?

 

[Bob Barrows [MVP]]

REALLY???? Using a client-side cursor *ALSO* forces a static
cursor???

Errr ... yes.
The only cursor type available with a client-side cursor is static. If you
specify a different type, no error will be raised, but you will get a static
cursor.

When a client-side cursor is specified, ADO creates a cursor using the ADO
Cursor Library. It then uses a firehose cursor to get the data from the
database and populates the cursor, immediately closing the firehose when
complete.

By definition, it is a static cursor because
1. it contains all the data that satisfies the criteria used to retrieve the
data
2. there is no attempt to respond to changes in the database made by other
users.
i.e., it's a collection of static data

I've always done both
rs.cursorlocation = 3 ' adUseClient
rs.cursortype = 3 ' adOpenStatic

Nothing really wrong with being explicit ...

The ADO docs say nothing about this (example:
http://msdn.microsoft.com/en-us/library/ms677542(VS.85).aspx
)

You're right: this article's author doesn't come right out and say it, but
it is implied in the description of server-side cursors:
"These cursors are sometimes very flexible and allow for additional
sensitivity to changes others make to the data source"
Sensitivity to changes are available only in dynamic and keyset cursors.

But this is a weak implication because it leaves the door open for
forward-only cursors. To see where it comes right out and says it, you have
to look here:
http://msdn.microsoft.com/en-us/library/ms681510(VS.85).aspx

....where you will find:
"This is the only type of cursor allowed when you open a client-side
Recordset object." in the description of the Static cursor.

And here:
http://msdn.microsoft.com/en-us/library/ms677593(VS.85).aspx

....where you will find:
"Only a setting of adOpenStatic is supported if the CursorLocation property
is set to adUseClient. "

I'm sure there's other places ... those were the only two I remembered off
the top of my head.

but I agree that the implication makes sense. After all, if ADO is
managing the cursor and not the DB, then what else would make sense.

So you "knew" it

So you *can* teach an old dog new tricks?

I just learned something yesterday: like you, it was something that should
have been obvious if I had thought about it, but I had accepted an
authoritative source that said otherwise. In hindsight, I now realize the
source I had accepted was open to interpretation, and I had accepted what it
said literally rather than attempting to go beyond the literal words.