dragorixvlad
Story Format Specialist
LEVEL 1
200 XP
In this tutorial I will teach you how to Auto Complete a TextBox inside the DataGridView in the Visual Basic 2008 and MySQL Database. Others are used to store the list of items in the ComboBox. So, for a change, I used a TextBox because it’s easy to use and you don’t have to drop down in searching for the items that you needed. Just simply type the initial letter of the items that you’re going to search. I based this on my tutorials which are the “Simple AutoComplete ComboBox in a DataGridView” and “Advance Autocomplete/Autosuggest in a Textbox Using Visual Basic 2008 and MySQL database”.
To start with:
1. Create a database.
2. Create a Table.
3. Insert this records in the table that you have created.
4. Open the Visual Basic 2008, Create a project and create a Form. In the Form, add the DataGridView.
5. Double click the Form and above the Form1_Load
, set a connection of MySQL Database to Visual Basic 2008. After that, declare all the classes and variables that you needed.
6. In the Form1_Load
, declare the variable txt as a TextBox Column and add it in the DataGridView.
7. Go back to the Design Views, double click the DataGridview and change the method name in EditingControlShowing.
8. In the DataGridView1_ EditingControlShowing
, do the following code for storing data in the TextBox and the autocomplete properties of it.
Download
To start with:
1. Create a database.
2. Create a Table.
- CREATE
TABLE
[url=http://dev.mysql.com/doc/refman/%35%2E%31/en/control-flow-functions.html]IF
NOT
EXISTS
[/url] `item`
(
- `id`
int
(
11
)
NOT
NULL
,
- PRIMARY KEY
(
`id`
)
- )
ENGINE
=
MyISAM DEFAULT
CHARSET
=
utf8;
3. Insert this records in the table that you have created.
- INSERT
INTO
`item`
(
`id`
)
VALUES
- (
213
)
,
(
190
)
,
- (
102
)
,
- (
103
)
,
- (
104
)
,
- (
107
)
,
- (
124
)
,
- (
141
)
,
- (
142
)
,
- (
143
)
,
- (
144
)
,
- (
149
)
,
- (
174
)
,
- (
176
)
,
- (
178
)
,
- (
200
)
,
- (
201
)
,
- (
205
)
,
- (
206
)
,
- (
207
)
,
- (
100
)
4. Open the Visual Basic 2008, Create a project and create a Form. In the Form, add the DataGridView.
5. Double click the Form and above the Form1_Load
, set a connection of MySQL Database to Visual Basic 2008. After that, declare all the classes and variables that you needed.
- 'add reference
- Imports
MySql.
Data
.
MySqlClient
- Public
Class
Form1
- 'set up the string connection of MySQL Database
- Dim
con As
MySqlConnection =
New
MySqlConnection(
"server=localhost;user id=root;database=employeesdb"
)
- 'a set of commands in MySQL
- Dim
cmd As
New
MySqlCommand
- 'a Bridge between a database and datatable for retrieving and saving data.
- Dim
da As
New
MySqlDataAdapter
- 'a specific table in the database
- Dim
dt As
New
DataTable
- Private
Sub
Form1_Load(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
MyBase
.
Load
- End
Sub
- End
Class
6. In the Form1_Load
, declare the variable txt as a TextBox Column and add it in the DataGridView.
- Private
Sub
Form1_Load(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
MyBase
.
Load
- 'declaring a new textbox column in the datagridview
- Dim
txt As
New
DataGridViewTextBoxColumn
- 'adding a textbox column in the datagridview
- DataGridView1.
Columns
.
Add
(
txt)
- End
Sub
7. Go back to the Design Views, double click the DataGridview and change the method name in EditingControlShowing.
8. In the DataGridView1_ EditingControlShowing
, do the following code for storing data in the TextBox and the autocomplete properties of it.
- Private
Sub
DataGridView1_EditingControlShowing(
ByVal
sender As
Object
, ByVal
e As
System.
Windows
.
Forms
.
DataGridViewEditingControlShowingEventArgs
)
Handles
DataGridView1.
EditingControlShowing
- 'declare for a new textbox
- Dim
txt As
New
TextBox
- con.
Open
(
)
- 'set a new spicific table in the database
- dt =
New
DataTable
- 'set your commands for holding the data.
- With
cmd
- .
Connection
=
con
- .
CommandText
=
"Select * from employees"
- End
With
- 'filling the table in the database
- da.
SelectCommand
=
cmd
- da.
Fill
(
dt)
- Dim
r As
DataRow 'represents a row of data in the datatable
- For
Each
r In
dt.
Rows
'get a collection of rows that belongs to this table
- 'the control shown to the user for editing the selected cell value
- If
TypeOf
e.
Control
Is
TextBox Then
- txt =
e.
Control
- 'adding the specific row of the table in the AutoCompleteCustomSource of the textbox
- txt.
AutoCompleteCustomSource
.
Add
(
r.
Item
(
"EMPLOYEE_ID"
)
.
ToString
)
- txt.
AutoCompleteMode
=
AutoCompleteMode.
Suggest
- txt.
AutoCompleteSource
=
AutoCompleteSource.
CustomSource
- End
If
- Next
- 'closing the connection
- con.
Close
(
)
- End
Sub
Download
You must upgrade your account or reply in the thread to view the hidden content.