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.writit ing 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.E xecute(, Array(GroupChoi ce, 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_getPageHit s
(
@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_getUserCou nt
(
@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.CreateOb ject("ADODB.Com mand")
cmdStoredProc.A ctiveConnection = dbConn

do while DateCompare(cur rentsearchdate, todaysquarteren d) = "smaller"

cmdStoredProc.C ommandText = "Proc_getUserCo unt"
cmdStoredProc.C ommandType = adCmdStoredProc
Set rstStoredProc = cmdStoredProc.E xecute(, Array(GroupChoi ce, todate))

cmdStoredProc.C ommandText = "Proc_getPageHi ts"
cmdStoredProc.C ommandType = adCmdStoredProc
Set rstStoredProc2 = cmdStoredProc.E xecute(, Array(GroupChoi ce,
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.CreateOb ject("ADODB.Com mand") 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]]

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.writit ing 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.E xecute(, Array(GroupChoi ce,
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_getPage Hits GroupChoice,fro mdate,todate,rs tStoredProc2
set rstStoredProc =createobject(" adodb.recordset ")
cn.proc_getUser Count GroupChoice,tod ate,rstStoredPr oc
***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_getPageHit s
(
@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_getUserCou nt
(
@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_getUserCou ntAndPageHits (
@GroupID int,
@FromDate datetime,
@ToDate datetime,
@Users int output
)
As
SET NOCOUNT ON
SELECT @users=count(Us erID) 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_getUserCou ntAndPageHits (
@GroupID int,
@FromDate datetime,
@ToDate datetime
)
As
SET NOCOUNT ON
EXEC proc_getUserCou nt @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_getUser CountAndPageHit s GroupChoice,fro mdate, _
todate,rstStore dProc2
set rstStoredProc2 = rstStoredProc.N extRecordset

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.publi c.inetserver.as p.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.