Stored Procedure with Parameters

[nphani]

I have a stored procedure which accepts a parameter and returns a Recordset. It is:

CREATE PROCEDURE procstate
@ctry varchar(50)
AS
Select state from state WHERE country= @ctry

I have checked this using Query Analyzer and it worked fine. Now i am using the Command Object parameter to run it as follows:

cmd.CommandType = 4
cmd.CommandText = "procstate"
Set paramId = cmd.CreateParameter("ctry", adVarChar,adParamInput,50,Request.Form("ddlCountry&quot)
cmd.Parameters.Append paramId
Set rs = cmd.Execute

But the Stored Procedure doesnot work. Any suggestions?

 

[meckeard]

Hi,

I would start by checking the value being used with the parameter. Do a response.write before you execute to see that value.

And are there any errors? What is the rest of the code after you execute?

Mark

 

[JJ26]

i have a simple way to execute your Stored-Procedure.

1. use ADODB.recordset
2. in the openstring of ur recordset just type the exact script like u want to run your Stored-Procedure via the Query Analyser. Like

Rs.open "procstate '" & Request.Form("ddlCountry") & "' ",Conn
If Not Rs.EOF then
   response.write Rs("state")
end if
Rs.Close

Hope it helps

*JJ*

 

[Shaggs]

sorry to add a totally obvious suggestion but your using the adVarChar and AdParamInput ADO constants
have you referenced the typeLibrary.
Try using thier respective literal integer values.

If thats totally left outfield.
then i suggest write the parameter to the screen prior to execution.

Sorry if wasting ya time

 

[nphani]

Thank you all for your replies. But I have tried response.write and it works fine. And i have tried using the numbers instead of adVarChar and adParamInput. But there seems to be some problem. I use this Recordset for populating DropDown box values.
I have Submit and Reset buttons after this dropdown box. Because of some error in this parameter creation, the buttons are not being displayed. I am not able to figure out the error. Any help please?