RecordCount

Discussion in 'ASP General' started by shank, Jun 20, 2008.

  1. shank

    shank Guest

    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
    %>
     
    shank, Jun 20, 2008
    #1
    1. Advertising

  2. 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 Barrows [MVP], Jun 20, 2008
    #2
    1. Advertising

  3. shank

    shank Guest

    >>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]" <> wrote in message
    news:%23WSr$...
    > 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, Jun 20, 2008
    #3
  4. 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]" <> wrote in message
    > news:%23WSr$...
    >> 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"
     
    Bob Barrows [MVP], Jun 20, 2008
    #4
  5. 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" <> wrote in message
    news:...
    >>>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]" <> wrote in message
    > news:%23WSr$...
    >> 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"
    >>

    >
    >
     
    Mike Brind [MVP], Jun 20, 2008
    #5
  6. shank

    Old Pedant Guest

    > 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?
     
    Old Pedant, Jun 26, 2008
    #6
  7. 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"
     
    Bob Barrows [MVP], Jun 26, 2008
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Caliendo

    RecordCount of a datareader

    Steve Caliendo, Jun 2, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,413
    John Oakes
    Jun 2, 2004
  2. Daan
    Replies:
    4
    Views:
    15,599
    chris22smith
    Mar 18, 2009
  3. Replies:
    2
    Views:
    2,859
    Peter Rilling
    Oct 13, 2005
  4. =?Utf-8?B?UGF1bA==?=

    DataReader and RecordCount

    =?Utf-8?B?UGF1bA==?=, Oct 21, 2005, in forum: ASP .Net
    Replies:
    9
    Views:
    37,929
    Patrick.O.Ige
    Oct 22, 2005
  5. =?Utf-8?B?YmVybmFkb3U=?=

    datasource.recordcount? How to obtain this?

    =?Utf-8?B?YmVybmFkb3U=?=, Feb 9, 2006, in forum: ASP .Net
    Replies:
    7
    Views:
    3,508
    =?Utf-8?B?UGhpbGxpcCBXaWxsaWFtcw==?=
    Feb 9, 2006
Loading...

Share This Page