Checkbox problem in Datagrid

Hi Experts,
Currently I have a problem of not being able to edit the checkbox on the grid. The datagrid bound to a dataset which is filled by a DataAdapter. Since the table stored in MySql at run-time, I do not know the fields in advace, therefore I have to get data from database to dataset via Adapter. Also in MySQL boolean type is present at tinyint(1). Thus when being filled in dataset, it has "SByte" type instead of "Boolean" type .
Here is my code:

myAdapter.SelectCommand = New MySqlClient.MySqlCommand("select * from inspection_temp", myCon)
        myBuilder = New MySqlClient.MySqlCommandBuilder(myAdapter)
        myCon.Open()
        myAdapter.Fill(ds, "inspection_temp")

        format_grid()
        dg.SetDataBinding(ds, "inspection_temp")

Private Sub format_grid()
        Dim i As Integer
        Dim data_type, col_name As String
        Dim grdStyle As New DataGridTableStyle

        With grdStyle
            .MappingName = "inspection_temp"
            With .GridColumnStyles
                For i = 0 To (ds.Tables("inspection_temp").Columns.Count - 1)
                    data_type = ds.Tables("inspection_temp").Columns(i).DataType.Name
                    col_name = ds.Tables("inspection_temp").Columns(i).ColumnName
                    If data_type = "SByte" Then
                        .Add(New DataGridBoolColumn)
                    Else
                        .Add(New DataGridTextBoxColumn)
                    End If
                    With .Item(i)
                        .MappingName = col_name
                        .HeaderText = col_name
                        .ReadOnly = False
                        .NullText = ""
                    End With
                Next
            End With
        End With
        dg.TableStyles.Clear()
        dg.TableStyles.Add(grdStyle)
    End Sub

The Datagid displays grey checkbox which I can edit when having focus, but when I tab to the next cell it goes back to the grey checkbox. other columns in the datagrid are editable as they are String and Number types.
Anyone could help,please
LA
LanAnhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:

change

.Add(New DataGridBoolColumn)

to

       Dim boolCol As DataGridBoolColumn = New DataGridBoolColumn()
        boolCol.FalseValue = "0"
        boolCol.TrueValue = "1"
       .Add(boolCol)

change 0 and 1 to the values indicating true and false in your DB.
LanAnhAuthor Commented:
Thanks appari
I have tried your solution but it does not work, once the cell lost focus it goes back to grey.
However I have tried an alternative way which could solve my problem by constructing a DataTable.
         
       For i = 0 To rs.Fields.Count - 1
            col_name = rs.Fields(i).Name
            col_type = rs.Fields(i).Type
           
            Select Case col_type
                Case 5 : myColumn = New DataColumn(col_name, GetType(Double))
                Case 16 : myColumn = New DataColumn(col_name, GetType(Boolean))
                Case 133 : myColumn = New DataColumn(col_name, GetType(Date))
                Case 200 : myColumn = New DataColumn(col_name, GetType(String))
            End Select
            myTable.Columns.Add(myColumn)
        Next
        myAdapter.SelectCommand = New MySqlClient.MySqlCommand(sql, myCon)
        myBuilder = New MySqlClient.MySqlCommandBuilder(myAdapter)
        myCon.Open()
        myAdapter.fill(myTable) 'fill data to myTable
        ds.Tables.Add(myTable) 'add myTable to the dataset
        dg.SetDataBinding(ds, "inspection_temp")
So I have solve the problem in this way.
LA

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LanAnhAuthor Commented:
I forgot how to close this question as I have solved it myself. Any experts knows how?Thanks
appariCommented:
post a question asking for points refund in community support http://www.experts-exchange.com/Community_Support/ giving link to this question.
LanAnhAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.