How to display all tables in database using vb.net with mySQL xampp?-CodePudding

I am new with mySQL with xampp and VB.net connection and i try to research my question but all of them i cant get it could you please explain and correct my code down below

Try
    openCon() ''connection 

    mysqlCommand.Connection = con
    mysqlCommand.CommandText = "SHOW TABLES;"
    mysqlAdapter.SelectCommand = mysqlCommand

    data.Clear() ''dataset
    mysqlAdapter.Fill(data)

    con.Close()

    ListBox1.DataSource = data.Tables(0)
Catch ex As Exception
    MessageBox.Show(ex.ToString)
End Try

i get nothing when im run this code.

CodePudding user response：

Your code to retrieve the table names is correct. The error is in the assignement to the ListBox datasource without specifying which column of Table(0) should be used to display the lines in the ListBox.
Without setting the DisplayMember property to the name of the column, the Listbox could only display the name of the object that you bind. This object is DataRowView.

The command SHOW TABLES when used to fill a DataAdapter creates a table with a single column and the name of this column is Tables_of_<databasename> but you can also use an indexing approach without giving the exact name of the column

Try
    openCon() 

    mysqlCommand.Connection = con
    mysqlCommand.CommandText = "SHOW TABLES;"
    mysqlAdapter.SelectCommand = mysqlCommand

    data.Clear() 
    mysqlAdapter.Fill(data)

    con.Close()

    '' Use whatever is the name of the first column in table returned
    ListBox1.DisplayMember = data.Tables(0).Columns(0).ColumnName
    ListBox1.DataSource = data.Tables(0)

Catch ex As Exception
    MessageBox.Show(ex.ToString)
End Try

To complete my answer I suggest you to avoid using global variables for the connection, commands and dataadapters. You could only find yourself in trouble when you forget to clear the previous usage of these objects. And the connection is absolutely not the right type of object to keep global. Use the Using statement to create a local connection and destroy it when done.

Finally, remember that SHOW TABLES is a command understood only by MySql. If you ever need to use this same code with a different database you should change that line and use the more standardized query

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @yourDBNameParameter