Database values in textbox if select Combobox

Question

I am trying to write code for a form that writes to a sql table. I use data form other tables to complete the form (along with user input). I have a checkbox that options the source of a combobox (ddDefect) - it is based on one of two sql LIKE queries - so, the combox will display the results of one LIKE query if the checkbox = true and the other LIKE query if it = false. This part works great. Problem is; I cannot seem to figure out how to take the selected item in the combobox and display text form another column in my textbox (txtNcm)

I have tried various ways and this seems to make the most sense to me (though I am only a beginner and clueless) but I get nothing in my text box.

here is the code that I have been trying:

private void ddDefect_SelectedIndexChanged(object sender, EventArgs e)
{
    string constring = "Data Source=TS-ERP01;Initial Catalog=Touchstn02;Integrated Security=True";
    string Query = "select * from  Defect_Codes Where DESCP_91= ' " + ddDefect.Text + " ';";
    SqlConnection conDataBase = new SqlConnection(constring);
    SqlCommand cmdDataBase = new SqlCommand(Query, conDataBase);
    SqlDataReader myReader;
    try
    {
        conDataBase.Open();
        myReader = cmdDataBase.ExecuteReader();

        while (myReader.Read())
        {
            string sDEF = myReader["DEFECT_91"] as String;
            txtNcm.Text = sDEF;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

If you debug your code, what is contained in "sDEF"? The right value? — Sean Stayns, May 02 '17 at 19:21
*"I cannot seem to figure out how to take the selected item in the combobox"* - Use: `(sender as ComboBox).SelectedItem`. — Spencer Wieczorek, May 02 '17 at 19:22

score 1 · Accepted Answer · edited May 23 '17 at 11:54

Your sql command text contains spaces before and after the value of ddDefect.

 ....DESCP_91= ' " + ddDefect.Text + " ';";
                ^                     ^

These spaces will be passed as is to the database engine and, unless you have a very specific datatype in the database column (CHAR/NCHAR) with these exact spaces around the values in the column, the command will never find any data.

But this is not the end of your problems. Concatenating strings in this way is a well known source of problems. What if the ddDefect.Text contains a single quote? Another syntax error. Then there is the problem of Sql Injection vulnerability, a very serious security problem.

So you should use a parameterized query like this

string constring = "Data Source=TS-ERP01;Initial Catalog=Touchstn02;Integrated Security=True";
string Query = "select * from  Defect_Codes Where DESCP_91= @ds91";
using(SqlConnection conDataBase = new SqlConnection(constring))
using(SqlCommand cmdDataBase = new SqlCommand(Query, conDataBase))
{
   try
   {
       conDataBase.Open();
       cmdDataBase.Parameters.Add("@ds91", SqlDbType.NVarChar).Value = ddDefect.Text;
       using(SqlDataReader myReader = cmdDataBase.ExecuteReader())
       {
           while (myReader.Read())
           {
              string sDEF = myReader["DEFECT_91"].ToString();
              txtNcm.Text = sDEF;
           }
       }
   }
   catch (Exception ex)
   {
       MessageBox.Show(ex.Message);
   }
}

Notice also that SqlConnection, SqlCommand and SqlDataReader are disposable objects and thus you should try to always use the using statement to be certain that these objects free the unmanaged resources acquired during they work (particularly the SqlConnection object)

Worked like a charm - you guys are amazing. Also thank you for the added tip and code for security - MUCH appreciated! — Alan Robertson, May 03 '17 at 12:46

Database values in textbox if select Combobox

1 Answers1