CODEWITHSUNDEEP
mysqlvb.net
aLeXaNdRa08
aLeXaNdRa08

Reputation: 29

VB.NET populating multiple datagriviews with MYSQL

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

Upvotes: 0

Views: 91

Answers (1)

Crowcoder
Crowcoder

Reputation: 11514

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.

Upvotes: 1

Related Questions