Issue with sql query (not picking up parameter)

Question

I have the following code in c# for sql insert:

using (DM dbManager = new DM())
{
    dbManager.Command.CommandText = @"Insert into TransactionLog(TransactionNumber,Amount
    ,studentID) Values (@trnumber,@amount,@studentID) ; SELECT SCOPE_IDENTITY() ;";
    dbManager.Command.Parameters.AddWithValue("@trnumber", txnref);
    dbManager.Command.Parameters.AddWithValue("@amount", amount);
    dbManager.Command.Parameters.AddWithValue("@studentID", 
                                           Membership.GetUser().ProviderUserKey.ToString());

    id = Convert.ToInt32(dbManager.Command.ExecuteScalar());
}

But failing to understand why am i getting this error:

 System.Data.SqlClient.SqlException: The parameterized query '(@trnumber 
 nvarchar(4000),@amount nvarchar(5),@studentID nvarcha' expects the 
 parameter '@trnumber', which was not supplied.

Because the parameter is clearly in the query i tried changing the name of parameter same issue.

Any suggestions?

What is DM exactly? Can you please show it's structure as well? — Soner Gönül
– Soner Gönül, Commented May 28, 2015 at 11:01
@SonerGönül that is a class to care of SqlConnection etc that is working fine i have a few queries above the this one a lot till i reach this point. — confusedMind
– confusedMind, Commented May 28, 2015 at 11:02
If txnref is null, then the AddWithValue will mean that the SqlValue property of the parameter is null, and it is as if the parameter is not passed (remember null in c# and NULL in the DB are not the same thing). I would personally use the more strongly type method - dbManager.Command.Parameters.Add("@trnumber", SqlDbType.NVarChar, 4000).Value = txnref ?? string.Empty — GarethD
– GarethD, Commented May 28, 2015 at 11:07
the one of the variable is getting null..so it should specify with DBNull.Value..i hope the below answer can sort out the issue — Sachu
– Sachu, Commented May 28, 2015 at 11:08
Or if it should be DBNull rather than an empty string - dbManager.Command.Parameters.Add("@trnumber", SqlDbType.NVarChar, 4000).Value = string.IsNullOrEmpty(txnref) ? (object)DBNull.Value : (object)txnref; — GarethD
– GarethD, Commented May 28, 2015 at 11:14

wiretext · Accepted Answer · 2015-05-28 11:03:48Z

1

if (String.IsNullOrEmpty(txnref))
            {
                command.Parameters.AddWithValue("@trnumber", DBNull.Value); 
            }
            else
                command.Parameters.AddWithValue("@trnumber", txnref);

answered May 28, 2015 at 11:03

wiretext

3,35216 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

confusedMind Over a year ago

Ok let me check it should have been null ... but lets c

Sachu Over a year ago

@SonerGönül i hope this answer can sort out the issue..if no data is there in the string we should pass DBNull.Value to the parameter to make it database null value

RASKOLNIKOV · Accepted Answer · 2015-05-28 11:11:48Z

0

Try to put DBNull.Value if value is empty it will work! Also check your column accepts NULL

answered May 28, 2015 at 11:11

RASKOLNIKOV

7682 gold badges9 silver badges22 bronze badges

Collectives™ on Stack Overflow

Issue with sql query (not picking up parameter)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related