Hi badams,
Welcome to MSDN forums!
Here are several approaches to search & filter records from database for you to check, which can give you some ideas.
1) Use T-SQL Select command to filter records
Prerequisites: DataGridView1 and TextBox1 on Form1.
|
Imports System.Data.OleDb
Public Class Form1
' Handle TextBox_TextChanged event
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim keywords As String = TextBox1.Text
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
' Use wildcard
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1 WHERE Filed1 Like '%" & keywords & "%' ", con)
' or Where Filed1='" & keywords & "'
con.Open()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()
myDA.Fill(myDataSet, "MyTable")
DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView
End Sub
End Class
|
2) Use DataView.RowFilter Property to filter records
Prerequisites: DataGridView1 and TextBox1 on Form1.
|
Imports System.Data.OleDb
Public Class Form1
Dim ds As DataSet
' Firstly binding all records to DataGridView
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)
con.Open()
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
ds = New DataSet()
myDA.Fill(ds, "MyTable")
con.Close()
DataGridView1.DataSource = ds.Tables("MyTable").DefaultView
End Sub
'Then filter datatable view
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim keywords As String = TextBox1.Text
ds.Tables("MyTable").DefaultView.RowFilter = "Field1 =" & keywords
' or = "Field1 Like '%" & keywords & "%' "
End Sub
End Class
|
Related thread:
http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/bdd212be-f815-4023-9db1-582b2439987a
3) You can use LINQ to SQL to filter records if you use SQL Server database.
|
Dim db As DataClasses1DataContext = New DataClasses1DataContext()
' Using Like wildcard in LING to SQL
Dim tableQuery = _
From t In db.Table1 Where t.Filed1 Like "%" & keywords & "%" _
Select t
' Or using String.Contains method instead of Like wildcard in LING to SQL
Dim tableQuery = _
From t In db.Table1 Where t.Filed1.Contains(keywords) _
Select t
DataGridView1.DataSource = tableQuery
|
Some tutorials about LINQ to SQL:
http://msdn.microsoft.com/en-us/library/bb546190.aspx
http://blogs.msdn.com/charlie/archive/2007/11/19/connect-to-a-sql-database-and-use-the-sql-designer.aspx
http://blogs.msdn.com/mitsu/archive/2008/04/02/visual-linq-query-builder-for-linq-to-sql-vlinq.aspx
Best regards,
Martin Xie
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Home > Visual Basic Power Packs > Datagrid BindingSource Filter Reverencing Another Datagrid Value