Autofill textboxes from database based on one text

问题:

This question already has an answer here:

Searching in databound comboBox? (1 answer)

Closed 6 years ago.

I have a simple form in which there are 3 textboxes as seen in image below :

using autocompelete function on textchanged event, I am displaying data from database in textbox1(Person Name). Now if your user selects a particular name from suggested items, I want to auto fill textbox2 and textbox3 from database based on value from textbox1. How should I do that?

Code of textbox1 :

private void textBox1_TextChanged(object sender, EventArgs e)
{
    AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
    SqlConnection con = new SqlConnection(@"***my connection string***");
    con.Open();
    SqlCommand cmnd = con.CreateCommand();
    cmnd.CommandType = CommandType.Text;
    cmnd.CommandText = "SELECT * FROM tblTicketDetail";
    SqlDataReader dReader;
    dReader = cmnd.ExecuteReader();

    if (dReader.Read())
    {
        while (dReader.Read())
            namesCollection.Add(dReader["ContactPerson"].ToString());
    }
    else
    {
        MessageBox.Show("Data not found");
    }
    dReader.Close();

    textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;
    textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
    textBox1.AutoCompleteCustomSource = namesCollection;
}

now how to autofill textbox2 andd textbox3?

Regards.

回答1:

    try
    {
        int i = Convert.ToInt32(CBCompanies.SelectedValue.ToString());
        DataTable td = new DataTable();
        da = new SqlDataAdapter("select * from tblCustomerCompany where CustomerID =" + CBCompanies.SelectedValue.ToString() + "  ORDER BY CustomerName", conn);
        conn.Open();
        da.Fill(td);
        conn.Close();
        textBox3.Text = td.Rows[0].ItemArray[5].ToString();
    }
    catch { }  

回答2:

you can make this work in Form load to populate the list of Contact, then in select change you check if this contact exist and show other detail

private Class Contact
{
    public String Name { get; set; }
    public String Number{ get; set; }
    public String Mail { get; set; }
}

List<Contact> _listContact = new List<Contact>();
private void Form_Load(object sender, EventArgs e)
{
  AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
  SqlConnection con = new SqlConnection(@"***my connection string***");
  con.Open();
  SqlCommand cmnd = con.CreateCommand();
  cmnd.CommandType = CommandType.Text;
  cmnd.CommandText = "SELECT * FROM tblTicketDetail";        
  SqlDataReader dReader;
  dReader = cmnd.ExecuteReader();

  if (dReader.Read())
   {
     while (dReader.Read())
     {
        namesCollection.Add(dReader["ContactPerson"].ToString());
        Contact cont = Contact{Name = dReader["ContactPerson"].ToString(),
                              Number = dReader["ContactNumber"].ToString()
                              Mail = dReader["ContactMail"].ToString() }
        _listContact.add(cont);
     }
   }
  else
   {
      MessageBox.Show("Data not found");
   }
  dReader.Close();

}

then in your textBox1_TextChanged

private void textBox1_TextChanged(object sender, EventArgs e)
{
   if( _listContact.Contain(sender.Text))
   {
      textbox2.Text = _listContact[sender.Text].Number;
      textbox3.Text = _listContact[sender.Text].Mail;
   }
}

回答3:

Just as someone said it is not a good practice to have queries directly in code. But anyways what stops you from adding a where clause in your query. Also I am assuming that you need just the first record in which case, as per my modification, your namesCollection object will contain only 1 value. For Example:

private void textBox1_TextChanged(object sender, EventArgs e)
{
    AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
    SqlConnection con = new SqlConnection(@"***my connection string***");
    con.Open();
    SqlCommand cmnd = con.CreateCommand();
    cmnd.CommandType = CommandType.Text;

    //Changed code here
    cmnd.CommandText = "SELECT TOP 1 * FROM tblTicketDetail where LOWER(name) = LOWER('" + textBox1.Text + "')";
    SqlDataReader dReader;
    dReader = cmnd.ExecuteReader();

    if (dReader.Read())
    {
        while (dReader.Read())
        {
            namesCollection.Add(dReader["ContactPerson"].ToString());

            //Added code here
            textBox2.Text = dReader["Number"].ToString();
            textBox3.Text = dReader["email"].ToString();
        }
    }
    else
    {
        MessageBox.Show("Data not found");
    }
    dReader.Close();

    textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;
    textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
    textBox1.AutoCompleteCustomSource = namesCollection;
}

If this not what you want then please elaborate your question.

Hope this helps

回答4:

1. You may revise your code and apply LINQ. it is easier to handle then writing old fashioned query strings.

2. always close your connection after you ended the DB operation

3. Cache DB data maybe in the constructor of the main form's creation or in its Loaded event. Then you may accesss very fast the data from your in-memory object