CODEWITHSUNDEEP
mysqlsql.netvb.net
Jeffrey Lionel van Houdt
Jeffrey Lionel van Houdt

Reputation: 169

How to populate datagridview with already defined columns in VB.Net

I am trying to populate my DataGridView with data located in my MySQL database. Here comes my problem, my columns are already created in the DataGridView.

Instead of placing the data in the correct columns it just creates extra columns with the same names.

I tried changing the designer names of the columns so they match the MySQL ones. This didn't help at all.

So my guess is that I need to fix it with a piece of code but somehow I can't figure out what I need exactly.

MysqlConn.Open()

Query = "select * from offerte.Offerteinhoud where Offertenummer2='" & OfZo.Text & "'"

COMMAND = New MySqlCommand(Query, MysqlConn)
    READER = COMMAND.ExecuteReader
    table.Load(READER)

    With DataGridView1

        .DataSource = table

    End With

MysqlConn.Close()

I think my problem is that I just load the entire database into the datagridview. My MySQL table also includes 2 columns I don't want included in the datagridview.

Edit 2: Solution number 1.

Instead of doing things like this, you can make alias of the column names >which you want to show in your grid like this :

select col1 as [Column 1], col2 as [Column 2], col3 from >offerte.Offerteinhoud ...

And then if you don't want to show column col3, you can hide it using the >below code :

DataGridView1.Columns("col3").Visible = False

This worked like a charm :)

Solution number 2

I changed the DataPropertyName of column to the same name as the MySQL column names. This way it binded the data without a problemen.

And for removing the 2 columns I didn't want to show I removed them using the next code.

DataGridView1.Columns.Remove("offertenummer")
DataGridView1.Columns.Remove("offertenummer2")

I used the 2nd solution in my code so it finaly looks like this.

MysqlConn.Open()

Query = "select * from offerte.Offerteinhoud where Offertenummer2='" & OfZo.Text & "'"

COMMAND = New MySqlCommand(Query, MysqlConn)
    READER = COMMAND.ExecuteReader
    table.Load(READER)

    With DataGridView1

        .DataSource = table

    End With

DataGridView1.Columns.Remove("offertenummer")
DataGridView1.Columns.Remove("offertenummer2")

MysqlConn.Close()

EDIT 3: Solution 3

Finally I ran into some problems because of binding data because of the table.

I edited the code to the following.

        MysqlConn.Open()

        Query = "select * from offerte.Offerteinhoud where Offertenummer2='" & OfZo.Text & "'"

        COMMAND = New MySqlCommand(Query, MysqlConn)
        READER = COMMAND.ExecuteReader
        While READER.Read

            aantal = READER.GetString("Aantal")
            artikelnummer = READER.GetString("Artikelnummer")
            capaciteit = READER.GetString("Capaciteit")
            eenheid = READER.GetString("Eenheid")
            prijs = READER.GetString("Prijs")
            product = READER.GetString("Product")


            DataGridView1.Rows.Add(aantal, artikelnummer, capaciteit, eenheid, prijs, product)

        End While

        MysqlConn.Close()

Because I adjusted the DataPropertyName of the columns everything gets added to the right columns and the DataGridView itself can be edited without having problems with bound data.

I hope this will help someone else in his VB.net journey.

Upvotes: 1

Views: 2216

Answers (1)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Instead of doing things like this, you can make alias of the column names which you want to show in your grid like this :

select col1 as [Column 1], col2 as [Column 2], col3 from offerte.Offerteinhoud ...

And then if you don't want to show column col3, you can hide it using the below code :

DataGridView1.Columns("col3").Visible = False

Upvotes: 1

Related Questions