Setting Column Type as a Checkbox in a dataGridView

[Halliarse]

Evening All

I'm having a headache with a dataGridview!

I have a blank datagridview and I autogenratecolumns set to true.

My dataset is 3 columns, 2 text columns and the 3rd returns either True or False.

My code for populating the dataGridView is below....what I need is for column 2, headed Monthly, to be a checkbox column, but I cannot work out how to do this....HELP!!

        Dim SQLSelect As New System.Text.StringBuilder

        SQLSelect.Append("Select [Cust Ref], [Cust Delivery name], case when [Monthly ID] > 0 then 'True' Else 'False' End ")
        SQLSelect.Append("From customers ")
        SQLSelect.Append("Left Join Monthly On [Monthly Ref] = [Cust Ref] ")
        SQLSelect.Append("where [Cust Ref] <> '' ")
        SQLSelect.Append("Order By [Cust Ref]")

        ' Load Monthly Accounts

        dgvMonthlyAccounts.Columns.Clear()

        dgvMonthlyAccounts.DefaultCellStyle.ForeColor = Color.Black

        Try
            dgvMonthlyAccounts.AutoGenerateColumns = True

            Using con As New SqlConnection(Main.DWDataConstr)

                Using cmd As New SqlCommand(SQLSelect.ToString)
                    Using sda As New SqlDataAdapter()
                        sda.SelectCommand = cmd

                        cmd.Connection = con
                        con.Open()

                        Using ds As New DataSet
                            sda.Fill(ds)

                            dgvMonthlyAccounts.DataSource = ds.Tables(0)

                        End Using
                    End Using
                End Using
            End Using

            dgvMonthlyAccounts.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders
            dgvMonthlyAccounts.BorderStyle = BorderStyle.Fixed3D

            dgvMonthlyAccounts.RowHeadersVisible = False

            ' Customer Reference
            dgvMonthlyAccounts.Columns(0).Width = 80
            dgvMonthlyAccounts.Columns(0).HeaderCell.Value = "Customer Ref"
            dgvMonthlyAccounts.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
            dgvMonthlyAccounts.Columns(0).ReadOnly = True

            ' Customer Delivery Name
            dgvMonthlyAccounts.Columns(1).Width = 250
            dgvMonthlyAccounts.Columns(1).HeaderCell.Value = "Customer Name"
            dgvMonthlyAccounts.Columns(1).SortMode = DataGridViewColumnSortMode.NotSortable
            dgvMonthlyAccounts.Columns(1).ReadOnly = True

            ' Monthly
            dgvMonthlyAccounts.Columns(2).Width = 55
            dgvMonthlyAccounts.Columns(2).HeaderCell.Value = "Monthly"
            dgvMonthlyAccounts.Columns(2).SortMode = DataGridViewColumnSortMode.NotSortable

            ' Record Updated
            Dim CheckUpdated As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn With {
                .HeaderText = "Monthly",
                .Width = 55
            }
            dgvMonthlyAccounts.Columns.Add(CheckUpdated)
            dgvMonthlyAccounts.Columns(3).Visible = False

        Catch SQLEx_ As SqlException
            MessageBox.Show("Unable to load Delivery Notes, contact Knibbs Support", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try

        dgvMonthlyAccounts.RowsDefaultCellStyle.BackColor = Color.LightBlue
        dgvMonthlyAccounts.AlternatingRowsDefaultCellStyle.BackColor = Color.Silver
        dgvMonthlyAccounts.AllowUserToAddRows = False

Thanks

Steve

 

[Andrzejek]

Would this help: Adding CheckBox to DataGridView in VB.NET

Especially the part of:

Dim chk As New DataGridViewCheckBoxColumn()
DataGridView1.Columns.Add(chk)
chk.HeaderText = "Check Data"
chk.Name = "chk"[blue]
DataGridView1.Rows(2).Cells(3).Value =[/blue][red] True 'or False[/red]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Halliarse]

No, I've tried this!

The SQL statement returns 3 columns of data, the 3rd contains either true or false.

The data is linked to the datagridview as a datasource so rge columns are automatically created, I just need to be able to format the 3rd one as a checkbox

 

[Andrzejek]

I ask because I can see that your Select statement does have [Monthly ID] field, but returns a text of 'True' or 'False', but I don't see anywhere any line of code that states:
[tt]
dgvMonthlyAccounts.Rows(2).Cells(3).Value = ValueFromDB
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Halliarse]

The monthly ID field contains a numerical record ID.

What I'm trying to achieve with the text true or false is to get the 3rd column in the datagridview to be a checkbox that is populated if true. I did get this working previously without converting the Monthly ID to a true or false, but hid that column and added a checkbox column and looped through all the records and set the checkbox accordingly. Unfortunately, the time it takes to populate just that checkbox column is unacceptably slow.

 

[Andrzejek]

Instead of text, try some numerical values.

Give that a go:

...
SQLSelect.Append("Select [Cust Ref], [Cust Delivery name], Case When [Monthly ID] > 0 Then [blue]1[/blue] Else [blue]0[/blue] End [blue]As MonthlyID[/blue] ")
...

You may also try -1 instead of 1 for True, False is always 0

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson