Why with Dapper I get a declare the scalar variable error?

Question

Why with Dapper I get a declare the scalar variable error?

c# dapper parameterized-query sql

Question

I am experiencing issues when trying to used a paramaterized query in Dapper. I have found a number of other users with similar issues but have not been able to resolve the issue.

The code

    public User GetUser(int employeeId)
    {
        var args = new
        {
            EmployeeId = employeeId
        };

        const string sql = @"
                    select 
                        first_name 'FirstName', 
                        last_name 'LastName'
                    from 
                        users 
                    where 
                        employee_id = @EmployeeId 
                ";

        using (var con = MakeConnection())
        {
            var r = con.Query<User>(sql, args);
            return r.FirstOrDefault();
        }
    }

The error

A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll

Additional information: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@EmployeeId".

I have also tried using DynamicParameters and passing that instead but that is not working either

var p = new DynamicParameters();
p.Add("@EmployeeId", employeeId); // I have also tried without the @
//...
var r = con.Query<User>(sql,p);

davidk

Popular Answer

I landed here from dublicate question: Dapper must declare the scalar variable

Error: Must declare the scalar variable "@Name".

I created queries dynamically with this piece of code:

public static bool Insert<T>(T entity)
        {
            var tableName = entity.GetType().CustomAttributes.FirstOrDefault(x => x.AttributeType.Name == nameof(TableAttribute))?.ConstructorArguments?.FirstOrDefault().Value as string;
            if (string.IsNullOrEmpty(tableName))
                throw new Exception($"Cannot save {entity.GetType().Name}. Database models should have [Table(\"tablename\")] attribute.");
            DBSchema.TryGetValue(tableName.ToLower(), out var fields);
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();

                var sql = $"INSERT INTO [{tableName}] (";
                foreach (var field in fields.Where(x => x != "id")) 
                {
                    sql += $"[{field}]"+",";
                }
                sql = sql.TrimEnd(',');
                sql += ")";
                sql += " VALUES (";
                foreach (var field in fields.Where(x => x != "id"))
                {
                    sql += "@"+field + ",";
                }
                sql = sql.TrimEnd(',');
                sql += ")";

                var affectedRows = con.Execute(sql, entity);
                return affectedRows > 0;
            }
        }

And I got the same error when my models was like this:

[Table("Users")]
public class User
{
   public string Name;
   public string Age;
}

I changed them to this:

[Table("Users")]
public class User
{
    public string Name { get; set; }
    public string Age { get; set; }
}

And it solved the problem for me.

Muno

View more on Stack Overflow

Marc Gravell · Accepted Answer · 5/23/2017 12:10:36 PM

There are two issues here; firstly (although you note this in your question) where a.acct = '@ZYX', under SQL rules, does not make use of any parameter - it looks to match the literal string that happens to include an @ sign. For SQL-Server (see note below), the correct usage would be where a.acct = @ZYX.

However! Since you are use OdbcConnection, named parameters do not apply. If you are actually connecting to something like SQL-Server, I would strongly recommend using the pure ADO.NET clients, which have better features and performance than ODBC. However, if ODBC is your only option: it does not use named parameters. Until a few days ago, this would have represented a major problem, but as per Passing query parameters in Dapper using OleDb, the code (but not yet the NuGet package) now supports ODBC. If you build from source (or wait for the next release), you should be able to use:

...
where a.acct = ?

in your command, and:

var result = connection.Query(sqlString.ToString(),
new {
    anythingYouLike = accountNumber
});

Note that the name (anythingYouLike) is not used by ODBC, so can be... anything you like. In a more complex scenario, for example:

.Execute(sql, new { id = 123, name = "abc", when = DateTime.Now });

dapper uses some knowledge of how anonymous types are implemented to understand the original order of the values, so that they are added to the command in the correct sequence (id, name, when).

One final observation:

Which means dapper is not replacing the parameter with it's given value.

Dapper never replaces parameters with their given value. That is simply not the correct way to parameterize sql: the parameters are usually sent separately, ensuring:

there is no SQL injection risk
maximum query plan re-use
no issues of formatting

Note that some ADO.NET / ODBC providers could theoretically choose to implement things internally via replacement - but that is separate to dapper.

Why with Dapper I get a declare the scalar variable error?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator