Retrieve Database Tables from MySQL Database using Visual Basic.Net into ComboBox

Submitted by GeePee on Monday, April 13, 2015 - 23:04.
In this tutorial, I’m going to show you how to create an application that will load all the MySQL database and store it into a combobox displaying all the table fields in the datagridview. To start in this program you need to open visual basic and create a new application and name it as “dbcreator01”. Then add some tools from the toolbox to our form, these tools are the following: seven labels, one button, one combobox and a datagridview. Then change some property of the objects and arrange it same as shown below. db_creator This time, we’re going to add functionality to our application. First, we need to create another module and name it as “mySelect” module, add the following code: This code will handle the connections between the visual basic and MySQL database so that we could freely work with our MySQL Database and other functions especially in retrieving of data from a MySQL database in Visual Basic.
  1. Imports MySql.Data.MySqlClient
  2.  
  3. Module mySelect
  4.     Dim cmd As New MySqlCommand
  5.     Dim da As New MySqlDataAdapter
  6.     Public total As Integer
  7.     Dim con As MySqlConnection = jokenconn()
  8.     Public Function jokenconn() As MySqlConnection
  9.         Return New MySqlConnection("server=localhost;user id=root;password=;database='" & Form1.cbdb.SelectedValue & "'")
  10.  
  11.     End Function
  12.     Public Sub findThis(ByVal sql As String)
  13.         Try
  14.             con.Open()
  15.             With cmd
  16.                 .Connection = con
  17.                 .CommandText = sql
  18.             End With
  19.  
  20.         Catch ex As Exception
  21.          
  22.             Form1.Text = ex.Message
  23.  
  24.         End Try
  25.         con.Close()
  26.         da.Dispose()
  27.     End Sub
  28.     Public Sub filltable(ByVal dtgrd As Object)
  29.         Dim publictable As New DataTable
  30.         Dim numrow As Integer
  31.         Try
  32.             da.SelectCommand = cmd
  33.             da.Fill(publictable)
  34.             dtgrd.DataSource = publictable
  35.  
  36.             numrow = publictable.Rows.Count
  37.            
  38.             ' dtgrd.Columns(0).Visible = False
  39.             ' dtgrd.Columns(1).Visible = False
  40.             da.Dispose()
  41.  
  42.         Catch ex As Exception
  43.             MsgBox(ex.Message)
  44.  
  45.         End Try
  46.  
  47.     End Sub
  48.     Public Sub filldbOBJ(ByVal obj As Object)
  49.         Dim publictable As New DataTable
  50.         Try
  51.             da.SelectCommand = cmd
  52.             da.Fill(publictable)
  53.             '  publictable.Rows.Add("Please Select...")
  54.             With obj
  55.                 .DataSource = publictable
  56.                 .DisplayMember = "Database"
  57.                 .ValueMember = "Database"
  58.  
  59.  
  60.  
  61.             End With
  62.             ' dtgrd.Columns(1).Visible = False
  63.             da.Dispose()
  64.  
  65.         Catch ex As Exception
  66.             MsgBox(ex.Message)
  67.  
  68.         End Try
  69.  
  70.     End Sub
  71.     Public Sub filltxtbox(ByVal obj As Object, ByVal obj1 As Object)
  72.         Dim publictable As New DataTable
  73.         Try
  74.             da.SelectCommand = cmd
  75.             da.Fill(publictable)
  76.             obj.text = publictable.Rows(0).Item(1) 'user
  77.             obj1.text = publictable.Rows(0).Item(2) 'server
  78.             'dtgrd.Columns(1).Visible = False
  79.             da.Dispose()
  80.  
  81.         Catch ex As Exception
  82.             MsgBox(ex.Message)
  83.  
  84.         End Try
  85.  
  86.     End Sub
  87.     Public Sub fillcbOBJ(ByVal tableObj As Object, ByVal dbObj As Object)
  88.         Dim publictable As New DataTable
  89.         Try
  90.             da.SelectCommand = cmd
  91.             da.Fill(publictable)
  92.             With tableObj
  93.                 .DataSource = publictable
  94.                 .DisplayMember = "Tables_in_" & dbObj
  95.                 .ValueMember = "Tables_in_" & dbObj
  96.  
  97.             End With
  98.             ' dtgrd.Columns(1).Visible = False
  99.             da.Dispose()
  100.  
  101.         Catch ex As Exception
  102.  
  103.             MsgBox(ex.Message)
  104.  
  105.         End Try
  106.  
  107.     End Sub
  108.     Public Sub fillFieldsOBJ(ByVal obj As Object)
  109.         Dim publictable As New DataTable
  110.         Try
  111.             da.SelectCommand = cmd
  112.             da.Fill(publictable)
  113.             With obj
  114.                 .DataSource = publictable
  115.                
  116.  
  117.             End With
  118.             ' dtgrd.Columns(1).Visible = False
  119.             da.Dispose()
  120.  
  121.         Catch ex As Exception
  122.             MsgBox(ex.Message)
  123.  
  124.         End Try
  125.  
  126.     End Sub
  127.     Public Sub filltblname(ByVal obj As Object, ByVal tblname As String)
  128.         Dim publictable As New DataTable
  129.         Try
  130.             da.SelectCommand = cmd
  131.             da.Fill(publictable)
  132.             With obj
  133.                 .DataSource = publictable
  134.                 .DisplayMember = "Tables_in_" & tblname
  135.                 .ValueMember = "Tables_in_" & tblname
  136.  
  137.             End With
  138.             ' dtgrd.Columns(1).Visible = False
  139.             da.Dispose()
  140.  
  141.         Catch ex As Exception
  142.             MsgBox(ex.Message)
  143.  
  144.         End Try
  145.  
  146.     End Sub
  147.     Public Sub findThisTableCount(ByVal sql As String)
  148.         Try
  149.             con.Open()
  150.             With cmd
  151.                 .Connection = con
  152.                 .CommandText = sql
  153.             End With
  154.  
  155.         Catch ex As Exception
  156.  
  157.             Form1.Text = ex.Message
  158.  
  159.         End Try
  160.         con.Close()
  161.         da.Dispose()
  162.     End Sub
  163.     Public Sub fillNo_tblCount(ByVal obj As Object)
  164.         Dim publictable As New DataTable
  165.         Try
  166.             da.SelectCommand = cmd
  167.             da.Fill(publictable)
  168.             obj.text = publictable.Rows(0).Item(0)
  169.             ' dtgrd.Columns(1).Visible = False
  170.             da.Dispose()
  171.  
  172.         Catch ex As Exception
  173.             MsgBox(ex.Message)
  174.  
  175.         End Try
  176.  
  177.     End Sub
  178.  
  179.  
  180. End Module
