title: Auto-Complete in textbox using data from a SQL Server database
body: <p>I am trying to get my textbox to auto-complete when a user types in it to write a query. This would be similar to how SQL Server Management Studio does it and gives the option as you type to arrow down or click on a table name or column name. Here is the following code I have.</p>
  
  <pre><code>public void loadData()
  {
      var myConnection = new SqlConnection(DBConnectionBox.Text);
      myConnection.Open();
  
      AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
      string query = @"Select distinct [name] from [INFORMATION_SCHEMA.TABLES]";
  
      SqlCommand cmd = new SqlCommand(query, myConnection);
  
      SqlDataReader dr = cmd.ExecuteReader();
  
      if (dr.HasRows == true)
      {
          while (dr.Read())
              namesCollection.Add(dr["name"].ToString());
      }
  
      dr.Close();
      myConnection.Close();
  
      ManualQueryBox.AutoCompleteMode = AutoCompleteMode.Append;
      ManualQueryBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
      ManualQueryBox.AutoCompleteCustomSource = namesCollection;
  }
  
  private void ManualQueryBox_KeyUp(object sender, KeyEventArgs e)
  {
      loadData();
  }
  </code></pre>
  
  <p>This is the code I use to grab what is in the textbox and execute it.</p>
  
  <pre><code>private void ExecuteBtn_Click(object sender, EventArgs e)
  {
      this.ClientInfoDGV.DataSource = null;
      this.ClientInfoDGV.Rows.Clear();
  
      var myConnection = new SqlConnection(DBConnectionBox.Text);
  
      var ManualCmd = new SqlCommand(ManualQueryBox.Text);
      ManualCmd.Connection = myConnection;
      ManualCmd.CommandType = CommandType.Text;
  
      SqlDataAdapter SqlAdap = new SqlDataAdapter(ManualCmd);
      DataTable MQRecord = new DataTable();
      SqlAdap.Fill(MQRecord);
  
      ClientInfoDGV.DataSource = MQRecord;
  }
  </code></pre>
  
  <p>I have never done autofill before but looking around I have see some asp.net AJAX control toolkit, but I am not entirely sure how that all works. Any help is welcome.</p>
  
  <p>Update to auto-fill code</p>
  
  <pre><code>public void loadData()
          {
              var myConnection = new SqlConnection(DBConnectionBox.Text);
              myConnection.Open();
              AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
              string query = @"Select distinct [Id] from [Clients]";
              SqlCommand cmd = new SqlCommand(query, myConnection);
              SqlDataReader dr = cmd.ExecuteReader();
              if (dr.HasRows == true)
              {
                  while (dr.Read())
                      namesCollection.Add(dr["id"].ToString());
  
              }
  
              dr.Close();
              myConnection.Close();
  
              ManualQueryBox.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
              ManualQueryBox.AutoCompleteSource = AutoCompleteSource.CustomSource;
              ManualQueryBox.AutoCompleteCustomSource = namesCollection;
          }
  </code></pre>