How to insert NULL into database if form field is empty

Question

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 1

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