Auto Complete in Datagrid

VB.NET example


Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Dim scAutoComplete As New AutoCompleteStringCollection
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConn As String
Dim da As SqlDataAdapter
Dim conn As SqlConnection
Dim ds As New DataSet
strConn = "Server = .;Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(strConn)
da = New SqlDataAdapter("Select * from [Order Details]", conn)
da.Fill(ds, "Orders")
DataGridView1.DataSource = ds.Tables("Orders")
For x As Integer = 1 To 61000
scAutoComplete.Add(x.ToString)
Next
End Sub
Private Sub DataGridView1_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
If e.ColumnIndex = 1 Then
Debug.Print(e.FormattedValue.ToString)
If Not scAutoComplete.Contains(e.FormattedValue.ToString) Then
MessageBox.Show("Invalid Entry")
e.Cancel = True
End If
End If
End Sub
Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
If DataGridView1.CurrentCell.ColumnIndex = 1 AndAlso TypeOf e.Control Is TextBox Then
With DirectCast(e.Control, TextBox)
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
.AutoCompleteSource = AutoCompleteSource.CustomSource
.AutoCompleteCustomSource = scAutoComplete
End With
End If
End Sub
End Class


C# example

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace CSAutoComplete
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
AutoCompleteStringCollection scAutoComplete = new AutoCompleteStringCollection();
private void Form1_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
String strConn = "Server = .;Database = NorthWind; Integrated Security = SSPI;";
SqlConnection conn = new SqlConnection(strConn);
SqlDataAdapter da = new SqlDataAdapter("Select * from [Order Details]", conn);
da.Fill(dt);
dataGridView1.DataSource = dt;
for (int x = 1; x <= 61000; x++ )
{
scAutoComplete.Add(x.ToString());
}
}
private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
if (dataGridView1.CurrentCellAddress.X == 1)
{
TextBox txt = e.Control as TextBox;
txt.AutoCompleteCustomSource = scAutoComplete;
txt.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
txt.AutoCompleteSource = AutoCompleteSource.CustomSource;
}
}
private void dataGridView1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
if(e.ColumnIndex==1)
{
if(!scAutoComplete.Contains(e.FormattedValue.ToString()))
{
MessageBox.Show("Invalid Data");
e.Cancel=true;
}
}
}
}
}