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

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