Passing INT to stored procedure fails

Question

I'm trying to pass a CommandArgument as int to my stored procedure, but I get:

Failed to convert parameter value from a String to a Int32

Even when it is converted before I pass it...

C# Code:

cmd.CommandText = "deleteProd";
int delprodID = Convert.ToInt32(e.CommandArgument.ToString());

Response.Write(delprodID);

cmd.Parameters.Add(new SqlParameter("@prodID", SqlDbType.Int)).Value = delprodID;
cmd.CommandType = CommandType.StoredProcedure;

sqlTools.cmdToNonQuery(cmd)

My Response.Write shows that I've got the right ID in delprodID.

Stored procedure:

ALTER PROCEDURE dbo.deleteProd @prodID int
AS
IF @prodID > 0
BEGIN
    DELETE FROM products WHERE prodID = @prodID
END

Answer 1

I had the same problem 3 months ago and couldn't explain what was going on. Somehow, you must explicitly declare parameter, set it and then add it to the Parameters property. It was only happening on SQL Server, not on Access for instance. I know that this is basically the same code but this worked for me, don't ask me why:

SqlParameter param = new SqlParameter("@prodID", SqlDbType.Int);
param.Value = delprodID;
cmd.Parameters.Add(param);