CODEWITHSUNDEEP
c#sqlasp.netado.net
Ron
Ron

Reputation: 1931

Stored procedure expects parameter though parameter is provided

I am using VS 2015 and SQL Server 2016.

In my C# code, I am assigning the parameters which my stored procedure expects, but am getting an error as my stored procedure is expecting a parameter.

My stored procedure:

CREATE PROCEDURE [dbo].[SDDeleteTAR]
    @TARKey int
AS
BEGIN
    DELETE technical_assistance_requests  
    WHERE request_id = @TARKey
END

My C# code:

Label id = (Label)e.Item.FindControl("REQUEST_ID");
var x = id.Text;
int rid = Convert.ToInt32(x);

string cs = ConfigurationManager.AppSettings.Get("connectionString").ToString();

using (SqlConnection con = new SqlConnection(cs))
{
    SqlCommand cmd = new SqlCommand("SDDeleteTAR", con);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.AddWithValue("@RequestID", rid); 

    con.Open();
    cmd.ExecuteNonQuery(); 
}

When I run this, I get this error:

Procedure or function 'SDDeleteTAR' expects parameter '@TARKey', which was not supplied.

I am passing the required parameters and executing my command type as CommandType.StoredProcedure, and am not able to resolve this.

I also checked the same issue in StackOverflow here but the submitter is missing the command type declaration.

Any help is appreciated, in pointing out where I am going wrong.

Upvotes: 0

Views: 953

Answers (2)

samithagun
samithagun

Reputation: 693

You can safely execute a stored procedure without directly specifying the names, by using SqlCommandBuilder.DeriveParameters. This will retrieve the parameters from the server and create the SQLParameter collection for you.

SqlCommand cmd = new SqlCommand("SDDeleteTAR", con);
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters[0].Value = rid;

Upvotes: 1

Burak Altin
Burak Altin

Reputation: 468

You should change @RequestID to @TARKey. Because your stored procedure expects a parameter with that name.

Upvotes: 4

Related Questions