Must declare the scalar variable "@historyId" exception

Question

I have a working raw Sql command execution

using (var cmd = SqlUtils.CreateSqlCommand(cmdText, sqlConn))
    {
      cmd.Parameters.Add(new SqlParameter("historyId", SqlDbType.Int)).Value = AAverage.CashFlowRelevantHistoryId;
      cmd.Parameters.Add(new SqlParameter("nodeId", SqlDbType.Int)).Value = AAverage.CashNodeId;
      cmd.Parameters.Add(new SqlParameter("denomId", SqlDbType.Int)).Value = AAverage.DenominationId;
      cmd.Parameters.Add(new SqlParameter("dayId", SqlDbType.Int)).Value = AAverage.CashFlowDayCategoryId;
      cmd.Parameters.Add(new SqlParameter("hour", SqlDbType.Int)).Value = AAverage.Hour;
      cmd.Parameters.Add(new SqlParameter("type", SqlDbType.Int)).Value = AAverage.ValueType;
      cmd.Parameters.Add(new SqlParameter("average", SqlDbType.Real)).Value = AAverage.AverageDerivative;

      using (var reader = cmd.ExecuteReader())
        if (reader.HasRows && reader.Read())
        {
          IDataRecord record = (IDataRecord)reader;
          AAverage.Id = record.GetInt32ByName("Id");
        }
    }

but if I've changed it to use SqlQuery method, I get the error in the subject:

  StringBuilder cmdText = new StringBuilder();
  cmdText.AppendLine("INSERT INTO CashFlowAverageDerivatives");
  cmdText.AppendLine("  (CashFlowRelevantHistoryId, CashNodeId, DenominationId, CashFlowDayCategoryId, Hour, ValueType, AverageDerivative)");
  cmdText.AppendLine("VALUES");
  cmdText.AppendLine("  (@historyId, @nodeId, @denomId, @dayId, @hour, @type, @average)");
  cmdText.AppendLine("SELECT Id FROM CashFlowAverageDerivatives WHERE @@ROWCOUNT > 0 and Id = scope_identity()");
  AAverage.Id = ADbContext.Database.SqlQuery<int>(
    cmdText.ToString(),
    new SqlParameter("historyId", SqlDbType.Int).Value = AAverage.CashFlowRelevantHistoryId,
    new SqlParameter("nodeId", SqlDbType.Int).Value = AAverage.CashNodeId,
    new SqlParameter("denomId", SqlDbType.Int).Value = AAverage.DenominationId,
    new SqlParameter("dayId", SqlDbType.Int).Value = AAverage.CashFlowDayCategoryId,
    new SqlParameter("hour", SqlDbType.Int).Value = AAverage.Hour,
    new SqlParameter("type", SqlDbType.Int).Value = AAverage.ValueType,
    new SqlParameter("average", SqlDbType.Real).Value = AAverage.AverageDerivative
  ).First<int>();

and I have no idea why. If I tried to rename "historyId" in Sql text to something else, exception reports this new name. It seems like parameter definition was missing or misspelled, but list of parameters has been copy&pasted, VS IDE search command finds it, prefix "@" does not matter...

Sure I can live with my working version, but I'm learning EF and it tease me I'm not able to solve this

I edited the tags, the question has nothing to do with entity framework and code first. — Yuliam Chandra, Jul 30 '14 at 07:47
I don't agree: sqlcommand & sqlparameters per se works perfectly. But with the same parameter list with conjuction of DbSet.SqlQuery() method does not. And DbSet class **is** EF — pf1957, Jul 30 '14 at 07:52
Sorry I didn't see any DbSet in the question, you can edit the question and edit the tag again.. — Yuliam Chandra, Jul 30 '14 at 07:54

Yuliam Chandra · Accepted Answer · 2014-07-30T08:15:19.167

You were sending the sql parameter as value directly.

When you did this

new SqlParameter("historyId", SqlDbType.Int).Value = AAverage.CashFlowRelevantHistoryId

You actually sent a value of AAverage.CashFlowRelevantHistoryId, not a SqlParameter.

Try changing it with this and see if it works.

AAverage.Id = ADbContext.Database.SqlQuery<int>(
  cmdText.ToString(),
  new SqlParameter("historyId", AAverage.CashFlowRelevantHistoryId),
  new SqlParameter("nodeId", AAverage.CashNodeId),
  new SqlParameter("denomId", AAverage.DenominationId),
  new SqlParameter("dayId", AAverage.CashFlowDayCategoryId),
  new SqlParameter("hour", AAverage.Hour),
  new SqlParameter("type", AAverage.ValueType),
  new SqlParameter("average", AAverage.AverageDerivative)
).First<int>();

Or this

AAverage.Id = ADbContext.Database.SqlQuery<int>(
  cmdText.ToString(),
  new SqlParameter("historyId", SqlDbType.Int) { Value = AAverage.CashFlowRelevantHistoryId },
  new SqlParameter("nodeId", SqlDbType.Int) { Value = AAverage.CashNodeId },
  new SqlParameter("denomId", SqlDbType.Int) { Value = AAverage.DenominationId },
  new SqlParameter("dayId", SqlDbType.Int) { Value = AAverage.CashFlowDayCategoryId },
  new SqlParameter("hour", SqlDbType.Int) { Value = AAverage.Hour },
  new SqlParameter("type", SqlDbType.Int) { Value = AAverage.ValueType },
  new SqlParameter("average", SqlDbType.Real) { Value = AAverage.AverageDerivative }
).First<int>();

Thanx, I missed that in my working case the Add() method added instance of parameter into list, wherease with SqlQuery method does not. Finally, You were right it has nothing to do with Entity Framework. I'll remove tags as you suggested — pf1957, Jul 30 '14 at 08:25

Must declare the scalar variable "@historyId" exception

1 Answers1