How to pass integer value 0 as SQL parameter.

Question

1.00/5 (1 vote)

See more:

i want to pass int value 0 as SQL parameter. but in execution of method it become null. but i want to insert 0.

i also tried:

C#

SQLPmtr[2] = new SqlParameter("@DebitNinUnit", SqlDbType.Int, 0);

but, getting same error.

What I have tried:

C#

SqlParameter[] SQLPmtr = null;
SQLPmtr = new SqlParameter[6];
                SQLPmtr[0] = new SqlParameter("@UserID", UserID);
                SQLPmtr[1] = new SqlParameter("@Description", "New Account Created");
                SQLPmtr[2] = new SqlParameter("@DebitNinUnit", 0);
                SQLPmtr[3] = new SqlParameter("@CreditNinUnit",0);
                SQLPmtr[4] = new SqlParameter("@TransactionDate", CurrentDateTime);
                SQLPmtr[5] = new SqlParameter("@ClosingNinUnitBalance", 0);
                MyCommonFunction.ExecuteQuery("Insert Into TblAccountDetail(UserID,Description,DebitNinUnit,CreditNinUnit,TransactionDate,ClosingNinUnitBalance) Values(@UserID,@Description,@DebitNinUnit,@CreditNinUnit,@TransactionDate,@ClosingNinUnitBalance)",connection, SQLPmtr);

// My function to execute query

C#

public void ExecuteQuery(string CmdString, SqlConnection connection, SqlParameter[] SQLPmtr = null)
        {
            cmd = new SqlCommand(CmdString, connection, transaction);
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 120;
            if (SQLPmtr != null)
            {
                cmd.Parameters.Clear();
                for (int i = 0; i < SQLPmtr.Length; i++)
                {
                    cmd.Parameters.Add(SQLPmtr[i]);
                }
            }
            cmd.ExecuteNonQuery(); 
        }

Posted 4-Feb-20 19:54pm

kantagrawal

Updated 6-Feb-20 4:21am

Herman<T>.Instance

v2

Add a Solution

Comments

Maciej Los 5-Feb-20 2:01am

getting same error - What kind of error?

Herman<T>.Instance 5-Feb-20 3:23am

One tip that does not solve your problem:
Since you use SqlParameter[] SQLPmtr
You can state:
if (SqlPmtr != null && SQLPmtr.Length > 0) cmd.Parameters.AddRange(SQLPmtr);

phil.o 5-Feb-20 4:16am

0 is a perfectly valid integer value. You do not have to pass differently from any other non-zero valid integer value.
So we get back to the issue and what Maciej asked you: what is this error you are talking about but not describing? Please improve your question and provide the error message.

2 solutions

Solution 1

As far as i see, you're passing proper value within a parameter:

C#

SQLPmtr[2] = new SqlParameter("@DebitNinUnit", 0);

There might be a set of reasons that you get null on database side, instead of correct value.

Accordingly to the comment posted by digimanus, you can improve your method, by:
- returning the number of rows affected,
- adding try catch block for error handling.
See:

C#

public int ExecuteQuery(string CmdString, SqlConnection connection, SqlParameter[] SQLPmtr = null)
{
	int retVal = 0;
	try
	{
		cmd = new SqlCommand(CmdString, connection, transaction);
		//check connection state!
	    if(connection.State != ConnectionState.Open) connection.Open();
		cmd.CommandType = CommandType.Text;
	    cmd.CommandTimeout = 120;
		//add parameters
	    if (SQLPmtr != null && SQLPmtr.Length > 0) 
			cmd.Parameters.AddRange(SQLPmtr);
		//	        
	    retVal = cmd.ExecuteNonQuery(); 
	}
	catch(SqlException ex)
	{
		//display error message!
	}
	return retVal;
}

Posted 5-Feb-20 0:57am

Maciej Los

Comments

kantagrawal 6-Feb-20 0:02am

we are getting error on int type paramenter:

The parameterized query '(@UserID int,@Description nvarchar(19),@DebitNinUnit bigint,@Cre' expects the parameter '@DebitNinUnit', which was not supplied.

kantagrawal 6-Feb-20 0:11am

actually we are inserting 0 in sql parameter. but on execution of query this integer value become null.

Maciej Los 6-Feb-20 5:36am

Seems, that on execution you're passing null, where @DebitNinUnit expects numeric value. See:
The parameterized query expects the parameter which was not supplied c# SQL - Stack Overflow[^]
asp.net - The parameterized query which was not supplied - Stack Overflow[^]

Richard Deeming 6-Feb-20 10:22am

Actually, it's a nasty overload resolution issue. The literal 0 is implicitly cast to the SqlDbType enum, rather than being boxed to an object, so it's calling the wrong overload. :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-02-06T04:21:00

You're hitting an overload resolution problem.

C#

new SqlParameter("@DebitNinUnit", 0)

This calls the SqlParameter(string, SqlDbType) overload, when you were expecting it to call the SqlParameter(string, object) overload.

A literal 0 is implicitly convertible to any Enum type, and the compiler decides that this implicit conversion is a better match than boxing the int into an object.

If you were passing an int variable instead, then this would work as expected:

C#

static readonly int Zero = 0;
...
new SqlParameter("@DebitNinUnit", Zero) // Calls the (string, object) overload correctly.

However, if you passed a const, this would still call the wrong overload.

Demonstration: Enum Overload Resolution | C# Online Compiler | .NET Fiddle[^]

As to your second option:

C#

new SqlParameter("@DebitNinUnit", SqlDbType.Int, 0)

In this case, the 0 is the Size of the parameter, not its Value.

You have three options:

Pass a variable instead of a literal 0;
Box the literal zero before calling the constructor;
Set the Value property instead of passing the value to the constructor;

C#

// Option 1:
static readonly int Zero = 0;
...
new SqlParameter("@DebitNinUnit", Zero)

// Option 2:
new SqlParameter("@DebitNinUnit", (object)0)

// Option 3:
new SqlParameter("@DebitNinUnit", SqlDbType.Int) { Value = 0 }