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
– Sean Stayns, Commented May 2, 2017 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
– Spencer Wieczorek, Commented May 2, 2017 at 19:22

Community · Accepted Answer · 2017-05-23 11:54:37Z

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!

Collectives™ on Stack Overflow

Database values in textbox if select Combobox

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related