Can't get a SQL command to recognise the params added

Question

Hi,

I've not used basic SQL commands for a while and I'm trying to pass a param to a sproc and the run it. However when I run the code I get a "Not Supplied" error.

Code:

SqlConnection conn1 = new SqlConnection(DAL.getConnectionStr());
SqlCommand cmd1 = new SqlCommand("SProc_Item_GetByID", conn1);
cmd1.Parameters.Add(new SqlParameter("@ID", itemId));
conn1.Open();
cmd1.ExecuteNonQuery();

I'm not really sure why this would fail. Apologies for the basic question, but I'm lost!

Thanks in advance.

Answer 1

You should set the CommandType to StoredProcedure, set the connection and use Parameters.AddWithValue("@ID", itemID)

cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Connection = conn1;
cmd1.Parameters.AddWithValue("@ID",itemID);
conn1.Open();
cmd1.ExecuteNonQuery();

If you want to use Parameters.Add() (which is obsolete), here is how you do it (you need to pass the type too)

cmd1.Parameters.Add("@ID", SqlDbType.Int); //string maybe, I don't know
cmd1.Parameters["@ID"].Value = itemID;

This should work:

SqlConnection conn1 = new SqlConnection(DAL.getConnectionStr());
SqlCommand cmd1 = new SqlCommand("SProc_Item_GetByID", conn1);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@ID", itemId);
conn1.Open();
cmd1.ExecuteNonQuery();

Answer 2

And to make your code even better, put your SqlConnection and SqlCommand into using statements, so that they'll be freed automatically at the end of the using block:

using(SqlConnection conn1 = new SqlConnection(DAL.getConnectionStr()))
{
   using(SqlCommand cmd1 = new SqlCommand("SProc_Item_GetByID", conn1))
   {
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.Parameters.AddWithValue("@ID", itemId);

        conn1.Open();

        cmd1.ExecuteNonQuery();

        conn.Close();
   }
}