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

codingbadger · Accepted Answer · 2011-01-18 10:44:31Z

25

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

edited Jan 18, 2011 at 10:44

answered Jan 18, 2011 at 10:06

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jammer Over a year ago

I just tested above and it still doesn't insert NULL, see edited question above, thanks.

Jammer Over a year ago

This does work actually, my fault, i was referencing the wrong form field in my edited code, thanks Barry.

Cary Bondoc · Accepted Answer · 2015-12-25 11:12:14Z

3

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

answered Dec 25, 2015 at 11:12

Cary Bondoc

3,0164 gold badges41 silver badges61 bronze badges

1 Comment

user8953824 Over a year ago

This was also how I had to solve my problem. For some reason DBNull.Value doesn't work with DateTime fields

YvesR · Accepted Answer · 2011-01-18 10:04:15Z

1

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

answered Jan 18, 2011 at 10:04

YvesR

6,2829 gold badges46 silver badges75 bronze badges

1 Comment

Jammer Over a year ago

Its a form item, if nothing is entered it equals "" not NULL.

m.edmondson · Accepted Answer · 2011-01-18 10:09:48Z

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

Andrew Barber · Accepted Answer · 2012-09-25 05:03:13Z

0

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

edited Sep 25, 2012 at 5:03

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

answered Sep 3, 2012 at 5:57

AmollJ.

1

Comments

micbobo · Accepted Answer · 2015-05-12 20:39:29Z

0

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;
}

edited May 12, 2015 at 20:39

micbobo

8727 silver badges25 bronze badges

answered Jan 18, 2011 at 10:21

Surya

591 bronze badge

Comments

The_Black_Smurf · Accepted Answer · 2016-01-15 12:45:02Z

0

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

edited Jan 15, 2016 at 12:45

The_Black_Smurf

5,29714 gold badges58 silver badges80 bronze badges

answered Jan 14, 2016 at 22:48

Mohamed Mo. Abosaleh

1

Collectives™ on Stack Overflow

How to insert NULL into database if form field is empty

7 Answers 7

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related