ASP, looping, and stored procedures.... error '800a0bb9' ...

Discussion in 'ASP General' started by Beau, Nov 29, 2004.

  1. Beau

    Beau Guest

    Hi all, thanks in advance.

    Ok, heres the story.
    What is happening......
    --------------------------------

    I've got an ASP page that loops.
    It loops in order to get data in different, sequential date ranges. I.E.
    from 9/1/2000 - 10/1/2000 then 10/1/2000 - 11/1/2000 etc etc etc.
    It calls SPs using the 2 dates and an integer used for companyid reference.

    Let's just do this for 2 SP's (there are like 6 on the page.)
    One SP has 3 params, one has only 2.

    Now, the first iteration of the loop, it works. (because I'm
    response.writiting out the dates it's using to verify they are ok.
    The second time through I get the following error when I try to execute the
    following ASP:

    Set rstStoredProc = cmdStoredProc.Execute(, Array(GroupChoice, fromdate,
    todate))
    ______________________________________________
    ADODB.Command error '800a0bb9'

    Arguments are of the wrong type, are out of acceptable range, or are in
    conflict with one another.

    _______________________________________________



    What I need to do.........

    --------------------

    In the loop, I am trying to reuse my command/connection objects instead of
    reinstantiating them for each iteration of the loop.
    ***Currently, it must use ODBC not OLEDB so keep that in mind.***



    Here's the code for the SP's
    --------------------

    CREATE Procedure proc_getPageHits
    (
    @GroupID int,
    @FromDate datetime,
    @ToDate datetime
    )
    As
    SELECT sum(counter) as hitcount
    FROM tblTracking
    WHERE CreateUserID in (select UserID from tblUser where GroupID=
    @GroupID)
    and (CreateDate between @FromDate and @ToDate)
    GO



    CREATE Procedure proc_getUserCount
    (
    @GroupID int,
    @ToDate datetime
    )
    As
    SELECT count(UserID) as usercount
    FROM tblUser
    WHERE (GroupID = @GroupID) and (CreateDate < @ToDate)
    GO


    Here's the ASP
    ----------------------------------------------
    Set cmdStoredProc = Server.CreateObject("ADODB.Command")
    cmdStoredProc.ActiveConnection = dbConn

    do while DateCompare(currentsearchdate,todaysquarterend) = "smaller"

    cmdStoredProc.CommandText = "Proc_getUserCount"
    cmdStoredProc.CommandType = adCmdStoredProc
    Set rstStoredProc = cmdStoredProc.Execute(, Array(GroupChoice, todate))

    cmdStoredProc.CommandText = "Proc_getPageHits"
    cmdStoredProc.CommandType = adCmdStoredProc
    Set rstStoredProc2 = cmdStoredProc.Execute(, Array(GroupChoice,
    fromdate, todate)) '(**THIS IS WHERE IT ERRORS**)

    loop
    ----------------------------------------------


    Why does it error on the SECOND procedure of the SECOND iteration of the
    loop?
    It makes it past the first loop ok.
    Then it makes it past the first SP of the second loop but errors in the
    second SP....??...

    The dates are verified correct and in format.... so what the heck is the
    prob?

    If I re-create the command object each iteration (i.e. put the Set
    cmdStoredProc = Server.CreateObject("ADODB.Command") line inside the loop)
    it works fine. but it's so much slower. The results of the page are coming
    back in 13seconds. (there's alot more calls and alot more stuff going on,
    but these procedures should pick it up a bit.

    Also, if you have any suggestions on the SQL select statements, feel free to
    enlighten me. I am using the 'IN' method whereas some people have told me of
    the 'where exists' SQL method. I have not received any examples though.

    Thanks again in advance.
    -Beau
    www.worlddoc.com
     
    Beau, Nov 29, 2004
    #1
    1. Advertising

  2. Beau wrote:
    > Hi all, thanks in advance.
    >
    > Ok, heres the story.
    > What is happening......
    > --------------------------------
    >
    > I've got an ASP page that loops.
    > It loops in order to get data in different, sequential date ranges.
    > I.E. from 9/1/2000 - 10/1/2000 then 10/1/2000 - 11/1/2000 etc etc etc.
    > It calls SPs using the 2 dates and an integer used for companyid
    > reference.
    >
    > Let's just do this for 2 SP's (there are like 6 on the page.)
    > One SP has 3 params, one has only 2.
    >
    > Now, the first iteration of the loop, it works. (because I'm
    > response.writiting out the dates it's using to verify they are ok.
    > The second time through I get the following error when I try to
    > execute the following ASP:
    >
    > Set rstStoredProc = cmdStoredProc.Execute(, Array(GroupChoice,
    > fromdate, todate))
    > ______________________________________________
    > ADODB.Command error '800a0bb9'
    >
    > Arguments are of the wrong type, are out of acceptable range, or are
    > in conflict with one another.
    >
    > _______________________________________________
    >
    >
    >
    > What I need to do.........
    >
    > --------------------
    >
    > In the loop, I am trying to reuse my command/connection objects
    > instead of reinstantiating them for each iteration of the loop.


    You don't need to even use a Command object. You can execute the below
    procedures simply by doing this (assuming you've got a connection object
    with the sensible name of cn):

    set rstStoredProc2 =createobject("adodb.recordset")
    cn.proc_getPageHits GroupChoice,fromdate,todate,rstStoredProc2
    set rstStoredProc =createobject("adodb.recordset")
    cn.proc_getUserCount GroupChoice,todate,rstStoredProc

    > ***Currently, it must use ODBC not OLEDB so keep that in mind.***


    This is irrelevant, but ... Why is that? I've never seen a good reason for
    using the obsolete ODBC driver.

    >
    >
    >
    > Here's the code for the SP's
    > --------------------
    >
    > CREATE Procedure proc_getPageHits
    > (
    > @GroupID int,
    > @FromDate datetime,
    > @ToDate datetime
    > )
    > As

    --you forgot to include this:
    SET NOCOUNT ON

    > SELECT sum(counter) as hitcount
    > FROM tblTracking
    > WHERE CreateUserID in (select UserID from tblUser where GroupID=
    > @GroupID)
    > and (CreateDate between @FromDate and @ToDate)


    SELECT sum(counter) as hitcount
    FROM tblTracking t INNER JOIN tblUser u
    ON t.CreateUserID = u.UserID AND u.GroupID = @GroupID
    WHERE t.CreateDate between @FromDate and @ToDate

    > GO
    >
    >
    >
    > CREATE Procedure proc_getUserCount
    > (
    > @GroupID int,
    > @ToDate datetime
    > )
    > As


    --Again:
    SET NOCOUNT ON
    > SELECT count(UserID) as usercount
    > FROM tblUser
    > WHERE (GroupID = @GroupID) and (CreateDate < @ToDate)
    > GO
    >



    Actually, this can be done with a single stored procedure:

    CREATE Procedure proc_getUserCountAndPageHits (
    @GroupID int,
    @FromDate datetime,
    @ToDate datetime,
    @Users int output
    )
    As
    SET NOCOUNT ON
    SELECT @users=count(UserID) as usercount
    FROM tblUser
    WHERE (GroupID = @GroupID) and (CreateDate < @ToDate)

    SELECT sum(counter) as hitcount
    FROM tblTracking t INNER JOIN tblUser u
    ON t.CreateUserID = u.UserID AND u.GroupID = @GroupID
    WHERE t.CreateDate between @FromDate and @ToDate
    go

    Of course, you are back to needing to use a Command object in order to
    retrieve the output parameter value. Also, you will no longer be able to use
    the Array method to execute the procedure. You will need to use
    CreateParameter statements to create the Parameters collection. I've posted
    code for doing this before.

    Slightly less efficiently, you can do this to avoid writing the
    CreateParameter statements:

    CREATE Procedure proc_getUserCountAndPageHits (
    @GroupID int,
    @FromDate datetime,
    @ToDate datetime
    )
    As
    SET NOCOUNT ON
    EXEC proc_getUserCount @GroupID, @ToDate
    SELECT sum(counter) as hitcount
    FROM tblTracking t INNER JOIN tblUser u
    ON t.CreateUserID = u.UserID AND u.GroupID = @GroupID
    WHERE t.CreateDate between @FromDate and @ToDate
    go

    And in ASP:
    set rstStoredProc =createobject("adodb.recordset")
    cn.proc_getUserCountAndPageHits GroupChoice,fromdate, _
    todate,rstStoredProc2
    set rstStoredProc2 = rstStoredProc.NextRecordset

    Bob Barrows
    PS. .inetserver.asp.db was the only group for which this question was
    relevant. There was no need to crosspost to so many groups, especially the
    irrelevant ones. I've set the Followup-To to
    microsoft.public.inetserver.asp.db
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 29, 2004
    #2
    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. ab
    Replies:
    3
    Views:
    356
  2. DC
    Replies:
    1
    Views:
    332
    Tom B
    Sep 10, 2003
  3. Racer-D
    Replies:
    4
    Views:
    186
    Bob Barrows [MVP]
    Sep 3, 2004
  4. Replies:
    2
    Views:
    245
    Bullschmidt
    Oct 25, 2005
  5. news
    Replies:
    3
    Views:
    615
    Bob Barrows
    Jul 13, 2009
Loading...

Share This Page