Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to 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:

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


but, getting same error.

What I have tried:

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
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
Updated 6-Feb-20 5:21am
v2
Comments
Maciej Los 5-Feb-20 2:01am
   
getting same error - What kind of error?
digimanus 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.
Rate this:
Please Sign up or sign in to vote.

Solution 1

As far as i see, you're passing proper value within a parameter:
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:
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;
}
   
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
   
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. :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

You're hitting an overload resolution problem.
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:
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:
new SqlParameter("@DebitNinUnit", SqlDbType.Int, 0)
In this case, the 0 is the Size of the parameter, not its Value.


You have three options:
  1. Pass a variable instead of a literal 0;
  2. Box the literal zero before calling the constructor;
  3. Set the Value property instead of passing the value to the constructor;

// 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 }
   
Comments
phil.o 6-Feb-20 10:26am
   
Impressive. What a bug!
Maciej Los 6-Feb-20 12:04pm
   
Great and awesome answer!

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100