An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll?

Question

I'm getting an error and I don't know why!

using(SqlConnection sqlcon = new SqlConnection(con)) 
{
    sqlcon.Open();

    SqlCommand cmd = new SqlCommand("dbo.workScheduleDataGrid", sqlcon);
    cmd = sqlcon.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@worker_id", ids[i].worker_id);
    cmd.Parameters.AddWithValue("@day", days[j]);

    SqlDataReader reader = cmd.ExecuteReader();

    if (reader.HasRows) 
    {
        while (reader.Read()) 
        {
            MessageBox.Show("full name is: " + reader.GetInt32(1) + " and field of work is: " + reader.GetString(2) + " in day " + days[j]);
        }
    }
    else 
    {
        MessageBox.Show("No data");
    }

    reader.Close();
}

What is the error here knowing that I've used stored procedure the same way with no errors!

Answer 1

You should either use this method:

SqlCommand cmd = new SqlCommand("dbo.workScheduleDataGrid", sqlcon);

or this method

SqlCommand cmd = sqlcon.CreateCommand();

to create the command, but not both (the second assignment in your code overwrites the first one).

With the second options, you need to specify the command to execute separarely:

cmd.CommandText = "dbo.workScheduleDataGrid";

Also, do not forget to dispose the cmd object, best with another using statement.