CODEWITHSUNDEEP
sqlasp.net.netsql-servervb.net
Joe Yan
Joe Yan

Reputation: 2095

How to send null value to a stored procedure?

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:

    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 null value for Project_ID instead of 0

I had tried NULL, 'NULL' but it returns error.

Upvotes: 3

Views: 8641

Answers (3)

Joe Yan
Joe Yan

Reputation: 2095

Finally i got the solution...

i use Nullable Type for the project_id. Declare it as follow:

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

Upvotes: 1

PatFromCanada
PatFromCanada

Reputation: 2788

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.

if projectid <> 0 then
   cmd.addwithvalue("@ProjectId",projectid)
end if

Upvotes: 1

Darren
Darren

Reputation: 70806

Use the built in value: DBNull.Value

http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

Upvotes: 9

Related Questions