How to Fill DataGridView with Data Based on ComboBox

Submitted by janobe on Saturday, December 28, 2013 - 18:08.
Today, I will teach how to fill the data into a Combobox in the DataGridView Using Visual Basic 2008 and MySQL Database. This tutorial will show you how to retrieve the list of records that you have saved in MySQL Database into a ComboBox in the DataGridView. Let’s Begin: First, create a table in MySQL Database named "member"
  1. CREATE TABLE IF NOT EXISTS `names` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` text NOT NULL,
  4.   PRIMARY KEY (`id`)
Then, insert all the records in the table that you have created.
  1. INSERT INTO `names` (`id`, `name`) VALUES
  2. (1, 'Janno Palacios'),
  3. (2, 'Gerlie joy Castaños'),
  4. (3, 'John Paul Arroz'),
  5. (4, 'michael alejano'),
  6. (5, 'Jeszel R. alvarez'),
  7. (6, 'Mary Angelie I. Sitjar'),
  8. (7, 'Raffy Buendia'),
  9. (8, 'Jimmelyn Espraguera'),
  10. (9, 'joean mar m genit'),
  11. (10, 'Justine Ann Gomez'),
  12. (11, 'Amy Rose'),
  13. (12, 'Dolan Cuevas'),
  14. (13, 'clefhord john S macumao'),
  15. (14, 'Michael Ian C. Ruiz'),
  16. (15, 'lourdmaro'),
  17. (16, 'oscar neil s ilao'),
  18. (17, 'lyn rose'),
  19. (18, 'Ruselo P. Flores'),
  20. (19, 'Dennis Michael U. Berzuela'),
  21. (20, 'Jasper Gabat'),
  22. (21, 'Shanlee Marie Bongaita'),
  23. (22, 'ritchelle gatoc'),
  24. (23, 'Sherlyn F. Dolfo'),
  25. (24, 'july ganza'),
  26. (25, 'lanie pescasiosa'),
  27. (26, 'Marjorie'),
  28. (27, 'Giv Hamsleigh B. Javellana'),
  29. (28, 'Joshua J. Labrador'),
  30. (29, 'VANESSA G. BAÑEZ'),
  31. (30, 'Margie Rose Tondo'),
  32. (31, 'Gergil Luz Argamaso'),
  33. (32, 'Cheryl Tabasin'),
  34. (33, 'MYLIN I. MONTILLA'),
  35. (34, 'charkien palmos'),
  36. (35, 'Kim Ruel N Lo-on'),
  37. (36, 'james mesas'),
  38. (37, 'mario maravilla'),
  39. (38, 'janry apatan'),
  40. (39, 'janney joy kim')
Open the Visual Basic 2008, create a Project and set up your Form just like this. First Form Double click the Form, then set up your connection in MySQL Database and declare all the variables and classes that you needed above the Form_Load.
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3.     'set up the MySQL Connection
  4.     Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=member")
  5.     'a set of commands in MySQL
  6.     Dim cmd As New MySqlCommand
  7.     'a Bridge between a database and datatable for retrieving and saving data.
  8.     Dim da As New MySqlDataAdapter
  9.     'a specific table in the database
  10.     Dim dt As New DataTable
  11.     'declare variable as string
  12.     Dim sql As String
  13.  
  14. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  15.  
  16.     End Sub
  17. End Class
After that, inside the Form_Load, do the following codes for retrieving the list of records in MySQL Database into a ComboBox in the DataGridView and it will look like this.
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.         Try
  3.             'opening connection
  4.             con.Open()
  5.             'stores your query in the varialble(sql)
  6.             sql = "select name from names"
  7.             'set your MySQL COMMANDS for holding the data to be executed
  8.             With cmd
  9.                 .Connection = con
  10.                 .CommandText = sql
  11.             End With
  12.             'filling  data in the table
  13.             da = New MySqlDataAdapter(sql, con)
  14.             dt = New DataTable
  15.             da.Fill(dt)
  16.  
  17.             'declaring variable that set as combobox in the column on the datagridview.
  18.             Dim combo As New DataGridViewComboBoxColumn
  19.             'declaring variable that represents the row in the datatable
  20.             Dim row As DataRow
  21.  
  22.             'adding the data in a combobox
  23.             With combo
  24.                 'for each row in the datatable
  25.                 For Each row In dt.Rows
  26.                     'adding a list of records in the combobox
  27.                     .Items.Add(row.Item("name").ToString)
  28.                     'name of combobox
  29.                     .Name = "Name"
  30.                 Next
  31.             End With
  32.             'add the combobox in the column in the datagridview
  33.             DataGridView1.Columns.Add(combo)
  34.  
  35.         Catch ex As Exception
  36.             MsgBox(ex.Message)
  37.         End Try
  38.         da.Dispose()
  39.         'Close the connection
  40.         con.Close()
  41. End Sub
Complete Source Code is included.
Tags
Filling
combobox
visual basic
MYSQL
database

Comments

Submitted by Gem325 (not verified) on Fri, 09/08/2017 - 09:27

Hi Sir, Can you help me about retrieving data from mysql database based on values from combo box in vb.net. And loop until all items in combo box has been used to query. Thanks

Add new comment

About text formats