Passing INT to the stored procedure fails

Question

I am trying to pass CommandArgument as an int to my stored procedure, but I get:

Failed to convert parameter value from string to Int32

Even when he is converted, before passing 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 have the correct 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 could not explain what was happening. Somehow, you must explicitly declare a parameter, set it, and then add it to the Parameters property. This only happened on SQL Server, not Access, for example. I know this is basically the same code, but it worked for me, don't ask me why:

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

Answer 2

Try using the SqlParameterCollection.AddWithValue method. This will greatly simplify the situation.

 cmd.CommandText = "deleteProd"; int delprodID = Convert.ToInt32(e.CommandArgument.ToString()); Response.Write(delprodID); cmd.Parameters.AddWithValue("@prodID", delprodID); cmd.CommandType = CommandType.StoredProcedure; sqlTools.cmdToNonQuery(cmd)