Taking same column but different values from database in C#

Question

I am trying to get top 3 safety data row according to equipment and plant selection from database. Now I could get top 3 safety value from database with equipment and plant selection and insert to textbox.

When I wrote "' or Safety '" + textbox.text + it is getting other plant and equipment selections

sqlcon1.Open();

SqlDataAdapter Data = new SqlDataAdapter (@"select * from ****** Where " 
    + "[Equipment Type]='" + equipmenttype_combobox.Text.Trim() 
    + "' and Plant='"   + plant_combobox.Text.Trim() 
    + "' and Safety= '" + firstsafety_textbox.Text.Trim() 
    + "' or Safety='"   + secondsafety_textbox.Text.Trim() 
    + "' or Safety='"   + thirdsafety_textbox.Text.Trim() + "'", sqlcon);

DataTable dt1 = new DataTable();
SqlDataAdapter db1 = new SqlDataAdapter();
Data.Fill(dt1);

datagridview1.DataSource = dt1;

sqlcon1.Close();

put query on string variable and then debug it and check what values going to concate — Muhammad Usama Alam, Jan 16 '20 at 08:38
Side Note: you should always use parameterized queries to avoid SQL Injection. Example https://stackoverflow.com/a/40050820/2946329 — Salah Akbari, Jan 16 '20 at 08:39

Dmitry Bychenko · Accepted Answer · 2020-01-16T09:28:04.657

Keep your sql been readable with a help of verbatim strings and string interpolation and many an error will be evident. Here you should either wrap Safety = ... or Safety = ... in parenthesis (Safety = ... or Safety = ... ) or use in construction Safety in (...).

Quick but dirty amendment is

...
string sql = $@"select * 
                  from Makerinfo 
                 where [Equipment Type] = '{equipmenttype_combobox.Text.Trim()}' 
                   and [Plant]          = '{plant_combobox.Text.Trim()}'
                   and [Safety]       in ('{firstsafety_textbox.Text.Trim()}', 
                                          '{secondsafety_textbox.Text.Trim()}', 
                                          '{thirdsafety_textbox.Text.Trim()}')";

SqlDataAdapter Data = new SqlDataAdapter(sql, sqlcon1);

...

However, this implementation has at least 3 flaws:

It's prone to SQL Injection
It will crash on equipmenttype_combobox.Text = "Browns' equipment" (note apostroph)
For different plants, you have different queries which should be parsed, optimized etc.

Much better aproach is parametrized query:

...

string sql = $@"select * 
                  from Makerinfo 
                 where [Equipment Type] = @prm_Equipment 
                   and [Plant]          = @prm_Plant
                   and [Safety]       in (@prm_Safety_1, @prm_Safety_2, @prm_Safety_3)";

using (SqlCommand q = new SqlCommand(sql, sqlcon1)) {
  // I don't know the underlying RDMBS types, that's why I've put AddWithValue
  //TODO: change AddWithValue to Add and provide the right rdbms type 
  // Something (and most probably) like
  //  q.Parameters.Add("@prm_Equipment", SqlDbType.VarChar).Value = 
  //    plant_combobox.Text.Trim();
  q.Parameters.AddWithValue("@prm_Equipment", equipmenttype_combobox.Text.Trim());
  q.Parameters.AddWithValue("@prm_Plant",     plant_combobox.Text.Trim());
  q.Parameters.AddWithValue("@prm_Safety_1",  firstsafety_textbox.Text.Trim());
  q.Parameters.AddWithValue("@prm_Safety_2",  secondsafety_textbox.Text.Trim());
  q.Parameters.AddWithValue("@prm_Safety_3",  thirdsafety_textbox.Text.Trim());  

  using (var reader = q.ExecuteReader()) {
    DataTable dt1 = new DataTable();
    dt1.Load(reader);
    datagridview1.DataSource = dt1;
  }
}

...

Taking same column but different values from database in C#

1 Answers1