JBoom JBoom - 5 months ago 7
Vb.net Question

How to insert NULL into database if form field is empty

I have a form and stored procedure that inserts the data from the form. It works fine except that if a field isn't filled in it doesn't insert a

NULL
into SQL it inserts
""
.

I've tried a few different ways but none seem to insert
NULL
, the one below still inserts
""
, can anyone point me in the right direction?

Here is the required part of the code, if you require more just let me know.

Dim rdr As SqlDataReader
Dim cmdInsert As SqlCommand = New SqlCommand()
cmdInsert.CommandText = "spPersonalDetailsInsert"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = connSQL


Dim firstname, lastname, address, address1, town, county, postcode As SqlParameter
'convert to null if ""
Dim frmFirstName As String
If pd_first_name.Text = "" Then
frmFirstName = Convert.DBNull
Else
frmFirstName = pd_first_name.Text
End If

firstname = New SqlParameter()
firstname.ParameterName = "@firstname"
firstname.SqlDbType = SqlDbType.NVarChar
firstname.Size = 50
firstname.Direction = ParameterDirection.Input
firstname.Value = frmFirstName


EDIT

I tested the following code:

If pd_first_name.Text = "" Then
frmFirstName = DBNull.Value
Else
frmFirstName = pd_first_name.Text
End If


But it still doesn't insert
NULL
so I tested this:

If pd_first_name.Text = "" Then
Response.Write("NULL")
address1.Value = DBNull.Value
Else
Response.Write("NOT NULL")
address1.Value = pd_address1.Text
End If


So if I enter nothing into
address1
field it should write
NULL
to screen but it always writes
NOT NULL
. What does an empty form field equal? in classic ASP it was always
""
.

Answer

You need to use DBNull.Value

            If String.IsNullOrEmpty(pd_first_name.Text.ToString().Trim) = true Then
                frmFirstName = DBNull.Value
            Else
                frmFirstName = pd_first_name.Text
            End If
Comments