Visual Basic Online Course - WildCards in Database

Visual Basic Online Course

Using Wild Cards when searching Database

Problem : I have 50 records of mobile phone numbers and they start with different digits according to network area code, I want to search the [MobileNo] record from [TextBox] so when I write the first digit i get some search result and the second digit returns the next search result .... etc as i go, using TextBox1_Change() event, i want when every time i type a digit i get a result in the DataGrid control

Solution : Using ADO + MS-Access 2003 + Sql Statements

	'Visual Basic 6.0 Lessons and Tricks
	'How to use the Wildcards in Visual Basic 6.0
	'to create a custom search
	Option Explicit
	Public CN As New ADODB.Connection
	Public RS As New ADODB.Recordset
	Private Sub Form_Load()
	If CN.State = 1 Then CN.Close
	CN.Open ("Provider = Microsoft.Jet.Oledb.4.0 ; Data Source = " & App.Path & "/test.mdb")
	End Sub
	Private Sub Text1_Change()
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	RS.Open ("Select * from students where mobNo LIKE '%" & Text1.Text & "%'"), CN, adOpenDynamic, adLockOptimistic
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

That means : Search all records that contain these values within.
But, If you want to search for records start with certain values, then use :

	'Visual Basic Online Course
	'Wild Cards in Database - Sql Statement
	Dim SqlStr As String
	SqlStr = ("Select * from students where mobNo LIKE '" & Text1.Text & "%'")
	RS.Open (SqlStr), CN, adOpenDynamic, adLockOptimistic

view raw gistfile1.vb hosted with ❤ by GitHub

And, If you want to search for records end with certain values, then use :

	'Visual Basic Online Course
	'Select Column From Table Where Value Like Value
	Dim SqlStr As String
	SqlStr = ( "Select * from students where mobNo LIKE '%" & Text1.Text & "'")
	RS.Open (SqlStr), CN, adOpenDynamic, adLockOptimistic
	Set DataGrid1.DataSource = RS
	DataGrid1.Refresh
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

---

♥ Visual Basic 6.0 Course Online :

• Automate MS-Access Database in Visual Basic 6.0 though a network
• Visual Basic 6.0 Analog Clock
• Visual Basic 6.0 Temperature Convertor
• Visual Basic 6.0, MS-Access 2003 and Crystal Reports - Phone book
• Visual Basic 6.0, MS-Access 2003 Database and DataGridView
• Visual Basic 6.0  FTP full application source code
• Visual Basic 6.0 Color Picker tool source code
• Visual Basic 6.0 TreeView Control lesson (Part1 - Part 2 - Part3 - Part4 - Part5) Arabic.
• Visual Basic 6.0 - Introduction to Graphics (Part1 - Part2 - Part3)  
• Visual Basic 6.0 and MS-Excel 2003 tutorials (Part1 - Part2 - Part3 - Part4 - Part5 - Part6)