RecordCount

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
Fri Jun 20 12:31:42 CDT 2008

shank wrote:
> 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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

shank
Fri Jun 20 13:40:57 CDT 2008

>>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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23WSr$tv0IHA.2084@TK2MSFTNGP06.phx.gbl...
> shank wrote:
>> 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.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Bob
Fri Jun 20 14:22:29 CDT 2008

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_q=implicit+connections&num=10&scoring=r&as_epq=&as_oq=&as_eq=&as_ugroup=&as_usubject=&as_uauthors=Bob+Barrows&lr=&as_drrb=q&as_qdr=&as_mind=1&as_minm=1&as_miny=1981&as_maxd=20&as_maxm=6&as_maxy=2008&safe=off>

shank wrote:
>>> 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]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:%23WSr$tv0IHA.2084@TK2MSFTNGP06.phx.gbl...
>> shank wrote:
>>> 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.
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Mike
Fri Jun 20 15:47:20 CDT 2008

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.

--
Mike Brind
Microsoft MVP - ASP/ASP.NET

"shank" <shank@tampabay.rr.com> wrote in message
news:uSJDwUw0IHA.5564@TK2MSFTNGP06.phx.gbl...
>>>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]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:%23WSr$tv0IHA.2084@TK2MSFTNGP06.phx.gbl...
>> shank wrote:
>>> 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.
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so I
>> don't check it very often. If you must reply off-line, then remove the
>> "NO SPAM"
>>
>
>

OldPedant
Thu Jun 26 01:22:03 CDT 2008

> 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
Thu Jun 26 05:57:03 CDT 2008

Old Pedant wrote:
>> 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???

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.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"