如果表单字段为空，如何将NULL插入数据库

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

我有一个表单和存储过程插入表单中的数据。它工作正常，除非如果没有填充字段，它不会在SQL中插入NULL，它会插入“”。

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?

我尝试了几种不同的方法，但似乎没有插入NULL，下面的一个仍然插入“”，有人能指出我正确的方向吗？

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:

但它仍然没有插入NULL所以我测试了这个：

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

因此，如果我在address1字段中没有输入任何内容，它应该向屏幕写入NULL，但它总是写入NOT NULL。空表格字段等于什么？在经典ASP中它总是“”。

7 个解决方案

#1

You need to use DBNull.Value

您需要使用DBNull.Value

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

#2

why you even set it if it is null?

为什么你甚至设置它，如果它是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

#3

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

在我的情况下使用'Nothing'解决了这个问题。像这样使用它

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

#4

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

我认为你的问题是frmFirstName是一个字符串而一个字符串不能代表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

#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

希望这可能会有所帮助

#6

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

创建存储过程时，将这些列设置为null，可以为null ..就像

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

然后在代码中不添加那些为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;
}

#7

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