更新时允许使用空值，使用SQL Server存储过程将记录插入数据库 [英] Allowing null values when updating, inserting records to database using SQL Server stored procedure

问题描述

    Dim cmd As SqlCommand = sqlconn.CreateCommand
    sqlconn.Open()
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "udpateELSEinsertEquipmentProfile"

    'declare the variables
    cmd.Parameters.Add("@OE_ID", SqlDbType.VarChar, 11, "oeq-su-999")
    cmd.Parameters.Add("@OE_Category", SqlDbType.Char, 3, "COM")
    cmd.Parameters.Add("@OE_SubCategory", SqlDbType.Char, 3, "SU")
    cmd.Parameters.Add("@OE_Name", SqlDbType.VarChar, 35, "adminpmis01")
    cmd.Parameters.Add("@OE_User", SqlDbType.VarChar, 35, "Ivan")
    cmd.Parameters.Add("@OE_Brand", SqlDbType.VarChar, 15, "DELL")
    cmd.Parameters.Add("@OE_Model", SqlDbType.VarChar, 35, "optiplex")
    cmd.Parameters.Add("@OE_Specs", SqlDbType.VarChar, 1000, "dualcore")
    cmd.Parameters.Add("@OE_SerialNo", SqlDbType.VarChar, 35, "sgh5960")
    cmd.Parameters.Add("@OE_PropertyNo", SqlDbType.VarChar, 35, "j7h7h6g6f2")
    cmd.Parameters.Add("@OE_MacAddress", SqlDbType.VarChar, 100, "j7h7:h6g6f2")
    cmd.Parameters.Add("@OE_Static_IP", SqlDbType.VarChar, 15, "192.168.1.5")
    cmd.Parameters.Add("@OE_Vendor", SqlDbType.VarChar, 35, "ADWAYS")
    cmd.Parameters.Add("@OE_PurchaseDate", SqlDbType.SmallDateTime)
    cmd.Parameters.Add("@OE_WarrantyInclusiveYear", SqlDbType.Int)
    cmd.Parameters.Add("@OE_WarrantyStatus", SqlDbType.Char, 2, "IN")
    cmd.Parameters.Add("@OE_Status", SqlDbType.VarChar, 15, "Good")
    cmd.Parameters.Add("@OE_Dept_Code", SqlDbType.Char, 3, "ADM")
    cmd.Parameters.Add("@OE_Location_Code", SqlDbType.Char, 8, "ADM_OFC")
    cmd.Parameters.Add("@OE_Remarks", SqlDbType.VarChar, 1000, "ACTIVE")
    cmd.Parameters("@OE_ID").Value = txtOEID.Text
    cmd.Parameters("@OE_Category").Value = cmbCategory.Text
    cmd.Parameters("@OE_SubCategory").Value = cmbSubCategory.Text
    cmd.Parameters("@OE_Name").Value = txtName.Text
    cmd.Parameters("@OE_User").Value = txtUser.Text
    cmd.Parameters("@OE_Brand").Value = cmbBrand.Text
    cmd.Parameters("@OE_Model").Value = cmbModel.Text
    cmd.Parameters("@OE_Specs").Value = txtSpecs.Text
    cmd.Parameters("@OE_SerialNo").Value = txtSerialNo.Text
    cmd.Parameters("@OE_PropertyNo").Value = txtPropertyNo.Text
    cmd.Parameters("@OE_MacAddress").Value = txtMacAddress.Text
    cmd.Parameters("@OE_Static_IP").Value = txtStaticIp.Text
    cmd.Parameters("@OE_Vendor").Value = txtVendor.Text
    cmd.Parameters("@OE_PurchaseDate").Value = txtPurchaseDate.Text
    cmd.Parameters("@OE_WarrantyInclusiveYear").Value = txtWarrantyInclusiveYear.Text
    cmd.Parameters("@OE_WarrantyStatus").Value = txtWarrantyStatus.Text
    cmd.Parameters("@OE_Status").Value = txtStatus.Text
    cmd.Parameters("@OE_Dept_Code").Value = cmbDeptCode.Text
    cmd.Parameters("@OE_Location_Code").Value = cmbLocationCode.Text
    cmd.Parameters("@OE_Remarks").Value = txtRemarks.Text

    cmd.ExecuteNonQuery()

    MsgBox("Successfully Added Equipment Profile")

    sqlconn.Close()

在没有空值的情况下，我的插入和更新存储过程正在工作，但是当我需要使某些列真正为空值时呢?

My insert and update stored procedures are working when there is no null values, but how about when I need to make some columns really null values?

我正在vb.net 2003中使用SQL Server存储过程

I'm using SQL Server stored procedures in vb.net 2003

有关代码示例的任何建议

Any suggestions about codes example

推荐答案

您可以将DBNull.Value用作存储过程的参数.确保您的表允许字段的空值.

You can use DBNull.Value as the parameter to your stored procedure. Ensure that your table allows null value for the fields.

例如，如果您希望OE_Brand成为null，则可以执行以下操作:

For example if you wanted OE_Brand to be null you can do:

cmd.Parameters("@OE_Brand").Value = DBNull.Value

这篇关于更新时允许使用空值，使用SQL Server存储过程将记录插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！