September 12, 2008 at 12:06 pm
i have my stored procedure as follows and it is works successfully in sql server but when i use to pass parameter to my .aspx page through query string but i get an error " stored procedure error no parameters and arguments were supplied "
How can i pass parameter as a query string .
my stored procedure is as follows
CREATE PROCEDURE dbo.More_news
@newscat INT
AS
BEGIN
WITH Tanzania AS (
SELECT TOP 11 ROW_NUMBER() OVER (ORDER BY PostedDate DESC)
AS Row, NewsID, NewsHeading,NewsCategory,PostedDate
FROM NewsTable where NewsCategory=@newscat)
SELECT NewsID, NewsHeading,NewsCategory
FROM Tanzania
WHERE Row between
2 AND 15
END
and my asp page for query sting is as follows
int x = Convert.ToInt32(Request.QueryString["Cat"]);
SqlCommand comm = new SqlCommand("More_news", con);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add("newscat", SqlDbType.Int);
comm.Parameters["newscat"].Value = x;
con.Open();
rptr.DataSource = comm.ExecuteReader();
rptr.DataBind();
con.Close();
PLEASE HELP
September 12, 2008 at 12:09 pm
Either use Parameters.Refresh, or add two parameters. The first parameter is the RETURN_VALUE parameter for the procedure. Since it happens to be an integer data type, the parameter you are adding is getting associated with the error output parameter.
September 12, 2008 at 5:28 pm
how can i do it then?
September 13, 2008 at 8:22 pm
Try changing the parameter name
From
comm.Parameters.Add("newscat", SqlDbType.Int);
To
comm.Parameters.Add("@newscat", SqlDbType.Int);
To verify, if you have a development server with admin rights, you can perform a trace to examine the calls as server receives them.
October 27, 2010 at 2:06 am
Hi i encountered a problem which is similar, my query as below:
When i try to pass the date as parameter the error message saying that
[failed with the following error:
"Syntax error, permission violation, or other nonspecific error".
Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ]
WITH Clist AS (SELECT QC_tag, rim_no, customer_name, pid_source, row_number() OVER (partition BY rim_no
ORDER BY pid_source) AS nr
FROM Risk.CUSTOMER
WHERE snapshot_dt = ?)
UPDATE Clist
SET QC_tag = nr;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply