Must declare the scalar variable/ Variable has been already declared

Question

I am trying to create a function which takes a query and it's parameters and returns a SQLDataSource for further binding.

The function is:

 public static SqlDataSource getSqlSource(string sql, Dictionary<string, string> parameters)
    {
        SqlConnection con = new SqlConnection(ConnectionString);

        using (SqlCommand cmd = con.CreateCommand())
        {
            con.Open();
            cmd.CommandText = sql;
            foreach (KeyValuePair<string, string> item in parameters)
            {
                 
                cmd.Parameters.Add(new SqlParameter(item.Key, item.Value));

            }
            SqlDataSource source = new SqlDataSource(ConnectionString, cmd.CommandText);

            con.Close();

            return source;
        }
    }

The query I am trying to create is:

    string sql =
          // "declare @from date ; " +
          "with cte as " +
"( " +
"select username,count(username) as cc from SaleReport " +
"group by username " +
") " +
"select count(SaleReport.username) as numerKlientesh,distributorname from SaleReport " +
"inner join cte on SaleReport.username=cte.username " +
 "where  (SaleReport.saledate) <=CONVERT(date,@from) " +
  "and (SaleReport.saledate)>=(dateadd(DAY,-10,CONVERT(date,@from))) " +
 "and cc>1 and SaleReport.comboid not in (43,44,46,47) " +
"group by distributorname ";
        Dictionary<String, String> Params = new Dictionary<String, String>();
        Params.Add("@from", from);
        SqlDataSource source = QueryManager.getSqlSource(sql, Params); 
        GridView1.DataSource = source;
        GridView1.DataBind();

When I run the application, I get the following error:

"Must declare the variable @from"

But, when I try to declare it (I have put the declaration as a comment ) I get this message:

The variable name '@from' has already been declared.

Variable names must be unique within a query batch or stored procedure.

Am i doing something wrong in the query perhaps?

EDIT1: With the suggestion of SabinBio, I have outputted the result of the query as following:

declare @from date; 
with cte as (
     select username
        ,count(username) as cc 
    from SaleReport 
    group by username 
    ) 
select count(SaleReport.username) as numerKlientesh
    ,distributorname 
from SaleReport 
    inner join cte on SaleReport.username=cte.username 
where (SaleReport.saledate) <= CONVERT(date,@from) 
    and (SaleReport.saledate) >= (dateadd(DAY,-10,CONVERT(date,@from))) 
    and cc>1 
    and SaleReport.comboid not in (43,44,46,47) 
group by distributorname

EDIT2

If I put the query in a stored procedure, and I call it as :

DECLARE @return_value int  EXEC @return_value = [dbo].[ProcedureName]  @from ="+from+"

it works fine, but I want to avoid possible Sql Injection and if I declare it like:

DECLARE @return_value int  EXEC @return_value = [dbo].[ProcedureName]  @from =@saledate

and put the saledate as an item in my dictionary I still have the error

Must declare the variable @saledate

You declared "@saleDate" not "@from" ( this is the line // "declare @saledate date ; " + ) — Sabin B, Apr 28 '15 at 13:37
@sabinbio thank you for pointing it out. However, even if i declare it as "@from", it still brings me the same issue :( . I edited the question — Ange1, Apr 28 '15 at 13:38
@Ange1 - this question is off-topic for dba.stackexchange.com - it will get automatically moved to StackOverflow. — Hannah Vernon, Apr 28 '15 at 13:44
What happens if you remove the `declare @from date ;` completely from the string? — ypercubeᵀᴹ, Apr 28 '15 at 14:01
@ypercube i get the error: "Must declare the variable @from" — Ange1, Apr 28 '15 at 14:02
The sql part seems ok ; can you create a stored procedure or a function (that encapsulate the text ), and call it from c# ? — Sabin B, Apr 28 '15 at 14:13
try to execute with the help of sp_executeSQL , like this: (this is the way in sql). See some examples on it. DECLARE '@SQLString' NVARCHAR(500) DECLARE '@ParmDefinition' NVARCHAR(500) SET '@SQLString' = N'your string' SET '@ParmDefinition' = N'@from date' EXECUTE sp_executesql '@sql' , '@ParmDefinition' , '@from' = Your_data_value http://stackoverflow.com/questions/376941/c-sp-executesql-and-incorrect-syntax — Sabin B, Apr 29 '15 at 04:59
@sabinbio i have posted an answer regarding the question, with the changes i've made to the function. Now everything is alright :) — Ange1, Apr 29 '15 at 08:12

score 1 · Answer 1 · answered Apr 28 '15 at 13:41

1

You have cmd.ExecuteReader() in the wrong place here:

        foreach (KeyValuePair<string, string> item in parameters)
        {

            cmd.Parameters.Add(new SqlParameter(item.Key, item.Value));

            cmd.ExecuteReader();

        }

I expect this needs to be like:

        foreach (KeyValuePair<string, string> item in parameters)
        {

            cmd.Parameters.Add(new SqlParameter(item.Key, item.Value));

        }
        cmd.ExecuteReader();

answered Apr 28 '15 at 13:41

Hannah Vernon

3,367
1
26
48

Still the Same issue. Since i just have to return the dataSource, i have tried to remove the cmd.ExecuteReader() at all, but in this case, it doesn't return any value – Ange1 Apr 28 '15 at 13:44
if is the same issue , can you help with the output of this : MessageBox.Show(sql.ToString()); to see the text you pass – Sabin B Apr 28 '15 at 13:48

score 1 · Accepted Answer · edited May 23 '17 at 12:15

After researching a while , i changed the function in:

 public static SqlDataSource getSqlSource(string sql,  Dictionary<string, string> parameters)
 {
     SqlConnection con = new SqlConnection(ConnectionString); 
     SqlDataSource source = new SqlDataSource(ConnectionString, sql);

     foreach (KeyValuePair<string,string> pair in parameters)
     {

         source.SelectParameters.Add(pair.Key, pair.Value);
     }

     return source;
 }

and when declaring the dictionary in code behind, the Key item should not be declared with the "@" character,the "@" character has to be declared only in the query, so, the declaration is:

  Dictionary<String, String> Params = new Dictionary<String, String>();
    Params.Add("from", from);

Suggestion taken HERE Thank you everyone for the help :)

Must declare the scalar variable/ Variable has been already declared

2 Answers2