0

I was creating an Appointment Table and i want to check if the row contains same Date,Slot,HR exists before another user enter.

The Connection is Opened Before this shown code.

SqlCommand slot_check = new SqlCommand("select * from Appointment where AppoinmentDate='"+textBox1.Text+"' and Slot='"+comboBox3.Text+ "'and HRName='" +comboBox2.Text+"'");

SqlDataReader Exist = slot_check.ExecuteReader(); 

if (Exist.HasRows)
                {
                    string message = "Appointment Already Exists!!!!!";
                    MessageBox.Show(message);
                }
                else
                {
                    string message = "Update";
                    MessageBox.Show(message);
                }

System.InvalidOperationException: 'ExecuteReader: Connection property has not been initialized.'

3
  • 3
    Where do you set the connection in the SqlCommand? Without a connection your command cannot reach the database and execute. Side note: Learn ASAP how to use a parameterized query instead of concatenating strings Commented Aug 13, 2019 at 10:04
  • 1
    Little Bobby Tables sez: Do use paramters in your queries!! Commented Aug 13, 2019 at 10:09
  • If you are later going to get the Appointment data, you could change if (Exist.HasRows) into a if (Exist.Read()). The .Read() function returns true if it did read an false if it didn't (if no data existed or if all data was already read) Commented Aug 13, 2019 at 10:31

1 Answer 1

2

To execute a command two informations are essential:
The sql string to execute and the connection to reach the database.

Without the connection your command cannot be executed because the framework doesn't know how to read or write the database.

There is an overload for the SqlCommand constructor that takes the two required parameters:

SqlCommand cmd = new SqlCommand(sqlText, connectionInstance);

So your code should be something like this

// The command text to run, without string concatenations and with parameters placeholders
string sqlText = @"select * from Appointment 
                   where AppoinmentDate=@aptDate 
                     and Slot=@slot 
                     and HRName=@name";

// Using statement to correctly close and dispose the disposable objects
using(SqlConnection cnn = new SqlConnection(connectionString))
using(SqlCommand slot_check = new SqlCommand(sqlText, cnn))
{
     // A parameter for each placeholder with the proper datatype
     cmd.Parameters.Add("@aptDate", SqlDbType.Date).Value = Convert.ToDateTime(textBox1.Text);
     cmd.Parameters.Add("@slot", SqlDbType.NVarChar).Value = comboBox3.Text;
     cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = comboBox2.Text;
     cnn.Open();         
     // Even the SqlDataReader is a disposable object
     using(SqlDataReader Exist = slot_check.ExecuteReader())
     {
         if (Exist.HasRows)
         {
              string message = "Appointment Already Exists!!!!!";
              MessageBox.Show(message + " " + Exist + comboBox2.Text);
         }
         else
         {
              string message = "Update";
              MessageBox.Show(message);
         }
    }
}

As you can see the code now has a connection passed to the command constructor and a command text built without concatenating strings but using parameters.
Using parameters is a mandatory approach for any kind of database related operation. Without parameters your code could be exploited with the well known Sql Injection hack, but also, the simple presence of a single quote in your values, could break the sql syntax resulting in a Syntax Error Exception

Note that this code could still be wrong because I don't know what kind of data is stored in your table in the columns used in the WHERE statement. I assumed some kind of type but you should check against your table and verify if they are correct.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.