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 "".

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 2

In my case using ‘Nothing’ solves the problem. Use it like this

If String.IsNullOrEmpty(pd_first_name.Text) = True Then
   frmFirstName = Nothing
Else
   frmFirstName = pd_first_name.Text
End If

Method 3

why you even set it if it is null?

        If pd_first_name.Text <> "" Then
          frmFirstName = pd_first_name.Text
          firstname = New SqlParameter()
          firstname.ParameterName = "@firstname"
          firstname.SqlDbType = SqlDbType.NVarChar
          firstname.Size = 50
          firstname.Direction = ParameterDirection.Input
          firstname.Value = frmFirstName
        End If

Method 4

I think you problem is that frmFirstName is a string and a string cannot represent DBNull.

I think this will solve your problem (I’ve just commented out your code):

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
            If pd_first_name.Text = "" Then
                  firstname.Value = DBNull.Value
            Else
                  firstname.Value = frmFirstName
            End If

Method 5

Dim TempStr As String
TempStr= "spPersonalDetailsInsert"
TempStr = TempStr.Replace("''", "null")
cmdInsert.CommandText = TempStr

Now No Need to use

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

Hope this might be Helpful

Method 6

While creating stored procedure make those columns as null which can be null..
like

CREATE PROCEDURE [dbo].[USP_TDS_SaveRecod]

@ID INT,

@CODE  INT,

@FIRSTNAME VARCHAR(8)=NULL,

@CITY VARCHAR(15)=NULL

AS

BEGIN

    .........................

    .........................

    .........................
END

and then in code don’t add those parameters which are null..

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = obj.ID;
cmd.Parameters.Add("@CODE", SqlDbType.Int).Value = obj.CODE;
if(pd_first_name.Text != "")
{
    cmd.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = pd_first_name.Text;
}
if(city.Text != "")
{
    cmd.Parameters.Add("@CITY", SqlDbType.VarChar).Value = pd_first_name.Text;
}

Method 7

If RdFree.Checked = True Then
    Dim nu As String = "NULL"
    UpdPolicys.Append(", AccIDFree = " & nu & " , AccTypeIDFree = " & nu & " ")
End If

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0