Add Boolean column as to bound SQL result for CheckBoxColumn in DataGridView

Question

I have a CheckBoxColumn in a DataGridView, and I am trying to fill the CheckBoxColumn with an unchecked box every time my SQLReader.Read() is true (If SqlReader.Read is called 10 times in the loop, there should be 10 checkboxes). This is the code I have so far which is wrong:

DataGridView1.DataSource = sqlReader

Using sqlReader As SqlDataReader = sqlCommand.ExecuteReader
    If sqlReader.HasRows Then
        While sqlReader.Read
            DataGridView1.Refresh()
        End While
    End If
End Using

I have found a way to add the checkboxes without databinding the sqlReader to a column, but I just want to know if it is possible to do it this way. I can't figure out how to bind the SqlReader to a specific column. Thank you.

Edit: After looking into it more, I think I need separate checkbox and textbox columns

Try using a DataTable instead of a reader. A bit data type from the DB will result in a checkbox. Or use the reader loop to parse the data into an array and add the array as a row. — rheitzman
– rheitzman, Commented Sep 8, 2015 at 19:25
what does SQLReader.Read() represent? If it is just that the action took place, it will alwsys be true since it is inside sqlReader.HasRows (assuming the query is not duplicated) — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Sep 8, 2015 at 22:25
by the way, SQLReader.Read() is reading in the name of the databases — cjw
– cjw, Commented Sep 9, 2015 at 15:26

Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-01-24 14:21:50Z

1

Its not clear what, if anything, SQLReader.Read() has to do with the check column, but you can add "virtual" columns to the Datasource along with the real data you are reading.

Dim sql As String = "SELECT Name, Species, Gender, False As Picker FROM Animal"

Using dbcon As OleDbConnection = ...
    Using cmd As New OleDbCommand(sql, dbcon)
        dbcon.Open()

        dtAnimals = New DataTable()
        Using da As New OleDbDataAdapter(cmd)
            da.Fill(dtAnimals)
        End Using

    End Using
End Using

dgv.AutoGenerateColumns = False
dgv.DataSource = dtCat

False As Picker adds a Bool column to the result set which isn't actually in the DataBase. Rather than a literal False you could also use an expression. The DGV has columns added manually:

It adds Picker to each row. The nice thing is that the underlying field is in the same container as the DB Data, not just in the DGV.

Since part of the question sounds like the query is meant to add new rows, there is one small change to accumulate results.

Using cmd As New OleDbCommand(sql, dbcon)
    dbcon.Open()

    'dtAnimals = New DataTable()           ' use the old one!
    Using da As New OleDbDataAdapter(cmd)
        da.Fill(dtAnimals)
    End Using

End Using

By not creating a New DataTable, the new data will be added to the existing result set. Rather than a Picker/bool column, you could add a SequenceID instead of the bool if you needed to track/groups rows.

edited Jan 24, 2016 at 14:21

answered Sep 8, 2015 at 23:26

Ňɏssa Pøngjǣrdenlarp

39k12 gold badges66 silver badges194 bronze badges

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

2 Comments

cjw Over a year ago

The virtual boolean columns are exactly what I need to use

Ňɏssa Pøngjǣrdenlarp Over a year ago

that thing could also be a counter, sequencer or grouping value.

Collectives™ on Stack Overflow

Add Boolean column as to bound SQL result for CheckBoxColumn in DataGridView

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related