Alex Rivera | Logout

How to insert NULL into database if form field is empty

Asked 2011-01-18T09:59:08.627
18

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

Edit
Report

1 Answer

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 2011-01-18T10:04:15.417

Your Answer