In this article we will see how you can use automatic completion of a TextBox and a ListBox.
For that I have used one TextBox, one ListBox & one DataGrid.
When I start typing into the text box it will automatically display related names into the listbox & from that listbox when you click a particular name then the related data will display in the DataGrid.
For the concept of automatic completion you just write one query using "like" keyword.
Code looks like below:
lbAutoComplete.Items.Clear();
try
{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT StudName FROM Student WHERE StudName
like '" + txtAutoComplete.Text + "%'", conn);
dr = comm.ExecuteReader();
while (dr.Read())
{
lbAutoComplete.Items.Add(dr.GetValue(0).ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn.Close();
}
In the above code first we must clear our listbox every time because otherwise the data will be appended into the listbox causing duplicate data in it.
After that looking the query in that I used like keyword & after that whatever character is written into the textbox it will find the names from the table that start with those characters.
Here "%" is the wildcard character and by using it you will the get number of characters after or before your given character.
See images below for a clearer understanding:
Display names that start with "a"...
Display names that start with "ak"...
Display names that start with "aksh"...
Display names that start with "ash"...
After that when the user selects the name from the ListBox it will automatically display that record into the DataGrid.
See below Image :
Main Code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;
namespace WPFAutoComplete
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True");
SqlDataReader dr;
public MainWindow()
{
InitializeComponent();
}
private void txtAutoComplete_TextChanged(object sender, TextChangedEventArgs e)
{
lbAutoComplete.Items.Clear();
try
{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT StudName FROM Student WHERE StudName like '" + txtAutoComplete.Text + "%'", conn);
dr = comm.ExecuteReader();
while (dr.Read())
{
lbAutoComplete.Items.Add(dr.GetValue(0).ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn.Close();
}
}
private void lbAutoComplete_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
conn.Open();
SqlCommand comm = new SqlCommand("SELECT * FROM Student Where StudName='" + lbAutoComplete.SelectedItem.ToString() + "'", conn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(ds);
MyDataGrid.ItemsSource = ds.Tables[0].DefaultView;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn.Close();
}
}
}
}