vb.net 为sql参数设置默认值null
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16370322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
setting the default value null for sql parameters
提问by ivandinglasan
Upon clicking the add button in vb, it works if you dont leave textbox fields null or empty the code executes well
单击 vb 中的添加按钮后，如果您不将文本框字段留空或为空，则代码执行良好
but when leaving some fields blank it returns error
但是当将某些字段留空时，它会返回错误
Failed to convert parameter value from a String to a Int32
无法将参数值从字符串转换为 Int32
STORED PROC
存储过程
CREATE PROCEDURE AddOfficeEquipmentProfile
(
@OE_ID varchar(11),
@OE_Category char(3) =NULL,
@OE_SubCategory char(3)= NULL,
@OE_Name varchar(35)=NULL,
@OE_User varchar(35)=NULL,
@OE_Brand varchar(15)=NULL,
@OE_Model varchar(35)=NULL,
@OE_Specs varchar(1000)=NULL,
@OE_SerialNo varchar(35)=NULL,
@OE_PropertyNo varchar(35)=NULL,
@OE_MacAddress varchar(100)=NULL,
@OE_Static_IP varchar(15)=NULL,
@OE_Vendor varchar(35)=NULL,
@OE_PurchaseDate smalldatetime,
@OE_WarrantyInclusiveYear int=NULL,
@OE_WarrantyStatus char(2)= NULL,
@OE_Status varchar(15)=NULL,
@OE_Dept_Code char(3)= NULL,
@OE_Location_Code char(8)= NULL,
@OE_Remarks varchar(1000)= NULL
)
AS
INSERT INTO tblOfficeEquipmentProfile (OE_ID, OE_Category, OE_SubCategory, OE_Name, OE_User, OE_Brand, OE_Model, OE_Specs, OE_SerialNo,
OE_PropertyNo, OE_MacAddress, OE_Static_IP, OE_Vendor, OE_PurchaseDate, OE_WarrantyInclusiveYear, OE_WarrantyStatus, OE_Status, OE_Dept_Code,
OE_Location_Code, OE_Remarks )
VALUES (@OE_ID, @OE_Category, @OE_SubCategory, @OE_Name, @OE_User, @OE_Brand, @OE_Model,
@OE_Specs, @OE_SerialNo, @OE_PropertyNo, @OE_MacAddress, @OE_Static_IP, @OE_Vendor, @OE_PurchaseDate, @OE_WarrantyInclusiveYear, @OE_WarrantyStatus,
@OE_Status, @OE_Dept_Code, @OE_Location_Code, @OE_Remarks)
GO
VB.NET CODE
VB.NET 代码
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "AddOfficeEquipmentProfile"
cmd.Parameters.Add("@OE_ID", SqlDbType.VarChar, 11, "oeq-su-999")
cmd.Parameters.Add("@OE_Category", SqlDbType.Char, 3, "COM").Value = DBNull.Value
cmd.Parameters.Add("@OE_SubCategory", SqlDbType.Char, 3, "SU").Value = DBNull.Value
cmd.Parameters.Add("@OE_Name", SqlDbType.VarChar, 35, "adminpmis01").Value = DBNull.Value
cmd.Parameters.Add("@OE_User", SqlDbType.VarChar, 35, "Ivan").Value = DBNull.Value
cmd.Parameters.Add("@OE_Brand", SqlDbType.VarChar, 15, "DELL").Value = DBNull.Value
cmd.Parameters.Add("@OE_Model", SqlDbType.VarChar, 35, "optiplex").Value = DBNull.Value
cmd.Parameters.Add("@OE_Specs", SqlDbType.VarChar, 1000, "dualcore").Value = DBNull.Value
cmd.Parameters.Add("@OE_SerialNo", SqlDbType.VarChar, 35, "sgh5960").Value = DBNull.Value
cmd.Parameters.Add("@OE_PropertyNo", SqlDbType.VarChar, 35, "j7h7h6g6f2").Value = DBNull.Value
cmd.Parameters.Add("@OE_MacAddress", SqlDbType.VarChar, 100, "j7h7:h6g6f2").Value = DBNull.Value
cmd.Parameters.Add("@OE_Static_IP", SqlDbType.VarChar, 15, "192.168.1.5").Value = DBNull.Value
cmd.Parameters.Add("@OE_Vendor", SqlDbType.VarChar, 35, "ADWAYS").Value = DBNull.Value
cmd.Parameters.Add("@OE_PurchaseDate", SqlDbType.DateTime)
cmd.Parameters.Add("@OE_WarrantyInclusiveYear", SqlDbType.Int).Value = DBNull.Value
cmd.Parameters.Add("@OE_WarrantyStatus", SqlDbType.Char, 2, "IN").Value = DBNull.Value
cmd.Parameters.Add("@OE_Status", SqlDbType.VarChar, 15, "Good").Value = DBNull.Value
cmd.Parameters.Add("@OE_Dept_Code", SqlDbType.Char, 3, "ADM").Value = DBNull.Value
cmd.Parameters.Add("@OE_Location_Code", SqlDbType.Char, 8, "ADM_OFC").Value = DBNull.Value
cmd.Parameters.Add("@OE_Remarks", SqlDbType.VarChar, 1000, "ACTIVE").Value = DBNull.Value
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 = dtpPurchaseDate.Value
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()
sqlconn.Close()
回答by Htin Aung
?? operator can help you to check Null value easily.
?? 运算符可以帮助您轻松检查 Null 值。
Example :
例子 ：
cmd.Parameters("@OE_ID").Value = txtOEID.Text ?? DBNull.Value;
cmd.Parameters("@OE_Category").Value = cmbCategory.Text ?? DBNull.Value;
回答by Tim
I think what you're missing (as you've asked a variation on this question several times over the last couple of days is an understanding of how the controls in your form map to the parameters that you associate with your SqlCommand, and in turn how those SqlCommandparameters map to the parameters of your stored procedure. That lack of understanding (and since you're learning, that lack is not a bad thing) coupled with how to handle NULL values is causing you no end of grief.
我认为您缺少什么（因为您在过去几天中多次询问这个问题的变体是了解表单中的控件如何映射到与您的SqlCommand.这些SqlCommand参数映射到您的存储过程的参数。缺乏理解（并且因为您正在学习，这种缺乏并不是一件坏事）再加上如何处理 NULL 值，这会让您感到悲伤无止境。
Rather than give you a single answer to a single question, I'm going to try to explain the overall concept, so you can then hopefully apply it to any number of situations you may encounter while writing code.
我将尝试解释整个概念，而不是给您一个单一问题的单一答案，这样您就可以希望将其应用于您在编写代码时可能遇到的任何数量的情况。
First, you have a collection of TextBoxand ComboBoxcontrols on your form. Both of these controls have a Textproperty - this property alwaysreturns a string, even if you put 12345 or 10/01/2007 in it. This means that you will have to cast (convert) the string you get from TextBox.Textor ComboBox.Textto the appropriate data type for the stored procedure parameter in question (unless its CHAR/VARCHAR or something similar).
首先，您的表单上有一个TextBox和ComboBox控件的集合。这两个控件都有一个Text属性 - 该属性始终返回一个字符串，即使您将 12345 或 10/01/2007 放入其中。这意味着，你将不得不投（转换）你得到字符串TextBox.Text或ComboBox.Text到有问题的存储过程参数的适当的数据类型（除非它的CHAR / VARCHAR或类似的东西）。
When you create your SqlCommandobject, you are adding parameters to the parameters collection. By the way, this is very good practice, and kudos to you for learning it - as it will mitigate SQL injection attacks (not always a high-risk in a WinForm app, but it's a good coding practice to get into.
创建SqlCommand对象时，您正在向参数集合中添加参数。顺便说一句，这是一个很好的实践，感谢您学习它 - 因为它会减轻 SQL 注入攻击（在 WinForm 应用程序中并不总是一个高风险，但这是一个很好的编码实践。
The SqlParameterobjects you add to your SqlCommandneed to match the parameters in the stored procedure, which you also do, so that is good. However, it looks like you may be trying to pass a default value in the fourth parameter, and that's parameter is not for setting default values but for specifying the source column in an UPDATE statement.
SqlParameter您添加到您SqlCommand需要的对象与存储过程中的参数相匹配，您也这样做，这样很好。但是，看起来您可能试图在第四个参数中传递默认值，该参数不是用于设置默认值，而是用于在 UPDATE 语句中指定源列。
Finally you have your stored procedure. None of the parameters are required - so you can safely admit any parameter that does not have a value in the form. Essentially, when you set a default value (NULL or anything else) on a parameter in a stored procedure you can omit that parameter from the call and SQL Server will use the default value you've specified in the declaration of the stored procedure.
最后你有你的存储过程。不需要任何参数 - 因此您可以安全地接受在表单中没有值的任何参数。本质上，当您为存储过程中的参数设置默认值（NULL 或其他任何值）时，您可以从调用中省略该参数，SQL Server 将使用您在存储过程声明中指定的默认值。
You're doing extra work in your code as it is - first you're setting the parameters all to null, then you're overriding that assignment by setting the values equal to the corresponding controls on the form. Which is where you're running into most of your trouble.
您正在代码中做额外的工作 - 首先将参数设置为 null，然后通过将值设置为与表单上的相应控件相等来覆盖该分配。这是您遇到最多麻烦的地方。
As others have suggested on at least a couple of your questions, I would do a check to see if a control has a value - if it does, then add the parameter and its value to the collection (converting as needed). This will reduce the "double" assignment you have.
正如其他人至少对您的几个问题提出的建议一样，我会检查一个控件是否有值 - 如果有，则将参数及其值添加到集合中（根据需要进行转换）。这将减少您的“双重”分配。
So it would look something like this:
所以它看起来像这样：
If txtOEID.Text.Trim() <> "" Then
cmd.Parameters.Add("@OE_ID", SqlDbType.VarChar, 11).Value = txtOEID.Text.Trim()
End If
If cmbCategory.Text.Trim() <> "" Then
cmd.Parameters.Add("@OE_Category", SqlDbType.Char, 3).Value = cmbCategory.Text.Trim()
End If
And so on. When you have a non VARCHAR/CHAR parameter, you'll need to do a little additional work (since you're using a DateTimePickerfor the purchase date, the Valueproperty is already a DateTimeso you don't need to worry about it):
等等。当你有一个非 VARCHAR/CHAR 参数时，你需要做一些额外的工作（因为你使用 aDateTimePicker作为购买日期，Value属性已经是 aDateTime所以你不需要担心它）：
If IsNumeric(txtWarrantyInclusiveYear.Text) Then
cmd.Parameters.Add("@OE_WarrantyInclusiveYear", SqlDbType.Int).Value = Integer.Parse(txtWarrantyInclusiveYear.Text)
End If
In the case above, I suggest using IsNumericto increase your chances of not accidently trying to parse a non-numeric value (since you're using VS 2003/.NET 1.1 TryParseis not available).
在上述情况下，我建议使用IsNumeric来增加您不意外地尝试解析非数字值的机会（因为您使用的是 VS 2003/.NET 1.1TryParse不可用）。
In a nutshell, when you creating parameters in your application, you need to be aware of the data type that is being passed in from the user, and the data type that the stored procedure is expecting. If they don't match, you'll need to convert the user input into the correct datatype for the stored procedure when you add the parameter to the collection.
简而言之，当您在应用程序中创建参数时，您需要了解从用户传入的数据类型以及存储过程期望的数据类型。如果它们不匹配，则需要在将参数添加到集合时将用户输入转换为存储过程的正确数据类型。
If a parameter has a default value specified in the stored procedure, then you can simply skip adding the parameter if the user did not supply a value. Though in the case of NULL, make sure that the destination column you're inserting into is marked as NULLABLE.
如果参数在存储过程中指定了默认值，那么如果用户没有提供值，您可以简单地跳过添加参数。尽管在 NULL 的情况下，请确保您要插入的目标列标记为 NULLABLE。
And I strongly suggest you dump VS 2003 and avail yourself of the Expressversion of VS 2012, as you are missing out on a whole bunch of features that will make your life easier as a programmer.
我强烈建议您放弃 VS 2003 并使用VS 2012的Express版本，因为您会错过一大堆功能，这些功能将使您的程序员生活更轻松。
If you need clarification on anything, please ask.
如果您需要澄清任何事情，请询问。
回答by John Woo
Try using Inline IFfor all the values.
尝试IF对所有值使用内联。
cmd.Parameters("@OE_ID").Value = If(txtOEID.Text.Trim().Length > 0, txtOEID.Text, DBNull.Value)
What it does is it trims the string first then checks its length. If the length is greater than zero then pass the string on the truepart otherwise pass DBNull.Value.
它的作用是先修剪字符串，然后检查其长度。如果长度大于零，则在true部件上传递字符串，否则传递DBNull.Value。
回答by Tomas Kirda
Since issue converting string to int and the only int I see is @OE_WarrantyInclusiveYearparameter. In your code you can safely try to convert to int.
由于问题将字符串转换为 int 并且我看到的唯一 int 是@OE_WarrantyInclusiveYear参数。在您的代码中，您可以安全地尝试转换为 int。
Dim year As Integer
If Integer.TryParse(txtWarrantyInclusiveYear.Text, year) Then
cmd.Parameters("@OE_WarrantyInclusiveYear").Value = year
End If
