0

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();
3
  • If I am not horrendously mistaken, I do not think SQLParams work for take and skip. Commented Nov 1, 2022 at 12:00
  • I thought it would be possible, but i guess you are right Commented Nov 1, 2022 at 12:06
  • Well, you are hard-coding them anyway, so you can as well put them into the query as literals. Commented Nov 1, 2022 at 12:08

1 Answer 1

2

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?

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.