VB.NET tutorial: connect to database

Dictionary

Connect to database file

The code below will help you to connect to the database file and retrieve data to put in the Lstterm listbox:

-Double-click on the form to open the code window

-In Declarations section type the following code:

 'declare global variables
Dim cn As OleDb.OleDbConnection
Dim reader As OleDb.OleDbDataReader
Dim com As OleDb.OleDbCommand
Dim rs As DataSet
Dim ad As OleDb.OleDbDataAdapter

This code will declare cn variable as the OleDbConnection class, reader variable as OleDbReader class, com variable as OleDbReader Class, rs variable as DatSet class, and ad as OleDbDataAdapter classs. These variables will be used to create their objects of their types.

-Create a procedure called myconnect and type the code as shown below:

Sub myconnect()
On Error GoTo aa
'connect to database
rs = New DataSet
cn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\data.accdb";)
cn.Open()
ad = New OleDb.OleDbDataAdapter("SELECT * FROM Tblterms ORDER BY Enterms", cn)

ad.Fill(rs,"Tblterms")
com = New OleDb.OleDbCommand("SELECT Enterms FROM Tblterms ORDERY Enterms, cn)
reader = com.ExecuteReader

'clear list
Lstterms.Items.Clear()
'clear txtresult
txtresult.Text = ""
While reader.Read
Lstterms.Items.Add(reader(0).ToString)

End While
Txtbox.Focus()

aa: If Err.Description <> "" Then MsgBox(Err.Description)
End Sub

The code starts by creating a DataSet object to store the table got from the database and a connection object to connect to the database file:

rs = New DataSet
cn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\data.accdb";)

cn.Open()

The Adapter object is created to get data from the Tblterms table and

the data is filled to the DataSet object rs by using the Fill() method of the adapter:

ad = New OleDb.OleDbDataAdapter("SELECT * FROM Tblterms ORDER BY Enterms, cn)
ad.Fill(rs, "Tblterms")

The Command object com also is also created to get data from the table and populate the list box:

com = New OleDb.OleDbCommand("SELECT Enterms FROM Tblterms ORDER BY Enterms", cn)
reader = com.ExecuteReader
'clear list
Lstterms.Items.Clear()
'clear txtresult
txtresult.Text = ""
While reader.Read
Lstterms.Items.Add(reader(0).ToString)
End While

The search text box is set the focus to make sure that the cursor is active in this box for text input.

Txtbox.Focus()

By putting the procedure myconnect() in the form load event, the list box Lstterms will be populated by the English words:

Private Sub Dic_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

myconnect()

End Sub

Now you have data filled in the list box Lstterms when the form loads. The next phase is to write some VB code to create a workable search text box.