如果表单字段为空，如何将NULL插入数据库 [英] How to insert NULL into database if form field is empty

问题描述

我有一个表单和存储过程，可以从表单中插入数据.它工作正常，除了如果未填充字段不会在SQL中插入 NULL 而不是在SQL中插入" .

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

我尝试了几种不同的方法，但是似乎都没有插入 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?

这是代码的必需部分，如果您需要更多，请告诉我.

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

编辑

我测试了以下代码:

If pd_first_name.Text = "" Then
            frmFirstName = DBNull.Value
        Else
            frmFirstName = pd_first_name.Text
        End If

但是它仍然没有插入 NULL ，所以我对此进行了测试:

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

因此，如果我在 address1 字段中不输入任何内容，则它应该在屏幕上写入 NULL ，但始终会写入 NOT NULL .空表格字段等于什么?在经典的ASP中，它始终是" .

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

推荐答案

您需要使用 DBNull.Value

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

这篇关于如果表单字段为空，如何将NULL插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！