Combobox and DBNull 1

[Durkin]

Hi. I'm trying to binding a field in a datatable to a combobox. Another table in the dataset is providing the Valuemembers and displaymembers. This field can contain nulls so I'm adding a row with a dbnull and "" into the start of the table where the Valuemembers and displaymembers are. However, when I attempt to bind to the 'SelectedValue' property I get an error:

Specified argument was out of the range of valid values. 
Parameter name: '-2147483554' is not a valid value for 'index'.

This only happens if I bind to a field that is null. If I open the form and the field is not null then it works fine. Also, in this case, if I try to change this field to the null row in the combobox then I can't leave the combobox.
I'm sure I'm missing something obvious but I've been looking at this for two days and it hasn't exactly jumped out at me:-( so any help would be greatly appreciated.
Thanks in advance,


Durkin

 

[chiph]

You cross-posted this to the VB.NET forum. What language are you using?

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[Durkin]

VB, but does it really make much difference?

Durkin

 

[chiph]

In this case, it probably does.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[obislavu]

I think you have a DataSet from which you bind the ComboBox.
After you fill the DataSet object e.g. Fill() is called sucessfull, you should iterate all table rows and inspect each column for System.DBNull.Value and replace with "":

if (myRow[myCol] == System.DBNull.Value)
myRow[myCol]="";

-obislavu-

 

[Durkin]

The column I am binding to is an integer column so putting a "" in it would also cause an error. I can successfully put a 0 or some other value in there but I want the users to be able to put nulls in the column.

Durkin

 

[obislavu]

The users cannot put NULL (as database NULL value) directly. It will be put when you will put back in the database record (insert or update). So, if you want a column to be set to NULL in the database depending on the user action , you have to set the value of this column to System.DBNull.Value and when the AcceptChanges() is called the database will have NULL in the corresponding column.
-obislavu-

 

[Durkin]

Yes, this is the problem I am having. When I try to bind to a column in the datatable that has DBNull.Value or I try to change a column to DBNull.Value I am getting errors. I think that it may have to do with the fact that binding to an integer field is a problem because DBNull.Value is an object not a primitive type.

Durkin

 

[obislavu]

You should not get error when you put back the DataSet into database if you say, for example :
DataRow dr;
dr["Amount"] = System.DBNull.Value;
and the "Amount" column in the database is set to allow NULL.
More than that you could set the AllowDBNull property for the column in the DataTable to allow nulls:
DataTable myTable;
myTable.Columns["Amount"].AllowDBNull = true;

-obislavu-

 

[Durkin]

Both the column in the database and the dataset allow nulls. If I did as you say

dr"Amount" = System.DBNull.Value;

this would work. However, I want to use databinding so this should automatically execute this line without me having to code it. The problem seem to be the way the binding class handles the DBNull.

Durkin

 

[Durkin]

Finally, I got it working. I'm not putting a blank row in the datatable anymore. What I'm doing is in the Validating event of the combobox I'm saying:

Private Sub cmbIN_AD_ID_Validatedata(ByVal sender As System.Object, ByVal e As PamIR.PamIREventArgs) Handles cmbIN_AD_ID.Validatedata
    If Me.cmbIN_AD_ID.SelectedValue Is Nothing Then
        If Utils.PutDBNullInCombo(Me.cmbIN_AD_ID, ds.Tables(0), "IN_AD_ID") Then
            Me.chkIN_Allow_AD_Access.Checked = False
            Me.chkIN_Stmts_To_AD.Checked = False
        End If
    End If
End Sub

which is calling the routine

PutDBNullInCombo

which is:

Public Shared Function PutDBNullInCombo(ByVal Combo As PamIRComboBox, ByVal dt As DataTable, ByVal FieldName as String) As Boolean

    If Not dt.Rows(0).Item(FieldName) Is System.DBNull.Value Then
        If dt.Columns(FieldName).AllowDBNull Then
            dt.Rows(0).Item(FieldName) = System.DBNull.Value
            Return True
        End If
    End If

    Return False

End Function

Anyway, thanks for your help. It's good to have someone to talk it out with. Have a star.


Durkin