James Robert Singleton James Robert Singleton - 3 months ago 19
C# Question

Auto-Complete in textbox using data from a SQL Server database

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.

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();
}


This is the code I use to grab what is in the textbox and execute it.

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;
}


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.

Update to auto-fill 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;
}

Answer

Try changing autocomplete mode to suggestappend:

ManualQueryBox.AutoCompleteMode=AutoCompleteMode.SuggestAppend;

I don't think you need to load the data every time in the key up event as long as you load it once (perhaps in form load) and specify the source of the text box.

This might help:https://msdn.microsoft.com/en-us/library/system.windows.forms.textbox.autocompletemode(v=vs.110).aspx

Comments