vb.net 如何将空值发送到存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17166450/
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
How to send null value to a stored procedure?
提问by Joe Yan
My stored procedure looks like this:
我的存储过程如下所示：
CREATE PROCEDURE [dbo].[insertCompList_Employee]
@Course_ID int,
@Employee_ID int,
@Project_ID int = NULL,
@LastUpdateDate datetime = NULL,
@LastUpdateBy int = NULL
AS
BEGIN
SET NOCOUNT ON
INSERT INTO db_Competency_List(Course_ID, Employee_ID, Project_ID, LastUpdateDate, LastUpdateBy)
VALUES (@Course_ID, @Employee_ID, @Project_ID, @LastUpdateDate, @LastUpdateBy)
END
My asp.net vb code behind as follows:
我的asp.net vb代码后面如下：
dc2.insertCompList_Employee(
rdl_CompList_Course.SelectedValue,
RadListBox_CompList_Select.Items(i).Value.ToString,
"0",
DateTime.Now,
HttpContext.Current.Session("UserID")
)
I want to insert a nullvalue for Project_IDinstead of 0
我想插入一个null值Project_ID而不是0
I had tried NULL, 'NULL'but it returns error.
我试过NULL，'NULL'但它返回错误。
采纳答案by Joe Yan
Finally i got the solution...
最后我得到了解决方案......
i use Nullable Type for the project_id. Declare it as follow:
我使用可空类型作为 project_id。声明如下：
Dim pid As System.Nullable(Of Integer)
and then code behind to insert data via store procedure
然后后面的代码通过存储过程插入数据
dc2.insertCompList_Employee(
rdl_CompList_Course.SelectedValue,
RadListBox_CompList_Select.Items(i).Value.ToString,
pid,
DateTime.Now,
HttpContext.Current.Session("UserID"))
回答by Darren
Use the built in value: DBNull.Value
使用内置值： DBNull.Value
回答by PatFromCanada
I don't see the code where you are converting the values into parameters, but just don't add the parameter to the parameter collection if it should be null. It works fine whenever there is a default in the stored proc, in your case null.
我没有看到您将值转换为参数的代码，但是如果参数应该为空，请不要将参数添加到参数集合中。只要存储过程中有默认值，它就可以正常工作，在您的情况下为 null。
if projectid <> 0 then
cmd.addwithvalue("@ProjectId",projectid)
end if
