Put a <NULL> in a SQL database

[BukHix]

I am building a front end for a shrink-wrapped accounting program. I have one feild in my app that can sometimes contain a value and sometimes not. The problem that I am having is that the shrink-wrapped program will not accept an empty string in the field of the database.

There has to be a value or it has to be a <NULL>. Empty strings such as "" cause it to error. Any ideas of how to accomplish this from my form?

In my AddRecord Function I have this but none of these worked.

Dim strCityTax As String
 
        If txtCityTax.Text = "" Then
            'strCityTax = vbNullString
            'strCityTax = Convert.DBNull
            'strCityTax = DBNull
            'strCityTax = vbNullString.ToString
        Else
            strCityTax = txtCityTax.Text
        End If

[unisse]

You dont mention of what type strCityType is, but I guess that it is a string. If so, offcourse a string cannot be Null, a field in a database record can.

What kind of datahandling do you use? ADO .NET?

If you use ADO .NET, then a field in a DataTable can be nullable, and you should be able to set the value to Null if it is.

/Nisse

[BukHix]

I am using Imports System.Data.SqlClient, which I assume means that my data handling is ADO.Net.

strCityType is a string but if the field is left blank I dont want anything for that field sent to the database. That is unless I can send something that will tell the database to keep the field a <NULL>. Because an empty string will not work for the application.

Off the top of my head the only thing I can think of to get around this is to have two different Insert Statements and then test for a value in the txtCityTax text box. If there is something in that box send the Insert Statement with the txtCityTax data. If not send the Insert Statement without the data included. For instrance:

If txtcity.text = "" Then
            With cmd
                .CommandText = "Insert Into myTable (" & _
                    "Co, " & _
                    "BatchTransType" & _
                    ") Values(" & _
                    intCo & ", " & _
                    "'" & strBatchTransType & "')"
                .ExecuteNonQuery()
            End With
        Else
            With cmd
                .CommandText = "Insert Into myTable (" & _
                    "Co, " & _
                     "BatchTransType, " & _
                    "LocalCode" & _
                    ") Values(" & _
                    intCo & ", " & _
                    "'" & strBatchTransType & "', " & _
                    "'" & strCityTax & "'" & ")"
                .ExecuteNonQuery()
            End With
        End If

It just seems like that is a horrible way to accomplish what I am trying to do. Is there an alternative?

[Andy]

try setting the variable to NOTHING( strCityTax = nothing)
in the if block.

[Andy]

or try this :

strCityTax = convert.DBNull

[BukHix]

Thanks. Everything suggested made the field appear as an empty string in the database or cause it not to work inside the application.

I went with the 2 Insert Statements, which seems to be working out ok.

[nemaroller]

Yes, there are a few ways to do this.

I often work with Stored Procedures, that default to null, so if you DO NOT explicity pass the value of a parameter to the procedure, it becomes null on the DB side.

If Not strCity.Text = String.Empty Then SqlCommand1.Parameters(0).Value = strCity.Text

If there's something in the string, it get passed, if there is not, the stored proc defaults it null on the DB side.

To me, I wouldn't want to write explicit commands inside the application code, which may be subject to SQL injection attacks.