Member Avatar for sumas

Hello , I am new at programming, and I need help with a simple VB.NET application I'm working on.

The trouble I'm having is, I need to populate a combobox with all the table names from one specific database, so that the user can select which database table to work with. once i select specific table from combobox.. the fields of table shd disply in list box.. how can i do this sir...??plz help me to solve this

  • 5 Contributors
  • 4 Replies
  • 2K Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by djjeavons

Dani AI

Generated

Nice starter task, . Building on ’s query idea and ’s Access example, here’s a clean VB.NET approach specifically for MySQL using the MySQL ADO.NET provider (MySql.Data). It fills a ComboBox with base tables from one schema and, when a table is selected, loads that table’s column names into a ListBox. This avoids OleDb and uses parameterized queries.

' Requires MySql.Data (Connector/NET). Install via NuGet.
Imports MySql.Data.MySqlClient

Private Const ConnStr As String =
    "Server=localhost;Database=yourdb;Uid=youruser;Pwd=yourpass;SslMode=Preferred;"

Private Sub Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    LoadTables()
End Sub

Private Sub LoadTables()
    cboTables.Items.Clear()
    Using conn As New MySqlConnection(ConnStr)
        conn.Open()
        Using cmd As New MySqlCommand(
            "SELECT TABLE_NAME
               FROM INFORMATION_SCHEMA.TABLES
              WHERE TABLE_SCHEMA=@db AND TABLE_TYPE='BASE TABLE'
              ORDER BY TABLE_NAME;", conn)
            cmd.Parameters.AddWithValue("@db", conn.Database)
            Using rdr = cmd.ExecuteReader()
                While rdr.Read()
                    cboTables.Items.Add(rdr.GetString(0))
                End While
            End Using
        End Using
    End Using
End Sub

Private Sub cboTables_SelectedIndexChanged(sender As Object, e As EventArgs) _
    Handles cboTables.SelectedIndexChanged

    lstFields.Items.Clear()
    Dim tbl = CStr(cboTables.SelectedItem)
    Using conn As New MySqlConnection(ConnStr)
        conn.Open()
        Using cmd As New MySqlCommand(
            "SELECT COLUMN_NAME
               FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_SCHEMA=@db AND TABLE_NAME=@tbl
              ORDER BY ORDINAL_POSITION;", conn)
            cmd.Parameters.AddWithValue("@db", conn.Database)
            cmd.Parameters.AddWithValue("@tbl", tbl)
            Using rdr = cmd.ExecuteReader()
                While rdr.Read()
                    lstFields.Items.Add(rdr.GetString(0))
                End While
            End Using
        End Using
    End Using
End Sub

Tips:

  • ASP.NET Web Forms: call LoadTables() in Page_Load inside If Not IsPostBack, set cboTables.AutoPostBack = True, and handle SelectedIndexChanged.
  • Want views too? Drop the TABLE_TYPE='BASE TABLE' filter or change it to 'VIEW'.
  • As noted, GetSchema is another option, but provider details vary; the queries above are predictable and easy to debug.

Recommended Answers

All 4 Replies

Member Avatar for Santanu.Das

Welcome sumas.
Members are here not to do your home work they are to help you. Please post your codes how far you did.

Member Avatar for Reverend Jim

To get the table names depends on your database. For MSSQL you can do

SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'

or

SELECT name from sys.tables

I think for MySQL it is

SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbName'
Member Avatar for Sugmuffen

As I understand it, you're wondering how the actual .net code looks like? You're also not specifying what database you're using, but in my example below, I'm assuming Access, database located in c:\temp..

To have the combo box populated (cboTables), I would put the following code in the forms Load event (no fault handling, but you will need to insert that too, ie if the database does not exist etc) :

'Create a connection object using OleDB
Dim conn As New OleDb.OleDbConnection
'Create a connection string for an Access database
Dim strConnectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.accdb"

'Attach the connection string to the connection object
conn.ConnectionString = strConnectionString
'Open the connection
conn.Open()

'Create a DataTable object for holding database information
Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

'Loop through the rows in the databases schema and retrieve table names and add them to the combo box
For Each dr As DataRow In schemaTable.Rows
   cboTables.Items.Add(dr.Item("TABLE_NAME"))
Next
commented: Good call on using this type of method. +3
Member Avatar for djjeavons

Hi

In addition to Sugmuffen's recommendation of using GetOleDbSchemaTable there is also the GetSchema method which works similarly and allows you to get all kinds of information about your tables, columns, indexes and views. The GetSchema method is part of the System.Data.Common.DBConnection object so is available for both OleDbConnection (Access) and SqlDbConnection (SQL Server) and I would assume also MySql if it inherits from DBConnection.

I wrote a on this which you may find useful. It demonstrates using the GetSchema method for OleDb so you can pass it any OleDb database (Access, Excel) and it will display, in a TreeView, all Tables, Columns, Indexes and Views. It uses classes to store the details so it would be fairly straightforward to take the code in the tutorial and use it in your project to populate your combo box with tables and listbox with fields for the selected table.

HTH

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.