CODEWITHSUNDEEP
c#sql.netwindowswinforms
user11921730
user11921730

Reputation:

Check if a row exists using windows form?

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.'

Upvotes: 0

Views: 646

Answers (1)

Steve
Steve

Reputation: 216293

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.

Upvotes: 2

Related Questions