Filling Combobox with Data Stored in MySQL Database in Visual Basic.Net

Submitted by joken on Tuesday, September 17, 2013 - 13:17.
In one of our previous tutorials, we discuss on How to "Connect MySQL Database using Visual Basic.Net". We also discuss on How to "Insert and Read data from MySQL Database using Visual Basic". This time, we are going to answer some of the questions found in the internet like “How to fill combobox with a data stored in a table in MySQL Database” and “How to populate combobox with MySQL Database”. Let’s start building our project by designing first our User Interface and the final output of this application is looks like shown below. So we need to add three labels and change the each text to Full Name, Username and Password. Then add a button and name it as “btngo” and change the text property into “Go”. Next add two textbox and name the first one as “txtuser” and place it beside the Username, then the other one name it as “txtpass” and place it beside “Password”. And finally add a combobox and leave all the property to its default settings. Next, double click the form. And above public class add the following code. But to make sure you have already added the reference of mysql. And if you don’t know how to do it, just read follow this article called "Connect MySQL Database using Visual Basic.Net" and have some background how to connect MySQL to Visual Basic. 
  1. Imports MySql.Data.MySqlClient
Then, under public class add the following code and now it looks like as shown below. 
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3.     Dim con As New MySqlConnection
  4.     'MySqlCommand It represents a SQL statement to execute against a MySQL Database
  5.     Dim cmd As New MySqlCommand
  6.     'Represents a set of data commands and a database connection that 
  7.     'are used to fill a dataset and update a MySQL database. This class cannot be inherited.
  8.     Dim da As New MySqlDataAdapter
Next, on the form1_load. Add this following code: This code is responsible for populating the combobox with data stored from the database. 
  1. con.ConnectionString = ("server=localhost;user id=root;password=;database=test")
  2.         Try
  3.             'we open Connection
  4.             con.Open()
  5.  
  6.             With cmd
  7.                 .Connection = con
  8.                 .CommandText = "SELECT `fullname` from users;"
  9.             End With
  10.             'declare dt as new datatable
  11.             Dim dt As New DataTable
  12.  
  13.             With ComboBox1
  14.                 da.SelectCommand = cmd
  15.                 'it fills the da values into dt
  16.                 da.Fill(dt)
  17.                 'dt provides the data surce of combobox
  18.                 .DataSource = dt
  19.                 'specify the what to display
  20.                 .DisplayMember = "fullname"
  21.                 'and the value
  22.                 .ValueMember = "fullname"
  23.  
  24.             End With
  25.  
  26.  
  27.         Catch ex As Exception
  28.             MsgBox(ex.Message)
  29.         End Try
  30.         con.Close()
