Jevon Davis Jevon Davis - 1 month ago 5
SQL Question

How to populate second TextBox based on the first TextBox that is pulling from SQL?

My problem is that of auto populating a second text box from a database based of the first text box.

This is the autocomplete TextBox, this works fine, no issues here.

private void textBox1_TextChanged(object sender, EventArgs e)
{
try
{
string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
string query = "SELECT Code FROM dbo.Liguanea_Lane";
SqlCommand cmd = new SqlCommand(query, con);

SqlDataReader dr = cmd.ExecuteReader();
AutoCompleteStringCollection mycollection = new AutoCompleteStringCollection();
while (dr.Read())
{
mycollection.Add(dr.GetString(0));
}
textBox1.AutoCompleteCustomSource = mycollection;
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}


This is the second TextBox which would auto populate based of the selection from the first TextBox and filters out the data from SQL based on the option selected in the first TextBox. Am not sure where I am going wrong. Please take a look:

private void textBox2_TextChanged(object sender, EventArgs e)
{
try
{
string connectionString = "Data Source=LPMSW09000012JD\\SQLEXPRESS;Initial Catalog=Pharmacies;Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
string query = "SELECT description FROM dbo.Liguanea_Lane where code= '" + textBox1.Text + "'"; // this query
SqlCommand cmd = new SqlCommand(query, con);
con.Close();
}
catch (SqlException sql)
{
MessageBox.Show(sql.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

Answer

I solved the problem. What i did was switch over to a autocomplete comboBox. I first created a fillCombo method that populated the respective comboBox with the items from the SQL database

void fillCombo()
    {
           try
           {

               string connectionString = "Data Source=JAVY26;Initial Catalog=Pharmacies;Integrated Security=True";
               SqlConnection con = new SqlConnection(connectionString);
               con.Open();
               string query = "SELECT * FROM dbo.Liguanea_Lane";
               SqlCommand cmd = new SqlCommand(query, con);

               SqlDataReader dr = cmd.ExecuteReader();
               while (dr.Read())
               {
                   string scode = dr.GetString(dr.GetOrdinal("code"));
                   comboBox2.Items.Add(scode);
               }
           }
           catch (Exception ex)
           {

               MessageBox.Show(ex.ToString());
           }*/


    }

And then implemented code within the comboBox which I created a string variable to store the contents of the "description" column within the SQL Database, which then is assigned to the text box which would return the value based on the selection of the fillCombo method.

   private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {

            string connectionString = "Data Source=JAVY26;Initial Catalog=Pharmacies;Integrated Security=True";
            string query = "SELECT * FROM dbo.Liguanea_Lane WHERE code = '" + comboBox2.Text + "' ; ";
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                string sdes = dr.GetString(dr.GetOrdinal("description"));
                textBox5.Text = sdes;

            }

        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.ToString());
        }