[RESOLVED] Using DataGrid with DataTable source - problem adding Combobox to one column

[AndyLD]

Hi!

I have a DataGridView, it's DataSource is set to a DataTable. One column in the table has only three possible values "C","N","O". I managed to get a ComboBox showing in the DataGridView in a new Column with this code:

 Dim cmb As New DataGridViewComboBoxColumn()
            Dim myTab As New DataTable
            myTab.Columns.Add("id", GetType(String))
            myTab.Columns.Add("value", GetType(String))
            myTab.Rows.Add("C", "certified")
            myTab.Rows.Add("O", "controlled")
            myTab.Rows.Add("N", "unknown")

            Dim GColumn As New DataGridViewComboBoxColumn
            GColumn.HeaderText = "Certification"
            GColumn.DataSource = myTab
            GColumn.DisplayMember = "value"
            GColumn.ValueMember = "id"
            newGrid2.Columns.Add(GColumn)

However, I don't know how to link this cell to the according value in the column of the DataGridView.
Should I update the combobox of each row one by one? This does not seem to be a nice solution to me.

[AndyLD]

BTW the old VB6 code, that I am now upgrading to VB.NET, had a very simple solution to this, but I could not find a matching new solution:

With grid2.columns("certification")
 .Control.Type = sgCellDropList
 .ValueItems.Add "O", Certified
 .ValueItems.Add "C", Controlled
 .ValueItems.Add "N", unknown
End With

I keep looking at the post here: http://www.vbforums.com/showthread.p...iew&highlight=
But I'm not sure this is the solution for me...I'm trying to figure it out.

[dunfiddlin]

It's the solution. Worth persevering with anything you don't understand.

[jmcilhinney]

As dunfiddlin says, that CodeBank thread of mine does demonstrate the exact principle that you need to implement. Your implementation will be different in the details but that's all. Here's the original GetParentTable method from that CodeBank thread:

Private Function GetParentTable() As DataTable
    Dim table As New DataTable

    With table.Columns
        .Add("ParentID", GetType(Integer))
        .Add("ParentName", GetType(String))
    End With

    With table.Rows
        .Add(1, "Parent 1")
        .Add(2, "Parent 2")
        .Add(3, "Parent 3")
    End With

    Return table
End Function

and here's what yours would look like:

Private Function GetParentTable() As DataTable
    Dim table As New DataTable

    With table.Columns
        .Add("Code", GetType(String))
        .Add("Name", GetType(String))
    End With

    With table.Rows
        .Add("C", "Certified")
        .Add("O", "Controlled")
        .Add("N", "Unknown")
    End With

    Return table
End Function

You would modify the GetChildTable appropriately too.

By the way, are you aware that you have transposed the C and O codes from your original VB6 code?

[AndyLD]

Thanks Dunfiddlin, you're right, it's just lack of time that does not let me get a deeper insight in all of this right now. Five days ago I first saw VB6 and VB.NET. My employer wants me to make multiple functional changes to the application, but I refused to start on the old code. The reason is, that the old code works everywhere with Object objects, uses variables without defining them and uses no nameing convention for variable names, uses methods without brackets etc. I believe in the code everything is done wrong, except for that the application "kind of" works.
E.g. if there is no new records to be worked on, the application exits with an Internal Error - it's something the people who work with it know and have got used to it
So, now I am here upgrading this mess and I don't want get to the point after a week, where I tell - well I cannot upgrade it, lets work on the old code, this would be a total defeat.

@jmcilhinney (is there a naming convention behind this nick ?)
Thank you very much for confirming this solution, this really helps, because the solution is in my eyes simple BUT very tricky.
The variables got transposed in my post, because the original values and names are localized - i thought it would look strange to have a K for controlled etc. .

[jmcilhinney]

@jmcilhinney (is there a naming convention behind this nick ?)

Yes there is. Initial of first name followed by last name: John McIlhinney

[AndyLD]

Well, that's a damn good naming convention. I'm sorry to bother you with this, but could you write your family name in phonetic transcription, else I'm afraid I brake it every time I read it.

[AndyLD]

I set up the column like in jmcilhinney's tutorial, the column is visible and contains the desired data. But, I cannot link it to the data coming from the Database.
The full setup is as following:

My form has multiple comboboxes and two DataGridViews. Whenever a selection of a value in any of the comboboxes changes, the data gets fetched from the database and it is given to the first grid like this:

  Dim da As New OleDbDataAdapter(mySQLCommand)
            Dim skJoin As New DataTable()
            da.Fill(skJoin)
            newGrid1.DataSource = skJoin

When any cell in the first Grid gets selected, the second Grid gets populated with the data based on the selected row in the first grid:

myOleDBCommand.CommandText = sSQL
            Dim da As New OleDbDataAdapter(myOleDBCommand)
            Dim skPackets As New DataTable()
            da.Fill(skPackets)
            myGrid2BindingSource.DataSource = skPakas
            newGrid2.DataSource = myGrid2BindingSource

The column "certification" in the DataTable of the second DataGridView contains the three possible values ("C","O","N").
I need this column to be a DataGridViewComboBoxColumn with the three possible values.

I have added a ComboBoxColumn like in the example and I have populated it with the "GetTable" method:

myGrid2BindingSource2.DataSource = GetCertTable()
            Column1.DisplayMember = "Name"
            Column1.ValueMember = "Code"
            Column1.DataSource = myGrid2BindingSource2

Now I try to find a way to tell column1 to take its value from column "certification"
But I cannot find a way to tell it.

[AndyLD]

I found a complete example of my problem here: http://msdn.microsoft.com/en-us/libr...boxcolumn.aspx
Now I have a lot to study.

I feel stupid, but where do I find the sample database for the example from the MSDN reference?
I switched the connection from SqlConnection to a MySQL connection, but I cannot recreate the database with all its linked data. I think there must be the example database somewhere, because the example cannot work without it. Am I blind?

[AndyLD]

As I cannot get the example running(I don't find the necessary database with its sample data), I continue to search for an answer.
There is a DataGridViewColumn.DataPropertyName property, it sounds like the answer to my problem, but when I set the property to the column name "sertification" nothing happens.

[AndyLD]

OK, the thing is really easy.
Add comboboxcolumn to the datagridview.
Add BindingSource to the dataGridView.
Set BindingSource.DataSource = to the column name of the data from database.
Set comboboxcolumn DataSource = DataTable with all possible options
Set combobboxcolumn.DataPropertyName = BindingSource.DataSource()
Set comboboxcolumn DisplayMember to the Values to be shown in the combobox, taken from the DataTable
Set comboboxcolumn.ValueMember to the column name of the DataTable with the key that will match the data from the database.

One thing I just had problems with was, that the value column in the database, which listed all ID's of warehouses, was defined as double, so if your table with the alternatives defines it as Integer this will throw an DataException.