[2008] DataSets, SQL Server and Null values

[Aspnot]

I have a strongly typed DataSet (dsContacts) that is bound to a textbox (txtFirstName). I use DataAdapters to Fill my DataSet with data from SQL Server stored procedures.

If a user creates a contact with no First Name, it is initially Null in the database. If a user then edits the record and puts in a value, that value is in the database. If a user then removes the value and saves it back to SQL Server, the value in the database is an empty string. I want this value to be Null.

I have a bunch of Update procedures that I would like this to happen with. Is there a way to force the DataSet to work this out rather than editing all of my SPs to have a NullIf() statement? Is there another approach that might be better?


Thanks in advance!

[wild_bill]

Maybe something like this

        If txtFirstName.Text = "" Then
            dr("firstname") = DBNull.Value
        Else
            dr.firstname = txtFirstName.Text
        End If

[Clanguage]

In SQL you can give a default value to the column. Open the table in design view and give it a default of null.

[Aspnot]

Wild Bill,
The control is Bound to the DataSet. This type of If...Then won't really work. Besides, I am looking for something far easier than this.

In total including all of the SPs, there are probably over 100 places I would need to do this. I can do this in a simpler NullIf() statement in the SP.


CLanguage,
I just tried that as that would have been perfect. The only problem is that the DataSet passes the empty string to the SqlCommand object and not Default.


It seems to me that it would have to be somewhere at the DataSet level. It dictates the entire interaction with the data.

Thoughts?

[jmcilhinney]

It feels like there should be an easier way but I don't think there is.

1. Create a new WinForms project and add a TextBox and a Button to the form.
2. Add this code:

Private table As New DataTable

Private Sub Form1_Load(ByVal sender As Object, _
                       ByVal e As EventArgs) Handles MyBase.Load
    Me.table.Columns.Add("Name", GetType(String))
    Me.table.Rows.Add(DBNull.Value)

    Me.TextBox1.DataBindings.Add("Text", Me.table, "Name")
End Sub

Private Sub Button1_Click(ByVal sender As Object, _
                          ByVal e As EventArgs) Handles Button1.Click
    Dim value As Object = Me.table.Rows(0)("Name")

    If value Is DBNull.Value Then
        MessageBox.Show("Null")
    ElseIf CStr(value) = String.Empty Then
        MessageBox.Show("Empty String")
    Else
        MessageBox.Show(CStr(value))
    End If
End Sub

3. Run the project and click the Button.
4. Enter some text and click the Button again.
5. Clear the text and click the Button again.

Note that you get "Null", "the text you entered", and "Empty String". That third message is obviously the issue.

Now change the code to this:

Private table As New DataTable

Private Sub Form1_Load(ByVal sender As Object, _
                       ByVal e As EventArgs) Handles MyBase.Load
    Me.table.Columns.Add("Name", GetType(String))
    Me.table.Rows.Add(DBNull.Value)

    Me.TextBox1.DataBindings.Add("Text", Me.table, "Name")

    AddHandler Me.TextBox1.DataBindings("Text").Parse, AddressOf ForceNullValueOnEmptyField
End Sub

Private Sub Button1_Click(ByVal sender As Object, _
                          ByVal e As EventArgs) Handles Button1.Click
    Dim value As Object = Me.table.Rows(0)("Name")

    If value Is DBNull.Value Then
        MessageBox.Show("Null")
    ElseIf CStr(value) = String.Empty Then
        MessageBox.Show("Empty String")
    Else
        MessageBox.Show(CStr(value))
    End If
End Sub

Private Sub ForceNullValueOnEmptyField(ByVal sender As Object, _
                                       ByVal e As ConvertEventArgs)
    If CStr(e.Value) = String.Empty Then
        e.Value = DBNull.Value
    End If
End Sub

and try the same steps as before. Tada!

[Aspnot]

Brilliant. Why didn't I think about using the Parse routine? I use it to handle int, bools, etc.

Thanks. I appreciate everyone's help on this.