VB.NET populating multiple datagriviews with MYSQL

Question

Ihave a form with 4 tabs, each containing a datagridview. i want to populate each of these grids with different tables from my database, but the results of the queries keep on accumulating, until the final table contains the reults of 4 queries. Here's my code:

    Try
        mysqlconn.Open()
        Dim query As String
        query = "SELECT* from venue"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset)
        bsource.DataSource = dbdataset
        DataGridView1.DataSource = bsource
        SDA.Update(dbdataset)

        query = "SELECT* FROM Decoration"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset)
        bsource.DataSource = dbdataset
        DataGridView2.DataSource = bsource
        SDA.Update(dbdataset)

        query = "SELECT* FROM food_drink"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset)
        bsource.DataSource = dbdataset
        DataGridView3.DataSource = bsource
        SDA.Update(dbdataset)

        query = "SELECT* FROM Entertainment"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset)
        bsource.DataSource = dbdataset
        DataGridView4.DataSource = bsource

        mysqlconn.Close()
    Catch ex As MySqlException
        MsgBox(ex.Message)
    Finally
        mysqlconn.Dispose()
    End Try

When i change it so that each query has its own datatable(dbdataset) i just get all datagridviews displaying the results of the final query.

Try
        mysqlconn.Open()
        Dim query As String
        query = "SELECT* from venue"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset1)
        bsource.DataSource = dbdataset1
        DataGridView1.DataSource = bsource
        SDA.Update(dbdataset1)

        query = "SELECT* FROM Decoration"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset2)
        bsource.DataSource = dbdataset2
        DataGridView2.DataSource = bsource
        SDA.Update(dbdataset2)

        query = "SELECT* FROM food_drink"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset3)
        bsource.DataSource = dbdataset3
        DataGridView3.DataSource = bsource
        SDA.Update(dbdataset3)

        query = "SELECT* FROM Entertainment"
        command = New MySqlCommand(query, mysqlconn)

        SDA.SelectCommand = command
        SDA.Fill(dbdataset4)
        bsource.DataSource = dbdataset4
        DataGridView4.DataSource = bsource
        SDA.Update(dbdataset4)

        mysqlconn.Close()
    Catch ex As MySqlException
        MsgBox(ex.Message)
    Finally
        mysqlconn.Dispose()
    End Try

thats because you keep adding the query results to the same dataset (dbdataset). you probably want to put the results each in a new datatable and bind each to its DGV — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Mar 7, 2015 at 13:30

Crowcoder · Accepted Answer · 2015-03-07 15:03:38Z

1

you can shrink that code way down. Use multiple queries in the command by separating them with semi-colon and each will load into a DataTable. Then bind your grids to the correct datatable:

Try
    mysqlconn.Open()
    Dim query As String
    query = "SELECT* from venue;SELECT* FROM Decoration;SELECT* FROM food_drink;SELECT* FROM Entertainment;"
    SDA.SelectCommand = New MySqlCommand(query, mysqlconn)
    SDA.Fill(dbdataset)

    DataGridView1.DataSource = dbdataset.Tables(0)
    DataGridView2.DataSource = dbdataset.Tables(1)
    DataGridView3.DataSource = dbdataset.Tables(2)
    DataGridView4.DataSource = dbdataset.Tables(3)

    mysqlconn.Close()
Catch ex As MySqlException
    MsgBox(ex.Message)
Finally
    mysqlconn.Dispose()
End Try

You could also create 4 bindingsources and bind to those instead of directly to the Tables. Also you should Dispose of the connection and command.

answered Mar 7, 2015 at 15:03

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

chris_techno25 Over a year ago

Ahhh, you beat me to it. I was going to suggest the dataset Tables technique. Haha. Anyhow, no need for me to answer this. Gonna upvote your answer.

aLeXaNdRa08 Over a year ago

Where does the 'dbdataset.tables(0)' and so on, come from? When i type in the code, i get the message "Tables is not a member of system.data.datatable".

Crowcoder Over a year ago

I inferred that your dbdataset variable was a DataSet, not a DataTable. Make dbdataset a DataSet or make a new one. But the code I have shown assumes you are filling a DataSet with tables, not filling one DataTable at a time.

Collectives™ on Stack Overflow

VB.NET populating multiple datagriviews with MYSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related