Today, I will teach you how to fill the data in a
ListBox with two
DisplayMember in
MySQL Database and
Visual Basic 2008. This will show you how the two fields of a table in the database are displayed in a
ListBox.
First create a
Database named “info”.
Then, create a table named
“name”.
After that, insert the data in the table that you have created.
('Mark', 'Palcios'),
('Craig', 'Palcios')
Open the
Visual Basic 2008, create a Project. In the
Form add a
ListBox.
Double click the
Form and do this code for setting up the connection in
MySQL Database and
Visual Basic 2008. Then, declare all the
classes that you needed above the
Form1_Load.
Imports MySql.Data.MySqlClient
Public Class Form1
'declare a string varible which has the value of a string connection
Dim strCon As String = "server=localhost;user id=root;database=info"
'create a private function for setting up the MySQL connection.
Private Function myCon() As MySqlConnection
'return new connection.
Return New MySqlConnection(strCon)
End Function
'pass the value of myCon to a con
Dim con As MySqlConnection = myCon()
'a bridge between the database and the dataset for saving and retrieving the data.
Dim da As New MySqlDataAdapter
'a set of comman in MySQL
Dim cmd As New MySqlCommand
'contain the basic element of a database such as key, table, indexes and even the relationship of two tables
Dim ds As New DataSet
End Class
In the
Form1_Load, do this code for filling the
ListBox with data that came from the database.
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'openning the connection
con.Open()
'declare a string variable to store your select query
'combining the two fields to display in the listbox
Dim sql As String = "SELECT id, concat (firstname,' ', lastname) as 'fullname' FROM name"
'set your commands
With cmd
.Connection = con
'properties that set the name of the table in a query
.CommandText = sql
End With
ds = New DataSet
da = New MySqlDataAdapter(sql, con)
'refreshes the rows in the dataset to match in the datasource using the table names
da.Fill(ds, "student")
With ListBox1
'set the data source for the listbox
.DataSource = ds.Tables(0)
'use the actual value for the items in a listbox
.ValueMember = "id"
'set a property to display in the listbox
.DisplayMember = "fullname"
End With
'closing the connection
End Sub
