Problem calling stored porecure with params

[sergeiY]

Hi

When I run my stored proc it doesn't propduse any errors but also dusn;t seems to execute... I am new to ADO can you please have a look maybe I am doing something wrong with my ASP?

Set Cmd = Server.CreateObject("ADODB.Command")
	With Cmd
	   .ActiveConnection = strConn
	   .CommandText = "sp_Synchronise" ' name of stored procedure
	   .CommandType = adCmdStoredProc
	   .Parameters.Append .CreateParameter ("@startDate", adDBTimeStamp, adParamInput, CDate("2003-01-01 00:00:00"))
	   .Parameters.Append .CreateParameter ("@endDate", adDBTimeStamp, adParamInput, CDate("2003-04-17 23:59:59"))
   		Set Rs_Customer = Server.CreateObject("ADODB.Recordset")
		
   'On Error Resume Next
   Set Rs_Customer = .Execute
   End With

and this is a signature of my stored proc:

CREATE PROCEDURE dbo.sp_Synchronise
	@startDate datetime = NULL,
	@endDate datetime = NULL
AS
....

I can run other stored procs with this code and they work fine ! (others don't take any params)

Any help is greatly appreciated

Thank you

 

[Ian2]

Hi Sergei

I'm also still learning ADO. We use the following simple call to stored procedures which works well:

set cnn = createObject("ADODB.Connection&quot
set rs = CreateObject("ADODB.Recordset&quot
strConn = "Driver={SQL SERVER};Server=...;Database=... etc."
cnn.provider = "SQLOLEDB"
cnn.Open strconn

set rs = cnn.execute("StoredProcedure " & ID)


The stored procedure would then be.....

CREATE PROCEDURE StoredProcedure
@ID int
AS
SELECT *
FROM Tablename
WHERE ID = @ID
GO

 

[mit99mh]

i might be incorrect but you are using the timestamp numtype from ADOVBS.INC when you define the parameter for the SP - you then use a datetime data type - the two datatypes are different and the datatype in the SP should match the datatype in the ASP.

Hope this helps

 

[longmatch]

Dear Ian2:
I met the same problem as SergeiY - can not make use parameters.append method in my ASP code to add the parameter. I tried your suggestion, it works. I did find many examples using parameters.append. Not sure why my code does not work.

My code is following:
dim objConn
dim strconn
strconn ="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=webuser;Password=webuser;Initial Catalog=residency2;Data Source=128.249.29.207"

set objConn = server.createobject("adodb.connection&quot
objConn.open strconn
set cmd=server.CreateObject("adodb.command&quot

cmd.commandtext = ("sp_procedure " & 130158)
'cmd.CommandType = adCmdStoredProc
cmd.activeconnection = objConn
'cmd.Parameters.append cmd.CreateParameter("BaylorID",adInteger,adParamInput)
'cmd("BaylorID&quot = 130158
set rsCmd = server.CreateObject("adodb.recordset&quot
set rsCmd=cmd.execute
do while not rsCmd.EOF
response.write(rsCmd(&quot;Procedure&quot) & &quot;<br>&quot;
rsCmd.movenext
LOOP

This is the working code. But when I use

'cmd.Parameters.append cmd.CreateParameter(&quot;BaylorID&quot;,adInteger,adParamInput)
'cmd(&quot;BaylorID&quot = 130158
the code stop working.

Any suggestions

Longmatch

 

[longmatch]

I tried with following code. Still does not work. I forgot to tell you more inforamtion in my previous post. The BaylorID is an integer field, length 4, a six digits number, for example, 130158. The error message for the following code is:

ADODB.Command error '800a0bb9'

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

/residency/procedure/TMP4ekduie4lb.ASP, line 16


not sure why it happens


dim objConn
dim strconn
strconn =&quot;Provider=SQLOLEDB.1;Persist Security Info=False;User ID=webuser;Password=webuser;Initial Catalog=residency2;Data Source=128.249.29.207&quot;

set objConn = server.createobject(&quot;adodb.connection&quot
objConn.open strconn
set cmd=server.CreateObject(&quot;adodb.command&quot

cmd.commandtext = (&quot;sp_procedure &quot
'cmd.CommandType = adCmdStoredProc
cmd.activeconnection = objConn

Set prmProcedure = cmd.CreateParameter(&quot;BaylorID&quot;, adInteger, adParamInput)
cmd.Parameters.Append prmProcedure
prmProcedure.Value = 130158

'cmd.Parameters.Append cmd.CreateParameter(&quot;BaylorID&quot;,adInteger,adParamInput)
set rsCmd = server.CreateObject(&quot;adodb.recordset&quot
set rsCmd=cmd.execute
do while not rsCmd.EOF
response.write(rsCmd(&quot;Procedure&quot) & &quot;<br>&quot;
rsCmd.movenext
LOOP

thanks

longmatch

 

[mit99mh]

try setting parameters like this:

objCM.ActiveConnection = objConn

objCM.CommandText = &quot;Do_Something&quot;

objCM.CommandType = adCmdStoredProc

objCM.Parameters.Append objCM.CreateParameter(&quot;@BaylorID&quot;, adInteger, adParamInput, 4, 130158)

declare your SP

CREATE PROCEDURE Do_Something

@BaylorID int

AS
/*
SQL HERE
*/

Hope this helps

 

[sergeiY]

I didn't publish what worked for me as I got side tracked with other issues

Here is a code that works for me now:

Set Cmd = Server.CreateObject("ADODB.Command")
	With Cmd
	.ActiveConnection = strConn
	.CommandText = "sp_Synchronise" ' name of stored procedure
	.CommandType = adCmdStoredProc
	.parameters.refresh
	.parameters.item("@startDate") = startDate
	.parameters.item("@endDate") = endDate
	'.Parameters.Append .CreateParameter ("@startDate", adDBTimeStamp, adParamInput, CDate("2003-04-17 23:59:59"))
	Set rs = Server.CreateObject("ADODB.Recordset")
	Set rs = .Execute
	
	'clean up
	Set rs = Nothing
	Set Cmd = Nothing	
End With

It's not most efficient code as it calls parameters.refresh and what it does it makes an extra call to DB to refresh stored proc params and to set up all data types. I had to do it because I had trouble with passing datetime param (I still don't know why it doesn't work for me see the commented out line) But anyway this solution works fine for now.

Sergei

 

[ndl]

hello sergeiY,


This may help u

<%set cmd=server.CreateObject(&quot;ADODB.Command&quot
cmd.ActiveConnection =ObjConn%
set rs=server.CreateObject (&quot;ADODB.Recordset&quot>

<%mnth=month(now())
if mnth>=6 then
fr=year(now())
tr=year(now())+1
else
fr=year(now())-1
tr=year(now())
end if
cmd.Parameters.Refresh
cmd.CommandText =&quot;stn_stud_id&quot;
cmd.CommandType =adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter(&quot;@fr_yr&quot;, adVarChar, adParamInput, 6, fr)
cmd.Parameters.Append cmd.CreateParameter(&quot;@to_yr&quot;, adVarChar, adParamInput, 6, tr)
cmd.Parameters.Append cmd.CreateParameter(&quot;@sl_no&quot;, adInteger,adParamOutput)
cmd.Execute
m=cmd.Parameters(&quot;@sl_no&quot.Value%>


have nice day
rani