Member Avatar for cjoyce
cjoyce 0 Newbie Poster

hi guys... i can connect now to the mysql database and is able to display the database names (because i have 3 databases) in a combo box.. i was stuck up in displaying tables from mysql in a listview..
please help me solve this.. here are my codes:

Public Sub OpenDatabase(ByVal dbName As String)
        ' Try
        Sql = "Show tables from " & dbName
        myCommand.Connection = conn
        myCommand.CommandText = Sql
        myAdapter.SelectCommand = myCommand
        Dim bld As MySqlCommandBuilder = New MySqlCommandBuilder(myAdapter)
        myAdapter.Fill(dataset, dbName)
        Form8.ListView1.Items(0).Text = dataset.Tables.ToString

        MsgBox("...")

        '  Catch
        '    MsgBox("error")
        ' End Try

    End Sub

what displays in the list view is this: System.Data.DataTableCollection

Edited by peter_budo because: Keep It Clear - Do wrap your programming code blocks within [code] ... [/code] tags
  • 1 Contributor
  • 0 Replies
  • 418 Views

Dani AI

Generated

In ’s snippet the line that assigns dataset.Tables.ToString to a ListView item is the root cause: calling ToString() on a DataTableCollection returns its type name (System.Data.DataTableCollection), not the rows or column values from the query. The result of SHOW TABLES places each table name in the first column of each result row, so the correct approach is to read that first-column value and add it to the ListView (either by iterating a DataTable or by using a MySqlDataReader).

Example (preferred, fast and simple) — use a MySqlDataReader and add the first column of each row to the ListView:

' ensure MySql.Data is referenced and conn is an open MySqlConnection
If conn.State <> ConnectionState.Open Then conn.Open()

Using cmd As New MySql.Data.MySqlClient.MySqlCommand("SHOW TABLES", conn)
    Using rdr As MySql.Data.MySqlClient.MySqlDataReader = cmd.ExecuteReader()
        Form8.ListView1.BeginUpdate()
        Form8.ListView1.Items.Clear()
        While rdr.Read()
            Form8.ListView1.Items.Add(New ListViewItem(rdr.GetString(0)))
        End While
        Form8.ListView1.EndUpdate()
    End Using
End Using

Alternative (if already using adapter): fill a local DataTable and iterate its rows:

Dim dt As New DataTable()
Using cmd As New MySql.Data.MySqlClient.MySqlCommand("SHOW TABLES FROM `" & dbName & "`", conn)
    Using adapter As New MySql.Data.MySqlClient.MySqlDataAdapter(cmd)
        adapter.Fill(dt)
    End Using
End Using

Form8.ListView1.Items.Clear()
For Each r As DataRow In dt.Rows
    Form8.ListView1.Items.Add(New ListViewItem(r(0).ToString()))
Next

Notes and troubleshooting: clear the ListView before adding items, use BeginUpdate/EndUpdate for bulk adds, add a ColumnHeader when View = View.Details, and ensure the code runs on the UI thread (use Invoke if running from a background thread). The returned column name can be Tables_in_<dbname>; always read the first column (r(0) or rdr.GetString(0)) rather than relying on a column name.

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.