0

I have a project for school wherein I need to do the above problem, however, our Prog teacher didn't properly fully teach us about mysql and binding it to VB.Net. So I am at a complete loss right now.

I am using Visual Basic.Net on Studio 2019.

epoch32
  • 13
  • 2
  • Query `INFORMATION_SCHEMA.SCHEMATA` Table. – Akina Nov 27 '20 at 11:21
  • Which contents of the database should be shown? – Alex B. Nov 27 '20 at 11:24
  • after the database is selected from the combobox, the columns and tables from the mySQL database will be displayed in a datagridview table – epoch32 Nov 27 '20 at 11:37
  • Check this [answer](https://stackoverflow.com/questions/8334493/get-table-names-using-select-statement-in-mysql) – Alex B. Nov 27 '20 at 12:31
  • It would help if you knew what RDBMS you were using. The title says SQL Server but the question and the tag says MySQL. Which is it? Please edit your question accordingly. – jmcilhinney Nov 27 '20 at 13:32
  • Also, please don't ask your question in the title and then refer us to that in the question itself. Write the question first and include ALL the relevant information in it. When you're done, write a title that summarises the issue. – jmcilhinney Nov 27 '20 at 13:32
  • Oops sorry, its my first time asking here, my bad. I'm Using mySQL thru Xampp – epoch32 Nov 27 '20 at 13:56

2 Answers2

0

if you are sure about that the server is mySQL you can add the MySql.Data.dll to your project first (MySQL Connector/NET), here is some code to guide you in your project:

Imports MySql.Data.MySqlClient 
Public Class Form2
        Private Sub Test()
        Dim myConnection As MySqlConnection = New MySqlConnection("server=localhost; user id=root; password=yourpassword; database=yourDB; pooling=false;")
        Dim strSQL As String = "SELECT * FROM my_users;"
        Dim myDataAdapter As MySqlDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
        Dim myDataSet As DataSet = New DataSet()
        myDataAdapter.Fill(myDataSet, "my_users")
        MySQLDataGrid.DataSource = myDataSet
        MySQLDataGrid.DataBind()
        End Sub
End Class
B.S.
  • 668
  • 1
  • 5
  • 15
  • Thanks! But unfortunately I cannot hardcode the database as I need to select it from my form. Then afterwards, another combobox will be made to list the table to be displayed in the DataGridView – epoch32 Nov 27 '20 at 13:12
0

Actually this is quite easy in MySql. Normally a connection string would include a database= clause but is this case you want a list of all databases.

My string looks like

Private ConStr As String = "server=localhost;user id=xxx;password=xxx"

Of course you would reference your server. The user id and password would probably need an account with admin privileges.

Private Sub DisplayDatabases()
    Dim dt As New DataTable
    Using cn As New MySqlConnection(ConStr),
            cmd As New MySqlCommand("show databases", cn)
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    ComboBox1.DisplayMember = "Database"
    ComboBox1.DataSource = dt
End Sub

Private Sub DisplayTables(DB As String)
    Dim dt As New DataTable
    Using cn As New MySqlConnection(ConStr),
            cmd As New MySqlCommand($"use {DB}; show tables", cn)
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    DataGridView1.DataSource = dt
End Sub

Usage:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    DisplayDatabases()
End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    DisplayTables(ComboBox1.Text)
End Sub

I certainly wouldn't want to be displaying this much info to just any user.

Mary
  • 14,926
  • 3
  • 18
  • 27