DataTable problem

[Abomination]

Hello, how come when I run my program, I get an error, I'll high light the error, this is my code

Imports MySql.Data.MySqlClient
Imports System.Data


Public Class Form2

    Private myConnString As String



    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As MySqlConnection
        Dim myData As New DataTable
        Dim myAdapter As New MySqlDataAdapter
        Dim myCommand As New MySqlCommand
        Dim SQL As String


        conn = New MySqlConnection
        conn.ConnectionString = "server=" & Form1.TextBox1.Text & ";" _
        & "user id=" & Form1.TextBox2.Text & ";" _
        & "password=" & Form1.TextBox3.Text & ";" _
        & "database=" & Form1.TextBox4.Text & ";"

        Try
            conn.Open()
            Me.Label1.Text = "Connected To: " & Form1.TextBox4.Text
            myCommand.Connection = conn
            myCommand.CommandText = SQL

            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(myData)   < ---------- Gives me an error here     " The CommandText property has not been properly initialized. "

            DataGridView1.DataSource = myData


        Catch myerror As MySqlException
            Me.Label1.Text = "Connection to, " & Form1.TextBox4.Text & " has been lost."



        End Try







    End Sub
End Class

[jmcilhinney]

Nope, not a DataTable problem. Nothing to do with your DataTable in fact.

Here's the important parts of your code:

Dim myAdapter As New MySqlDataAdapter
Dim myCommand As New MySqlCommand
Dim SQL As String

'...

myCommand.CommandText = SQL

myAdapter.SelectCommand = myCommand
myAdapter.Fill(myData)

At no point do you ever assign a string containing any SQL code (or anything else for that matter) to your SQL variable. As a result the CommandText property of your myCommand object is Nothing.

[Abomination]

Imports MySql.Data.MySqlClient
Imports System.Data


Public Class Form2

    Private myConnString As String



    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As MySqlConnection
        Dim myData As New DataTable
        Dim myAdapter As New MySqlDataAdapter
        Dim myCommand As New MySqlCommand
        


        conn = New MySqlConnection
        conn.ConnectionString = "server=" & Form1.TextBox1.Text & ";" _
        & "user id=" & Form1.TextBox2.Text & ";" _
        & "password=" & Form1.TextBox3.Text & ";" _
        & "database=" & Form1.TextBox4.Text & ";"

        Try
            conn.Open()
            Me.Label1.Text = "Connected To: " & Form1.TextBox4.Text
            myCommand.Connection = conn
            

            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(myData)

            DataGridView1.DataSource = myData


        Catch myerror As MySqlException
            Me.Label1.Text = "Connection to, " & Form1.TextBox4.Text & " has been lost."



        End Try







    End Sub
End Class

I took out the SQL or w/e it was im kind of following a tutorial

http://www.vbmysql.com/articles/vbne...utorial-part-1


But anyways im still getting the error and I have no idea what it means or how to fix it. Thanks!

[jmcilhinney]

I already told you what it means:

At no point do you ever assign a string containing any SQL code (or anything else for that matter) to your SQL variable. As a result the CommandText property of your myCommand object is Nothing.

How is your MySqlCommand supposed to get data from the database if you haven't provided and SQL code to tell what to get? This is from one of the posts near the bottom of that very tutorial you linked to:

Dim cmdString As String = “SELECT * FROM 34_invoice_products; SELECT * from 34_line_items”

Dim dataAdapter As New MySqlDataAdapter(cmdString, conn)

[Abomination]

What would be the command to population a ListBox with the names of the tables that are in that Database?

[Abomination]

Updated post above ^^^

[jmcilhinney]

I've never worked with MySQL before but it should be the same, or nearly the same, as working with SQL Server in this regard. You should create a MySqlConnection and use its GetSchema method, which returns a DataTable containing schema information.

First up, call it with no arguments and examine the results. The easiest way to do that is to simply bind it to a DataGridView. It should list all the collections you can query with GetSchema, plus a bit of information about them. There will most likely be a collection named "TABLES". Now, you can call GetSchema and pass a String argument containing any one of those names to get schema information.

If you pass "TABLES" (or whatever is correct for MySQL) then you'll get a DataTable returned with schema information specifically about tables. You should bind that to your DataGridView and see what it contains. One of the columns will be named "TABLE_NAME" or the like and it will contain the names of all the tables. Now you have all the information you need.

So, in your application, you can now create a MySqlConnection, call GetSchema for the "TABLES" collection and bind it to your ListBox, displaying the table names:

Using connection As New MySqlConnection("connection string here")
    connection.Open()
    Me.ListBox1.DisplayMember = "TABLE_NAME"
    Me.ListBox1.DataSource = connection.GetSchema("TABLES")
End Using

As I said, you'll have to do a bit of work for yourself to see what strings you need to use for the collection name and the column name.