Combobox will NOT update dataset value to NULL\Blank

[PhillD]

Is it a known issue that the combobox will not save changes to the dataset if the entry in the combobox is blank? This problem is driving me insane! Here's the scenario.

The user open the form, the record loads, they make a selection from a combobox and save the record. The user then realizes they made a mistake and they need to delete the selection from the combobox. They do so and save the record. They close the form and re-open it, the value they removed has come back, the value did not get removed.

It appears that when the user removes the entry from the combobox, the changes are not being made to the underlying dataset.

 

[jmcilhinney]

How exactly is the user "removing" the entry from the ComboBox, given that that's not something they can do through the UI?

 

[PhillD]

Maybe you misunderstood my scenario. They are clicking into the combo box and pressing the backspace key. They are removing the selection they made. Exactly as you would do in a text box.

 

[PhillD]

I can confirm that the combobox.selectedvalue = "" after the user deletes the entry in the combobox. My databinding is set to the SelectedValue so I don't understand why the dataset is not updated. I'm not having any other datasave related problems other than this.

 

[jmcilhinney]

How EXACTLY are you saving, i.e. what does the user do and what does your code do? I suspect that if you were binding via a BindingSource and were to call its EndEdit method before saving then you'd get the desired result.

 

[PhillD]

I was just creating a mock up project and now I'm getting some truly bizzare behaviour. Now when I delete the entry in a text box, it's reverting back to the previous value. Here is my code.

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Dim Connectionstring1 As SqlConnection
    Dim da As New SqlDataAdapter
    Dim da2 As New SqlDataAdapter
    Dim ds As DataSet
    Dim cb As SqlCommandBuilder
    Dim SQLString As String

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try

            'Load customer DropDown

            Connectionstring1 = New SqlConnection(My.Settings.ConnectionString1)

            SQLString = "SELECT CustomerID, CustomerID + ' - ' + CustomerName as CustomerName FROM TESTCustomers Order By CustomerName"

            da.SelectCommand = New SqlCommand
            da.SelectCommand.Connection = Connectionstring1
            da.SelectCommand.CommandText = SQLString

            ds = New DataSet()
            da.Fill(ds, "TESTCustomers")

            ComboBox1.ValueMember = "CustomerID"
            ComboBox1.DisplayMember = "CustomerName"
            ComboBox1.DataSource = ds.Tables("TESTCustomers")

            'load form ds

            SQLString = "Select * FROM TESTChecks"


            da2.SelectCommand = New SqlCommand
            da2.SelectCommand.Connection = Connectionstring1
            da2.SelectCommand.CommandText = SQLString
            da2.Fill(ds, "TESTChecks")
            cb = New SqlCommandBuilder(da2)

            ComboBox1.DataBindings.Add("SelectedValue", ds.Tables("TESTChecks"), "CustomerID")
            TextBox1.DataBindings.Add("Text", ds.Tables("TESTChecks"), "CheckNumber")


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub Form1_FormClosing(sender As System.Object, e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        Try

            Me.BindingContext(ds.Tables("TESTChecks")).EndCurrentEdit()
            da2.Update(ds.Tables("TESTChecks"))
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub
End Class

The 2 database tabled are as follows:

TESTCustomers
CustomerID nvarchar(7) PK
CustomerName nvarchar(40) ALLOW NULLS

TESTChecks
CheckID int PK IDENTITY(1,1)
CustomerID Nvarchar(7) ALLOW NULLS
CheckNumber int ALLOW NULLS

 

[PhillD]

I am completley lost as to why this code is causing BOTH the textbox and the combobox to not update the dataset. The textbox behavior is even worse because the value keeps reverting back to it's prior value when I delete the entry or type a text value. Is this a new bug?

 

[PhillD]

I have made SOME progress in this issue with regard to the text box, but NOT the ComboBox.

I added 2 lines to make the dataset accept NULL's on the CheckNumber and CustomerID columns, I also amended the databinding code.

Here is the new code, but the combo box still doesn't work.

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Dim Connectionstring1 As SqlConnection
    Dim da As New SqlDataAdapter
    Dim da2 As New SqlDataAdapter
    Dim ds As DataSet
    Dim ds2 As DataSet
    Dim cb As SqlCommandBuilder
    Dim SQLString As String

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try

            'Load customer DropDown

            Connectionstring1 = New SqlConnection(My.Settings.ConnectionString1)

            SQLString = "SELECT CustomerID, CustomerID + ' - ' + CustomerName as CustomerName FROM TESTCustomers Order By CustomerName"

            da.SelectCommand = New SqlCommand
            da.SelectCommand.Connection = Connectionstring1
            da.SelectCommand.CommandText = SQLString

            ds = New DataSet()
            da.Fill(ds, "TESTCustomers")

            ComboBox1.ValueMember = "CustomerID"
            ComboBox1.DisplayMember = "CustomerName"
            ComboBox1.DataSource = ds.Tables("TESTCustomers")

            'load form ds

            SQLString = "Select * FROM TESTChecks"


            da2.SelectCommand = New SqlCommand
            da2.SelectCommand.Connection = Connectionstring1
            da2.SelectCommand.CommandText = SQLString

            ds2 = New DataSet()
            da2.Fill(ds2, "TESTChecks")
            ds2.Tables("TESTChecks").Columns("CustomerID").AllowDBNull = True
            ds2.Tables("TESTChecks").Columns("CheckNumber").AllowDBNull = True

            cb = New SqlCommandBuilder(da2)

            ComboBox1.DataBindings.Add("SelectedValue", ds2.Tables("TESTChecks"), "CustomerID", True, DataSourceUpdateMode.OnValidation, String.Empty)
            TextBox1.DataBindings.Add("Text", ds2.Tables("TESTChecks"), "CheckNumber", True, DataSourceUpdateMode.OnValidation, String.Empty)
            TextBox2.DataBindings.Add("Text", ds2.Tables("TESTChecks"), "CheckID")


        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    Private Sub Form1_FormClosing(sender As System.Object, e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        Try

            Me.BindingContext(ds2.Tables("TESTChecks")).EndCurrentEdit()
            da2.Update(ds2.Tables("TESTChecks"))
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub

    'Private Sub ComboBox1_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles ComboBox1.Validating
    '    If ComboBox1.Text = "" Then
    '        ComboBox1.SelectedValue = String.Empty
    '    End If
    'End Sub
End Class