Must Declare Scalar Variable

Question

When I attempt to execute the following non query sql command in my application I always get the error message -

Must declare the scalar variable '@RateID'

I'm a bit at a loss as to why I receive this error. Here is my code:

string sqlText = "INSERT INTO tblRates (RateID, IPName, RateName, Rate, DateFrom, DateTo, Active) " +
                 "VALUES (@RateID, @IPName, @RateName, @Rate, @DateFrom, @DateTo, @Active);";

SqlCommand sqlCom = new SqlCommand(sqlText);
sqlCom.Parameters.Add("@RateID", SqlDbType.Int);
sqlCom.Parameters.Add("@IPName", SqlDbType.Text);
sqlCom.Parameters.Add("@RateName", SqlDbType.Text);
sqlCom.Parameters.Add("@Rate", SqlDbType.Decimal);
sqlCom.Parameters.Add("@DateFrom", SqlDbType.Date);
sqlCom.Parameters.Add("@DateTo", SqlDbType.Date);
sqlCom.Parameters.Add("@Active", SqlDbType.Bit);

this._rateID = NextID();

sqlCom.Parameters["@RateID"].Value = this._rateID;

if (this._ipName == "All")
{
   sqlCom.Parameters["@IPName"].Value = DBNull.Value;
}
else
{
   sqlCom.Parameters["@IPName"].Value = this._ipName;
}

sqlCom.Parameters["@RateName"].Value = this._rateName;
sqlCom.Parameters["@Rate"].Value = this._rate;
sqlCom.Parameters["@DateFrom"].Value = this._dateFrom;
sqlCom.Parameters["@DateTo"].Value = this._dateTo;

this._active = true;
sqlCom.Parameters["@Active"].Value = this._active;

cSqlQuery cQ = new cSqlQuery(sqlText, "non query");

When I step through line by line, I see the parameter @RateID is successfully added to the sqlCommand object, the method NextID correctly returns an integer value which is accepted as the Value of the @RateID parameter, but when I execute non query on the SqlCommand I get the aforementioned error.

Any help much be much appreciated.

You're setting up a big `SqlCommand` object - but it seems as if you're straightly executing the `sqlText` only - you don't seem to use the `sqlCom` object at all ..... — marc_s, May 15 '12 at 14:07
It would really help if you'd show *where* you're getting the exception... — Jon Skeet, May 15 '12 at 14:08

score 6 · Accepted Answer · answered May 15 '12 at 14:09

You're setting up the sqlCom object - and the code looks fine.

But then: it seems you're just executing the SQL command here:

cSqlQuery cQ = new cSqlQuery(sqlText, "non query");

not using all that setup code you had before! Why?

The parameters and all are contained in the sqlCom object - but your cSqlQuery doesn't seem to actually look at that object - just the SQL statement itself...

What happens if you use this line instead:

sqlCom.ExecuteNonQuery();

Does this work without exception?

Yes - that was a clear example of not seeing the wood for the trees - thanks for that. — PJW, May 15 '12 at 14:20

score 0 · Answer 2 · answered May 15 '12 at 14:13

0

You seem to be passing sqlText to your cSqlQuery object, but I believe you need to pass the command (sqlCom).

I don't know exactly how the cSqlQuery works, but passing the sqlText is no good.

answered May 15 '12 at 14:13

James Osborn

1,275
7
12

Must Declare Scalar Variable

2 Answers2

Linked