Before we forgot, heres the structure and the data of the table to be used in this project. 
  1. CREATE TABLE IF NOT EXISTS `users` (
  2.   `user_id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `fullname` varchar(30) NOT NULL,
  4.   `username` varchar(30) NOT NULL,
  5.   `password` varchar(50) NOT NULL,
  6.   PRIMARY KEY (`user_id`)
  7. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
  8.  
  9. --
  10. -- Dumping data for table `users`
  11. --
  12.  
  13. INSERT INTO `users` (`user_id`, `fullname`, `username`, `password`) VALUES
  14. (2, 'Juan Dela Cruz', 'coco', 'martin'),
  15. (3, 'Deigo Tarzan', 'Diego', 'tarzan'),
  16. (5, 'ecg', 'dsf', 'sdf');
Next let’s proceed, double click the “btngo” button and add the following code: 
  1. Dim table As New DataTable
  2.  
  3.         Try
  4.             'Gets or sets an SQL statement or stored procedure used to select records in the database.
  5.             With cmd
  6.                 .Connection = con
  7.                 .CommandText = "SELECT * from users where `fullname`='" & ComboBox1.SelectedValue & "';"
  8.             End With
  9.             da.SelectCommand = cmd
  10.             da.Fill(table)
  11.             'it gets the data from specific column and fill it into textbox
  12.             txtuser.Text = table.Rows(0).Item(2)
  13.             txtpass.Text = table.Rows(0).Item(3)
  14.  
  15.             da.Dispose()
  16.  
  17.         Catch ex As Exception
  18.             MsgBox(ex.Message)
  19.  
  20.         End Try
And all the code in this application is should look like as shown below. 
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3.     Dim con As New MySqlConnection
  4.     'MySqlCommand It represents a SQL statement to execute against a MySQL Database
  5.     Dim cmd As New MySqlCommand
  6.     'Represents a set of data commands and a database connection that 
  7.     'are used to fill a dataset and update a MySQL database. This class cannot be inherited.
  8.     Dim da As New MySqlDataAdapter
  9.  
  10.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  11.         con.ConnectionString = ("server=localhost;user id=root;password=;database=test")
  12.         Try
  13.             'we open Connection
  14.             con.Open()
  15.  
  16.             With cmd
  17.                 .Connection = con
  18.                 .CommandText = "SELECT `fullname` from users;"
  19.             End With
  20.             'declare dt as new datatable
  21.             Dim dt As New DataTable
  22.  
  23.             With ComboBox1
  24.                 da.SelectCommand = cmd
  25.                 'it fills the da values into dt
  26.                 da.Fill(dt)
  27.                 'dt provides the data surce of combobox
  28.                 .DataSource = dt
  29.                 'specify the what to display
  30.                 .DisplayMember = "fullname"
  31.                 'and the value
  32.                 .ValueMember = "fullname"
  33.  
  34.             End With
  35.  
  36.  
  37.         Catch ex As Exception
  38.             MsgBox(ex.Message)
  39.         End Try
  40.         con.Close()
  41.     End Sub
  42.  
  43.     Private Sub btngo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
  44.         Dim table As New DataTable
  45.  
  46.         Try
  47.             'Gets or sets an SQL statement or stored procedure used to select records in the database.
  48.             With cmd
  49.                 .Connection = con
  50.                 .CommandText = "SELECT * from users where `fullname`='" & ComboBox1.SelectedValue & "';"
  51.             End With
  52.             da.SelectCommand = cmd
  53.             da.Fill(table)
  54.             'it gets the data from specific column and fill it into textbox
  55.             txtuser.Text = table.Rows(0).Item(2)
  56.             txtpass.Text = table.Rows(0).Item(3)
  57.  
  58.             da.Dispose()
  59.  
  60.         Catch ex As Exception
  61.             MsgBox(ex.Message)
  62.  
  63.         End Try
  64.  
  65.     End Sub
  66. End Class
Then, to test this program just press “F5”.

Tags

Comments

Submitted bywawi (not verified)on Fri, 03/21/2014 - 22:28

Private Sub Form10_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load con.ConnectionString = ("Server=localhost;Database=einventorysystem;Uid=eisystem;password=password") Try con.Open() With cmd .Connection = con .CommandText = "SELECT `inventory_name` from inventory;" End With 'declare dt as new datatable Dim dt As New DataTable With ComboBox1 da.SelectCommand = cmd 'it fills the da values into dt da.Fill(dt) 'dt provides the data surce of combobox .DataSource = dt 'specify the what to display .DisplayMember = "inventory_name" 'and the value .ValueMember = "inventory_name" End With Catch ex As Exception MsgBox(ex.Message) End Try con.Close() End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim table As New DataTable Try con.Open() 'Gets or sets an SQL statement or stored procedure used to select records in the database. With cmd .Connection = con .CommandText = "SELECT * from inventory where `inventory_name`='" & ComboBox1.SelectedValue & "';" End With da.SelectCommand = cmd da.Fill(table) 'it gets the data from specific column and fill it into textbox TextBox1.Text = table.Rows(0).Item(2) da.Dispose() Catch ex As Exception MsgBox(ex.Message) End Try con.Close() End Sub End Class
Submitted byJustABystander (not verified)on Sat, 07/05/2014 - 16:02

it cant fill my textboxes here is the name of my texbox txtid txtname txtsec txtcour im stuck in here

Add new comment

About text formats