Next, double click our form, and add the following code:
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.         'using modules we just simply call this sub routine
  3.         'and state a specific query, this query will show all the database found in Database
  4.         findThis("SHOW DATABASES")
  5.         'list down all the database names into the combobox
  6.         filldbOBJ(cbdb)
  7.     End Sub
Then, double the “btngo”. And add the following code:
  1. Private Sub btngo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
  2.         'it select all the datafrom Processlist
  3.         findThis("SELECT * FROM information_schema.PROCESSLIST")
  4.         'get the USER and the HOST name used
  5.         filltxtbox(lbluser, lblhost)
  6.         'it count all the number of table based on the selected database
  7.         findThisTableCount("SELECT COUNT( * ) FROM(information_schema.tables) WHERE table_schema =  '" & cbdb.Text & "'")
  8.         'display the count of table
  9.         fillNo_tblCount(lblCount)
  10.         'display all the tables from a specific database
  11.         findThis("SHOW TABLES FROM " & cbdb.SelectedValue)
  12.         'and fill it into the datagridview
  13.         fillFieldsOBJ(DataGridView1)
  14.  
  15.     End Sub
Before executing this application, make sure that have installed MySQL database in your machine. If not you can download XAMPP or WAMP and install it to your computer. To test this program, just press “F5”.
Tags
Tables
MySQL Database
visual basic 2008

Add new comment

About text formats
  • 82 views