I have been getting error that there function contactDeleteById requires param @contactID but i dont think there is any problem with my code below

Below is my sql code:-

ALTER PROC [dbo].[ContactDeleteByID]
@ContactID int
AS
 BEGIN
         DELETE FROM contact
         WHERE ContactID = @ContactID
 END

Below is my C# code:-(which i have written correctly for sure)

protected void btndelete_Click(object sender, EventArgs e)
{
    if (sqlCon.State == ConnectionState.Closed)
    sqlCon.Open();
    SqlCommand sqlCmd = new SqlCommand("ContactDeleteByID",sqlCon);
    sqlCmd.Parameters.AddWithValue("@ContactID", Convert.ToInt32(hfcontactID.Value));
    sqlCmd.ExecuteNonQuery();
    sqlCon.Close();
    Clear();
    FillGridView();
    lblsuccessmessage.Text = "Deleted Successfully";
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You are not telling your command that it is executing a stored procedure. All you are realling doing is executing the following TSQL:

ContactDeleteByID

Which is executing the procedure, but as you are not passing the @ContractID parameter, which is required it throws an error.

You have two options, firstly explicitly state that “ContactDeleteByID” is a stored procedure, e.g.

SqlCommand sqlCmd = new SqlCommand("ContactDeleteByID",sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;

Alternatively, you could change your SQL to include the parameter, e.g.

SqlCommand sqlCmd = new SqlCommand("EXECUTE ContactDeleteByID @ContractID",sqlCon);

This would actually work without EXECUTE but I would implore you not to take this shortcut, as this makes the intent much clearer.

As an aside, I’d advise against using AddWithValue, instead supply your data type explicitly, e.g.

sqlCmd.Parameters.Add("@ContactID", SqlDbType.Int.Value).Value = Convert.ToInt32(hfcontactID.Value);

Method 2

You are getting this because the @ContactID is not nullable. You need to initialize it to null.

Try this:

 @ContactID int=null

So your SP should be like this:

SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROC [dbo].[ContactDeleteByID]
    @ContactID int = null
    AS
        
     BEGIN
             DELETE FROM contact
             WHERE ContactID = @ContactID
             END

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0