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

[Beau]

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

 

[Bob Barrows [MVP]]

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.........

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