CODEWITHSUNDEEP
c#sql-serverasp.net-core
Questieme
Questieme

Reputation: 1003

Passing null parameter to the stored procedure

In my Create method, I am using a stored procedure to INSERT INTO my SQL Server database. Sometimes, fields such as Comment will be left blank. However, it does not work as I wished.

Firstly, this is how my method looks like:

using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "CreateTask";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                .....................

                parameter = new SqlParameter
                {
                    ParameterName = "@Condition",
                    Value = task.Condition,
                    SqlDbType = SqlDbType.NVarChar
                };
                command.Parameters.Add(parameter);
                .....................

When task.Condition is null, command.ExecuteNonQuery(); gets the following error:

: 'Procedure or function 'CreateTask' expects parameter '@Condition', which was not supplied.'

However, the columns in the table are set to allow null values.

enter image description here

The stored procedure also looks like this:

    ALTER PROCEDURE [dbo].[CreateTask]
    @Name        NVARCHAR(50),
    @IsGate   BIT,
    @Condition Varchar(450),
    @Precondition       Varchar(450),
    @Comments       Varchar(450),
    @StartDate       DateTime,
    @EndDate       DateTime,
    @AssignedTo    Nvarchar(450),
    @PhaseId int 
AS
BEGIN
    Insert Into dbo.Tasks (Name, IsGate, Condition, Precondition, Comments, StartDate, EndDate, AssignedTo, PhaseId, InProgress, Finished, Aborted) Values (@Name, @IsGate, @Condition, @Precondition, @Comments, @StartDate, @EndDate, @AssignedTo, @PhaseId, '0', '0', '0')
END

Therefore, what should I tweak in order to allow the stored procedure to get null values?

Upvotes: 0

Views: 2862

Answers (2)

Ramesh Chandan
Ramesh Chandan

Reputation: 1

If Condition , data type is used as string in C#, then while passing this parameter as null to the procedure will give this error.

So better to user some ternary operator like this

 com.Parameters.Add("@Condition", SqlDbType.VarChar).Value =                         
                    (object.Condition== null) ? string.Empty : object.Condition;

I was also facing the same issue, but DB.Null did not work in my case.

My case is like:

 com.Parameters.Add("@RentalCounterProduct1EquipmentCode", SqlDbType.VarChar).Value =                         
                    (obgKafkaStagingRentalSource.RentalCounterProduct1EquipmentCode == null) ? string.Empty : obgKafkaStagingRentalSource.RentalCounterProduct1EquipmentCode;

Upvotes: -1

Crowcoder
Crowcoder

Reputation: 11514

Try this to assign DBNull.Value to the SqlParameter if the data is null and you want to insert null into the database:

parameter = new SqlParameter
{
    ParameterName = "@Condition",
    Value = (object)task.Condition ?? DBNull.Value,
    SqlDbType = SqlDbType.NVarChar
};

Upvotes: 2

Related Questions