Filling data in a listbox with two DisplayMember

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”.
  1. CREATE DATABASE `info` ;
Then, create a table named “name”.
  1. CREATE TABLE `info`.`name` (
  2. `firstname` TEXT NOT NULL ,
  3. `lastname` TEXT NOT NULL ,
  4. PRIMARY KEY ( `id` )
After that, insert the data in the table that you have created.
  1. INSERT INTO `name` ( `firstname` , `lastname` )
  2. VALUES ('Janno', 'Palcios'),
  3. ('Mark', 'Palcios'),
  4. ('Craig', 'Palcios')
Open the Visual Basic 2008, create a Project. In the Form add a ListBox. List Form 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.
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3. 'declare a string varible which has the value of a string connection
  4. Dim strCon As String = "server=localhost;user id=root;database=info"
  5. 'create a private function for setting up the MySQL connection.
  6. Private Function myCon() As MySqlConnection
  7. 'return new connection.
  8. Return New MySqlConnection(strCon)
  9. End Function
  10. 'pass the value of myCon to a con
  11. Dim con As MySqlConnection = myCon()
  12. 'a bridge between the database and the dataset for saving and retrieving the data.
  13. Dim da As New MySqlDataAdapter
  14. 'a set of comman in MySQL
  15. Dim cmd As New MySqlCommand
  16. 'contain the basic element of a database such as key, table, indexes and even the relationship of two tables
  17. Dim ds As New DataSet
  18. End Class
In the Form1_Load, do this code for filling the ListBox with data that came from the database.
  1. Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  2. 'openning the connection
  3. con.Open()
  4. 'declare a string variable to store your select query
  5. 'combining the two fields to display in the listbox
  6. Dim sql As String = "SELECT id, concat (firstname,' ', lastname) as 'fullname' FROM name"
  7. 'set your commands
  8. With cmd
  9. .Connection = con
  10. 'properties that set the name of the table in a query
  11. .CommandText = sql
  12. End With
  13. ds = New DataSet
  14. da = New MySqlDataAdapter(sql, con)
  15. 'refreshes the rows in the dataset to match in the datasource using the table names
  16. da.Fill(ds, "student")
  17. With ListBox1
  18. 'set the data source for the listbox
  19. .DataSource = ds.Tables(0)
  20. 'use the actual value for the items in a listbox
  21. .ValueMember = "id"
  22. 'set a property to display in the listbox
  23. .DisplayMember = "fullname"
  24. End With
  25. 'closing the connection
  26. con.Close()
  27. End Sub

Comments

Submitted byamith (not verified)on Tue, 05/12/2015 - 11:48

Hello there, I am creating a software to store hardcore invoice. and there are multiple items under one invoice. Plz help me with the logic, when I select multiple list items, they should be stored as multiple records in the database... Invoice 1 Sl# Items Qty Rate ============================ 1. Mouse 2 200 2. Keyboard 1 450 3. LG -Monitor 1 8500

Add new comment