How to cast datakey in a DataBinding

[AndyLD]

Hello!

I have a DataGridView which is bound to a DataTable populated from database. I have added a ComboBoxColumn to the DataGridView.
The first two ComboBoxColumns get populated from a DataTable created within my code, but now I need to populate it from the database.
I populated the "possibleAlternativeValues" from the database and I set up the bindingSource.
BUT, the ID in one table is Integer and the corresponding ID in another table is Double (the database has no relations set up).
Now I need to set some sort of cast in the BindingSource.
The code related to this is:

myBindingSource.DataSource = "heap_id" 'defined as Double in database
            With gredaComboboxColumn
                .DataSource = myDataTableWithChoices
                .DataPropertyName = myBindingSource.DataSource()
                .DisplayMember = "title"
                .ValueMember = "id" 'type of id in the table from which myDataTableWithChoices get's populated is set to Integer
                .HeaderText = "Greda1"
            End With

Is there some possibility to cast this binding?

[AndyLD]

OK, probably I should do it a step earlier, cast it in the DataTable of the alternative choices.

yOleDBCommand.CommandText = "select id, title from heap where record_state='N' order by title"
            Dim myFillAdapter As New OleDbDataAdapter(myOleDBCommand)
            Dim myChoices As New DataTable()
            myFillAdapter.Fill(myChoices)

At least I could not find any way to cast keys in the binding, but maybe this would be too much to ask for :-)

[dunfiddlin]

BUT, the ID in one table is Integer and the corresponding ID in another table is Double

I think that extremely unlikely. Databases generally make no distinction between numeric types. A number is a number is a number.

At least I could not find any way to cast keys in the binding

Nor would you expect to. The binding is simply a gateway to a Datatable which allows values to be transmitted to a control.

[jmcilhinney]

One possibility is to add an extra column to one of the DataTables and set its Expression property so that it's values will be dependent on the values in the PK column. The Expression can convert the data from one data type to the other, e.g.

myDataTable.Columns.Add("DoubleID", GetType(Double), "CONVERT(ID, 'System.Double')")

You can then use that new DoubleID column to relate your DataTable to another DataTable whose ID column is type Double. If you want to do it the other way then:

myDataTable.Columns.Add("IntegerID", GetType(Integer), "CONVERT(ID, 'System.Int32')")

[AndyLD]

Dear jmcilhinney,

thanks, I will look into that as soon as I will get back to "work", as I got a new workstation yesterday and only now after 5 hours of time spent I start to get back to what I called my working environment.

[AndyLD]

@dunfiddlin

I think that extremely unlikely. Databases generally make no distinction between numeric types. A number is a number is a number.

I'm not sure I understood your point, but the fact is, that in the table in which a column contains a reference key to the other tables ID, the reference key column is defined as DOUBLE. Access says "If joining this field to a AutoNumber field in a many-to-one relationship, this field must be Long Integer". Nevertheless, the join with the Double field works in Access.

BUT, probably the error I received was not due to this worng datatype in the column. Only after I have set up the cast( by adding the corresponding column to the DataTable and setting its DataType to Integer, before filling the table throught the OleDBAdapter):

Dim skPakas As New DataTable()
            skPakas.Columns.Add(New DataColumn("heap_id"))
            skPakas.Columns("heap_id").DataType = GetType(Integer)
            da.Fill(skPakas)
            myGrid2BindingSource.DataSource = skPakas

which works fine and the data is filled while type Integer is preserved, I kept getting the error. Then I found out, that my alternative choices combobox, does not contain all possible values from the referencing table, and the error probably came from this and not from the misdatatyped reference key column. So I solved one problem, which probably didn't even cause the error.

@jmcilhinney
Would this additional column be bound in both directions (read/write) to the original column? Thanks anyway, but most probably the problem I wanted to solve was never a problem.

[AndyLD]

I have to correct my statement from before.
The manual cast of the Double-typed-key in the DataGridView's DataSource DataTable column MUST BE DONE, I just wanted to remove it, as I thought the problem's source was a different one, and there I got it again, so definitely one cannot use a BindingSource on a DataGridViewComboBoxColumn datatable's id(datatype Integer) and a DataGridViews DataTables column with datatype Double in the access database.

[jmcilhinney]

That's why I suggested adding the computed column to the DataTable that you bind to the column. The data bound to the grid is being edited but the data bound to the column is not, so the fact that a computed column is read-only is no problem in that case.