Multi-Columns AutoComplete in a TextBox Using C# and SQL Server

Multi-Columns AutoComplete in a TextBox Using C# and SQL Server

Autocomplete plays an important role in searching for data or any records in the database.

So, in this tutorial, I will teach you an advanced method of autocomplete in a textbox using C# and SQL Server.

This method has the ability to get the records in every column of the table in the database.

It is very helpful because you don’t have to exert much effort when retrieving data or records in the database.

Let’s begin:

Create a database in the SQL Server 2005 Express Edition and name it “dbplace“.

Create a table in the database that you have created.

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblplace](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [p_Country] [nvarchar](50) NULL,
 [p_City] [nvarchar](50) NULL,
 [p_ZipCode] [nchar](10) NULL,
 CONSTRAINT [PK_tblplace] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

After creating the database, open Microsoft Visual Studio and create a new windows form application for c#. Then, do the form just like this.

Go to the solution explorer and hit “code view” to fire the code editor.

In the code editor, declare all classes and variables that are needed.

Note: Put “using System.Data.SqlClient;” above the namespace to access sql server library.

 //initialize all classes
 SqlConnection conn = new SqlConnection();
 SqlCommand cmd = new SqlCommand();
 SqlDataAdapter da = new SqlDataAdapter();
 DataTable dt = new DataTable();

 //declaring variables
 string query;
 int result;

After that, create a method for the autocomplete textbox.

 private void AutoSuggest()
 {
 try
 {

 //set a query for retrieving data in the database
 query = "Select * from tblplace";
 //initialize new Sql commands
 cmd = new SqlCommand();
 //hold the data to be executed.
 cmd.Connection = conn;
 cmd.CommandText = query;
 //initialize new Sql data adapter
 da = new SqlDataAdapter();
 //fetching query in the database.
 da.SelectCommand = cmd;
 //initialize new datatable
 dt = new DataTable();
 //refreshes the rows in specified range in the datasource. 
 da.Fill(dt);
 int max = dt.Columns.Count;

 txtMultiSuggest.AutoCompleteCustomSource.Clear();
 foreach (DataRow r in dt.Rows)
 {
 for (int i = 1; i < max; i++)
 {
 
 //getting all rows in the specific field|Column
 var rw = r.Field<string >(i);
 //MessageBox.Show(rw);

 //Set the properties of a textbox to make it auto suggest.
 txtMultiSuggest.AutoCompleteMode = AutoCompleteMode.Suggest;
 txtMultiSuggest.AutoCompleteSource = AutoCompleteSource.CustomSource;
 //adding all rows into the textbox
 txtMultiSuggest.AutoCompleteCustomSource.Add(rw);
 }
 } 
 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 
 }

 }

Go back to the design view, establish a connection between SQL server and C#. Then, call a method of an autocomplete textbox.

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection between SQL server and Visual C#
 conn.ConnectionString = "Data Source=.\\SQLEXPRESS;database=dbplace;trusted_connection=true;";
 AutoSuggest();
 }

Go back to the design view again, double-click the button and do the following code for saving the data in the database.

 private void Button1_Click(object sender, EventArgs e)
 {
 try
 {
 //opening connection
 conn.Open();
 //create an insert query;
 query = "INSERT INTO tblplace (p_Country,p_City,p_ZipCode) VALUES('" + txtCountry.Text + "','" + txtCity.Text + "','" + txtZipCode.Text + "')";
 //it holds the data to be executed.
 cmd.Connection = conn;
 cmd.CommandText = query;
 //execute the data.
 result = cmd.ExecuteNonQuery();
 //validate the result of the executed query.
 if (result > 0)
 { 
 MessageBox.Show("Data has been saved in the SQL database");
 //calling a method
 AutoSuggest();

 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 //closing connection
 conn.Close();

 }
 catch (Exception ex)//catch exeption
 {
 //displaying error message.
 MessageBox.Show(ex.Message);
 }
 }

Output:

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

Download Source Code