Executing a stored procedure 1

[obuspider]

I had the following query turned into a stored procedure. The procedure is called ProcTrackingOverview. How do I execute this procedure now? I have tried, but am doing something wrong. Can I not say Execute ProcTrackingOverview?

"Select schools.site, schools.area, avg(total_enrollment), avg(d_free_apr), avg(sc_free_Meals), avg(d_Redu_apr), avg(sc_redu_meals), avg(sc_d_paid_meals), sc_meal_type, schcat, avg(sc_emp_meals), avg(d_stud_adult_meals_cash), avg(d_tot_meals), avg(d_stud_adult_Ala_meals), schoolName from (schools inner join tbl_scanned_sales on schools.site = tbl_scanned_sales.sc_site) inner join enrollment on schools.site = enrollment.cus_site where tbl_scanned_sales.area=schools.area and sc_sales_date between '" & fsale_date & "' and '" & tsale_date & "' group by schools.site, schools.area, schcat, sc_meal_type, SchoolName"

 

[mwolf00]

What parameters does the SP need? Is the SP returning anything? Where are you executing the SP from - another SP, an ASP page, somewhere else?

EXEC ProcTrackingOverview '1/2/02','1/31/02'

Why don't you post the CREATE PROC you used to create the procedure and the place where you are trying to call it -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

Unfortunately, I don't have access to the Create Procedure. I don't have direct access to the db, so someone else created the stored procedure based on the query I gave. Basically, I am selecting a bunch of stuff that I will need to display in a recordset. There are two parameters for the date fsale_date and tsale_date. It is being executed in an ASP page

 

[mwolf00]

Set objConn = Server.CreateObject( &quot;ADODB.Connection&quot; )
Set objCmd = Server.CreateObject( &quot;ADODB.Command&quot; )
Set objRS = Server.CreateObject( &quot;ADODB.Recordset&quot; )
objConn.Open yourConnectionString
Set objCmd.ActiveConnection = objConn
objCmd.CommandTimeout = 300

with objCmd
.commandText = &quot;ProcTrackingOverview &quot;
.CommandType =adCmdStoredProc
.parameters(1) = fsale_date
.parameters(2) = tsale_date
End With
set objRS = objCmd.Execute()
set objCmd = nothing

DO WHILE NOT objRS.EOF
'go thru recordset here.....
objRS.MOVENEXT
LOOP -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

Thanks,

That got me started. However, now I am getting an error

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

 

[mwolf00]

You need to find out what the stored procedure needs as parameters. I was guessing fsale_date and tsale_date. If it needs only those two, then your error may bet that the variables don't hold dates. Try this:
IF NOT isDate(fsale_date) THEN fsale_Date = '1/1/02'
IF NOT isDate(tsale_date) THEN tsale_Date = '12/31/02'
.parameters(1) = cDate(fsale_date)
.parameters(2) = cDate(tsale_date)

-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

I tried that. It's still not working. I am assuming it has to do with the parameters. The line it says it is failing on is

.CommandType =adCmdStoredProc

 

[mwolf00]

.CommandType = adCmdStoredProc must have adovbs included or change value to - &H0004 (w/ no quotes)


.commandType = &H0004 -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

Mike,

Thanks so much for your help. I am making progress slowly. Now I am getting the error &quot;item cannot be found in collection&quot; This means I am requesting a field name not in the db? Just so we are on the same page, here's what I have thus far.

fsale_date = &quot;10/7/02&quot;
tsale_date = &quot;10/8/02&quot;
selReg = &quot;E&quot;
txtarea = &quot;13W&quot;

Set oConn = Server.CreateObject( &quot;ADODB.Connection&quot; )
Set objCmd = Server.CreateObject( &quot;ADODB.Command&quot; )
Set session(&quot;oRs&quot = Server.CreateObject( &quot;ADODB.Recordset&quot; )
sConn = &quot;uid=frodo;pwd=22536s3;dsn=test&quot;
oConn.Open sConn
session(&quot;oRs&quot.ActiveConnection = oConn
objCmd.CommandTimeout = 300

with objCmd
.commandText = &quot;ProcTrackingOverview &quot;
.CommandType = &H0004
.parameters(1) = fsale_date
.parameters(2) = tsale_date

End With
session(&quot;oRs&quot = objCmd.Execute()
objCmd = nothing

if not session(&quot;oRs&quot.eof then
session.movefirst

 

[mwolf00]

Why are you using a session variable for the recordset? Yes, the error means that you are not asking for an item in the recordset by the correct name. I have never used as session variable to hold a recordset. I'm not sure if the syntax is correct (or if it even possible). I would make sure that everything works w/o the session varaible and then try changing just that....

fsale_date = &quot;10/7/02&quot;
tsale_date = &quot;10/8/02&quot;
selReg = &quot;E&quot;
txtarea = &quot;13W&quot;

Set oConn = Server.CreateObject( &quot;ADODB.Connection&quot; )
Set objCmd = Server.CreateObject( &quot;ADODB.Command&quot; )
Set oRs = Server.CreateObject( &quot;ADODB.Recordset&quot; )
sConn = &quot;uid=frodo;pwd=22536s3;dsn=test&quot;
oConn.Open sConn
objCmd.ActiveConnection = oConn <-- you had an error here
objCmd.CommandTimeout = 300

with objCmd
.commandText = &quot;ProcTrackingOverview &quot;
.CommandType = &H0004
.parameters(1) = fsale_date
.parameters(2) = tsale_date

End With
set oRS= objCmd.Execute() <-- You definitly had an error here
objCmd = nothing

if not oRS.eof then
oRS.movefirst
-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

Mike,

I am using sessions because the original application was created using sessions. My job is to add to a module. I am trying out without the sessions as recommended--Good thought by the way.

Now I am getting an error &quot;Rowset position cannot be restarted&quot;

 

[mwolf00]

Remove the line: oRS.movefirst

You shouldn't need it. Your cursor type defaults to a move forward only cursor and you really don't want to try to mess with that 'cause it is persnicketty....
You will be at BOF w/o the movefirst command anyways.... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

Mike,

I seem to have things just about working except that it is saying the procdure &quot;expects parameter '@fsale_date' which was not supplied'

Any ideas?

 

[mwolf00]

if you are supplying 2 parameters and you are getting that message, then I would suggest the the stored procedure requires 3 parameters. Do you have access to the stored proc (via Enterprise Manager)? You need to see the parameters it expects and the order it expects them in...

You could try:
with objCmd
.commandText = &quot;ProcTrackingOverview &quot;
.CommandType = &H0004
.parameters(1) = null
.parameters(2) = fsale_date
.parameters(2) = tsale_date

End With

But these are just shots in the dark...
-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[obuspider]

Mike,

Thanks for all your help. I went back and added the Cdate in front of the parameters. It's not giving me errors now. It just is taking so long that it seems to hang.

 

[mwolf00]

IS the query taking a long time to execute, or is the asp page taking a long time to parse the results? Performance can be improved on both....

One not-so-elegant way to determine - since you don't have query analyzer.....

timeStr = cStr(now) & &quot;<br>&quot;

...execute your sp

timeStr = timeStr & cStr(now) & &quot;<br>&quot;

.....loop through the recordset

timeStr = timeStr & cStr(now) & &quot;<br>&quot;

response.write timeStr

....evaluate your hang-up.... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com