c# SQLDataAdapter syntax error near @parameterName

Question

I have to run a query in a SqlDataAdapter but I am having problems with syntax error near @parameterName. My code:

con.Open();
        string sql ="select top @take * from"+
"(Select ProductName, CategoryName, CompanyName, UnitPrice, ROW_NUMBER() OVER(ORDER BY ProductId) AS ROW_NUM "+
"from Products as p inner join Categories as c on p.CategoryID = c.CategoryID "+
"inner join Suppliers as s on p.SupplierID = s.SupplierID "+
") as x "+
"where x.ROW_NUM > @skip ";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
            
            adapter.SelectCommand.Parameters.Add("@take", SqlDbType.Int).Value = 20;
            adapter.SelectCommand.Parameters.Add("@skip", SqlDbType.Int).Value = 10;
          /* */ 
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            repProducts.DataSource = dt;
            repProducts.DataBind();
            con.Close();

If I am not horrendously mistaken, I do not think SQLParams work for take and skip. — Fildor, Nov 01 '22 at 12:00
Well, you are hard-coding them anyway, so you can as well put them into the query as literals. — Fildor, Nov 01 '22 at 12:08

score 2 · Accepted Answer · answered Nov 01 '22 at 13:55

To pass a parameter, variable or calculation for TOP you need to put it into () paranethesis. This is called out in the documentation:

For backward compatibility, the parentheses are optional in SELECT statements if the expression is an integer constant. We recommend that you always use parentheses for TOP in SELECT statements.

You are also missing using in various places.
You can use a verbatim string instead of concatenating, and embed newlines directly in the string.

DataTable dt = new DataTable();

const string sql = @"
select top (@take)
  *
from (
    Select
      ProductName,
      CategoryName,
      CompanyName,
      UnitPrice,
      ROW_NUMBER() OVER(ORDER BY ProductId) AS ROW_NUM
    from Products as p
    inner join Categories as c on p.CategoryID = c.CategoryID
    inner join Suppliers as s on p.SupplierID = s.SupplierID
) as x
where x.ROW_NUM > @skip;
";
using (var con = new SqlConnection(YourConnString))
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
{
    adapter.SelectCommand.Parameters.Add("@take", SqlDbType.Int).Value = 20;
    adapter.SelectCommand.Parameters.Add("@skip", SqlDbType.Int).Value = 10;
    con.Open();
    adapter.Fill(dt);
}
repProducts.DataSource = dt;
repProducts.DataBind();

You also don't need the ROW_NUMBER, as SQL Server now offers the OFFSET FETCH syntax:

const string sql = @"
select
  ProductName,
  CategoryName,
  CompanyName,
  UnitPrice
from Products as p
inner join Categories as c on p.CategoryID = c.CategoryID
inner join Suppliers as s on p.SupplierID = s.SupplierID
ORDER BY ProductId
OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY;
";

You may also want to take a look at this post, among others, on the inefficiency of Rowset Pagination:
Is there any better option to apply pagination without applying OFFSET in SQL Server?

c# SQLDataAdapter syntax error near @parameterName

1 Answers1