C# : Autocomplete in a Textbox with SQL Server

Submitted by janobe on Friday, July 22, 2016 - 09:15.
When you search data in the database, it is hard to search accurate data without a hard copy for your basis. So, in this tutorial, I will show you how to create an Autocomplete Textbox using C#.net and SQL Server 2005. This method has the ability to auto suggest and append the corresponding data on the Textbox when the first letter has an exact the same data in the database

Let's get started:

Step 1. Create a database and name it "dbplace". Step 2. Open Microsoft Visual Studio 2008 and create new Windows Form Application. Then, drag a TextBox on the form and it will look like this. fig 1 Step 3. Go to the Solution Explorer, double-click the “View Code” to fire the code editor. fig 2 Step 4. Declare all the classes and variables that are needed.
Note: Put using "System.Data.SqlClient;" above the namespace to access sql server library and to avoid errors when declaring the SQL classes.
  1.  //initialized all classes
  2.  SqlConnection con = new SqlConnection();
  3.  SqlCommand cmd = new SqlCommand();
  4.  SqlDataAdapter da = new SqlDataAdapter();
  5.  DataTable dt = new DataTable();
  6.  
  7. //declare a variable for the query.
  8. string strquery;
Step 4. Go back to the design view, double click the Form and do the following code for creating an autocompete TextBox and retrieving data in the SQL database.
  1.   private void Form1_Load(object sender, EventArgs e)
  2.         {
  3.             //Connection between SQL Server to C#
  4.             con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=dbplace;trusted_connection=true;";
  5.             //set a query for retrieving data in the database
  6.             strquery = "Select p_Country from tblplace";
  7.             //initialize new Sql commands
  8.             cmd = new SqlCommand();
  9.             //hold the data to be executed.
  10.             cmd.Connection = con;
  11.             cmd.CommandText = strquery;
  12.             //initialize new Sql data adapter
  13.             da = new SqlDataAdapter();
  14.             //fetching query in the database.
  15.             da.SelectCommand = cmd;
  16.             //initialize new datatable
  17.             dt = new DataTable();
  18.             //refreshes the rows in specified range in the datasource.
  19.             da.Fill(dt);
  20.  
  21.              textBox1.AutoCompleteCustomSource.Clear();
  22.             foreach (DataRow r in dt.Rows)
  23.             {
  24.                 //getting all rows in the specific field|Column
  25.                 var rw = r.Field<string>("p_Country");
  26.  
  27.                 //Set the properties of a textbox to make it auto suggest and append.
  28.                 textBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
  29.                 textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
  30.                 //adding all rows into the textbox
  31.                 textBox1.AutoCompleteCustomSource.Add(rw);  
  32.            
  33.             }
  34.  
  35.         }
For all students who need a programmer for your thesis system or anyone who needs a source code in any programming languages. You can contact me @ : Email – [email protected] Mobile No. – 09305235027 – tnt
Tags
autocomplete in a textbox
SQL database
c#

Add new comment

About text formats
  • 935 views