CODEWITHSUNDEEP
c#.netwinformsdata-binding
Steven
Steven

Reputation: 13769

NULL Empty Item in Bound ComboBox

How can I have an empty item in the bound ComboBox which uses NULL as the value for an Insert or Update?

With the code below, I can manually add the additional row. The column inspector_id is the primary key of an FK relationship. I have to set inspector_id = -1, since C# does not allow an int to be null. However, the insert (or update) fails since there is no inspector_id: -1 in the database.

private void ItemInfo_Load(object sender, EventArgs e)
{
    // TODO: This line of code loads data into the 'someDBDataSet.inspector' table. You can move, or remove it, as needed.
    this.inspectorTableAdapter.ClearBeforeFill = false;
    someDBDataSet.inspectorRow newRow = this.someDBDataSet.inspector.NewinspectorRow();
    newRow.inspector_id = -1; // Since an int in C# cannot be null
    newRow.fullName = "(none)";
    newRow.employeeCode = "";
    this.someDBDataSet.inspector.AddinspectorRow(newRow);

    this.inspectorTableAdapter.Fill(this.someDBDataSet.inspector);
    //this.inspectorTableAdapter.ClearBeforeFill = false;
    // TODO: This line of code loads data into the 'someDBDataSet.item' table. You can move, or remove it, as needed.
    this.itemTableAdapter.Fill(this.someDBDataSet.item);
}

Upvotes: 0

Views: 1185

Answers (3)

Steven
Steven

Reputation: 13769

Eureka! Bind to a view, not table.

Bind inspector_idComboBox to a new SQL Server view of the inspector table.

SELECT NULL as inspector_id, '(none)' as fullName, '' as employeeCode
UNION
SELECT inspector_id, fullName, employeeCode
FROM dbo.inspector

Pros:

  1. The (none) item is in the ComboBox
  2. The SelectedItem and text persists when selecting the item.
  3. The SQL view allows a NULL value for inspector_id
  4. No workarounds are needed in the application code. Just fill the DataSet from the view.
  5. Allows more flexibility as the relationship is not bound.

... brilliant!

Upvotes: 3

Steven
Steven

Reputation: 13769

After trying various approaches, I initially decided on the following:

private void ItemInfo_Load(object sender, EventArgs e)
{
    this.inspectorTableAdapter.Fill(this.someDBDataSet.inspector);
    this.itemTableAdapter.Fill(this.someDBDataSet.item);
}

private void noInspector_btn_Click(object sender, EventArgs e)
{
    inspector_idComboBox.SelectedItem = null;
}

Rather than adding a dummy item into the ComboBox, I added a (link) button to clear the ComboBox.

Pros:

  1. The ComboBox clears.
  2. The tableAdapter sets item.inspector_id = NULL

Cons:

  1. Other form controls bound to inspector fields remain unchanged (as there is no "empty" inspector row to use).
  2. No text is displayed in inspector_idComboBox when SelectedItem is null. I'd prefer having something like (none) shown in the box.

Upvotes: 0

Steven
Steven

Reputation: 13769

Another approach is to clear the ComboBox when selecting (none):

private void inspector_idComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    if (inspector_idComboBox.SelectedValue != null)
        if ((int)inspector_idComboBox.SelectedValue == -1)
            inspector_idComboBox.SelectedItem = null;
}

Pros:

  1. The correct NULL value is saved to the DataSet and sent on to the database.
  2. No external clear button is needed.

Cons:

  1. Selecting (none) also clears the text. I'd prefer for (none) to stay selected.

Upvotes: 0

Related Questions