How to change Yes/No (True/False) results from SQL query to checkboxes in DataGrid

[ads248]

Hi

I've populates a DataGridView from the results of a SQL query programmatically, so I haven't had to define the number of columns, etc.

Just before I display it, I'd like to change the columns with Yes/No in them with checkboxes.

Any ideas how I can do this programmatically?

Regards

Andrew

[ashishparihar]

Hi
in your presentation layer logic class you can manage it
ex.
i have a class student with data member
1. string StudentName
2. int Age
3. bool IsDelete

so on rowdatabound event on your grid you can acess that column by column number(in above should be 3) and check that it contain true or false and according to that assign yes or no vales to that column

[ads248]

Hi

Thanks for your response, but I think you misunderstand my requirement..

I have it displaying yes or no as it is passed in 1 or 0 from the sql query.

What I want to do is change the yes/nos in the resulting DataGridView column to checkboxes i.e. checked for the yes & unchecked for the nos.

Is there an attribute to the column that will do this or convert it.

i.e Column(3).????????????

Regards

Andrew

[ads248]

Any ideas guys, as I'm struggling to find anything on this?

[manal]

i hope this artical will help you

[ads248]

Hi

Thanks.

I understand the concept of that article to add a column with a checkbox, but I have the following code which populates the data grid from my SQL query. I end up with several columns which have either "True" or "False" in their cells. My question is, can I convert a column so that it has a checkbox instead of the "True" or "False". i.e. Columns(3) for example, or can the following code be rewritten, so that it makes these columns contain checkboxes from the outset?

Andrew

Me.dgdBrowseCttrLocns.Columns.Clear()

        Dim sql As String

        sql = "select CttrLocn_AACCode as [AAC Code],CttrLocn_AIMSNo as [AIMS No],CttrLocn_CttrName as [Cttr Name]" _
            + ",CttrLocn_GFE_Held as [GFA Held]" _
            + ",cast(round(CttrLocn_Materiality,5)as decimal (20,5)) as Materiality" _
            + ",CttrLocn_PriorityCttr as [Priority Contractor]" _
            + ",CttrLocn_AACGuidelinesMet as [Meets AAC Guidelines]" _
            + ",CttrLocn_Address1 + ' ' + CttrLocn_Address2 + ' ' + CttrLocn_Address_Town + ' '" _
            + "+ CttrLocn_Address_County + ' ' + CttrLocn_Address_PostCode as Address,CttrLocn_Remarks as Remarks" _
            + ",CttrLocn_Issues as Issues,CttrLocn_NoLines as [Num Lines]" _
            + ",cast(cast(round(CttrLocn_Materiality/(select sum(CttrLocn_Materiality) from cttrlocation)*100,5)" _
            + "as decimal(7,5))as varchar(12)) + '%' as [% Total Materiality]" _
            + "from cttrlocation compute sum(cast(round(cttrlocn_materiality,5)as decimal(20,5)))"

        Me.Cursor = Cursors.WaitCursor

        Dim cmd As New SqlCommand(sql, aiidb2.Connection)
        cmd.Connection.Open()

        dgdBrowseCttrLocns.Visible = False

        Dim myReader As SqlDataReader = cmd.ExecuteReader
        Dim record As Integer = 0
        Dim column As Integer
        Dim header As String
        header = ""
        While myReader.Read()
            record = record + 1
            Dim tmp(200) As String
            For column = 0 To myReader.FieldCount - 1
                If record = 1 Then
                    dgdBrowseCttrLocns.ColumnCount = column + 1
                    dgdBrowseCttrLocns.Columns(column).Name = myReader.GetName(column)
                End If
                tmp(column) = myReader.GetValue(column).ToString()
            Next
            dgdBrowseCttrLocns.Rows.Add(tmp)
        End While

        With dgdBrowseCttrLocns
            .Columns(0).Width = 60
            .Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(1).Width = 60
            .Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(2).Width = 250
            .Columns(3).Width = 60
            .Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

            .Columns(4).Width = 90
            .Columns(5).Width = 80
            .Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(6).Width = 80
            .Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(7).Width = 200
            .Columns(8).Width = 200
            .Columns(9).Width = 200
            .Columns(10).Width = 60
            .Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .Columns(11).Width = 80
            .Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        End With

        dgdBrowseCttrLocns.Visible = True

        cmd.Connection.Close()

[manal]

My question is, can I convert a column so that it has a checkbox instead of the "True" or "False". i.e. Columns(3) for example

yes you can
after you establish the connection with database , and fill the dataset with data, assuming you have datagrid with name dgdFunctionArea
creat new table and define the columns

Dim dtCol As DataColumn = Nothing                  'Data Column variable
Dim arrstrFunctionalArea As String() = Nothing             'string array variable
Dim dtblFunctionalArea As New DataTable                      'Data Table var
Dim n As Integer
 n = 5   ' n is the number of columns in your table that are not checkbox
arrstrFunctionalArea = New String(n)                             
arrstrFunctionalArea(0) = "AAC Code" 
arrstrFunctionalArea(1) = "Cttr Name" 
........
'Create the Data Table object which will then be used to hold columns and rows
        dtblFunctionalArea = New DataTable("TableName")

'Add the string array of columns to the DataColumn object 
        Dim i As Integer
        For i = 0 To 4
            Dim str As String = arrstrFunctionalArea(i)
            dtCol = New DataColumn(str)
            dtCol.DataType = System.Type.GetType("System.String")
            dtCol.DefaultValue = ""
            dtblFunctionalArea.Columns.Add(dtCol)
        Next i

now you created columns in table that are not check box as in the artical
after that you creat columns that will hold true/false values

'Add a Column with checkbox in the Grid 
           'create the data column object with the name TFColumn
           Dim dtcCheck As New DataColumn("TFColumn")
           'Set its data Type
           dtcCheck.DataType = System.Type.GetType("System.Boolean")       
           dtcCheck.DefaultValue = False           'Set the default value
           dtblFunctionalArea.Columns.Add(dtcCheck)   'Add the above column to the Data Table

after that you set the source of your datagrid as the Data Table createed above

dgdFunctionArea.DataSource = dtblFunctionalArea

now you will fill the table you created from dataset by creating rows and then filling them from data in dataset

Me.OleDbDataAdapter1.Fill(Me.DataSet11)
        Dim count As Integer
        count = Me.DataSet11.TableName.Count       'to gte the number of rows in dataset
        For i = 0 To count - 1
            Dim Row1 As DataRow
            Row1 = dtblFunctionalArea.NewRow()     'declaring a new row
            'filling the row with values       
            Row1.Item("AAC Code") = Me.DataSet11.TableName.Rows(i).Item(0) 
              .
              .
              .
            Row1.Item("TFColumn") = Me.DataSet11.TableName.Rows(i).Item(3)
              .
              .
            dtblFunctionalArea.Rows.Add(Row1)      'to add the row you created to the table
        Next i

[ads248]

Hi

I get "value of type 'Integer' cannot be converted tp '1 dimensional array of Char" with the following line of code when attempting to define the columns as suggested: -

arrstrFunctionalArea = New String(n)

Thanks

Andrew

[manal]

I get "value of type 'Integer' cannot be converted tp '1 dimensional array of Char" with the following line of code when attempting to define the columns as suggested: -

arrstrFunctionalArea = New String(n)

error means you defined something as integer then you tried to store in it array of char
I tried the code and I did not have this error
maybe you defined n like this
Dim n As String() ?

[ads248]

No, defined as per your code, because I copied it and pasted it in ?