Incorrect syntax for SELECT TOP with a Parameter

Question

I used to have this one

Dt = MyMod.GetDataTable("SELECT TOP " & QuestionsPerCats(i) & " * From Questions WHERE CategoriesID ='" & Cats(i) & "' ORDER BY NEWID()")

but now i decided to use sqlparameters like

Dim cmd As New SqlCommand("SELECT TOP @QuestionsPerCats * From Questions WHERE CategoriesID = @CategoriesID ORDER BY NEWID()", conn)
Dim sqlParam As SqlParameter = Nothing
sqlParam = cmd.Parameters.Add("@QuestionsPerCats", SqlDbType.SmallInt)
sqlParam.Value = QuestionsPerCats(i)
sqlParam = cmd.Parameters.Add("@CategoriesID", SqlDbType.SmallInt)
sqlParam.Value = Cats(i)

which unfortunately "renders" like

SELECT TOP @QuestionsPerCats * From Questions WHERE CategoriesID = @CategoriesID ORDER BY NEWID()

and returns the following error

Incorrect syntax near '@QuestionsPerCats'.

So what am i doing wrong here?

You might try using cmd.Parameters.AddWithValue(name, value) — Sam Axe, Apr 08 '11 at 00:00

score 12 · Accepted Answer · edited Nov 13 '18 at 23:39

12

Try:

SELECT TOP (@QuestionsPerCats) *
FROM Questions
WHERE CategoriesID = @CategoriesID
ORDER BY NEWID()

(SQL Server 2005 and up)

edited Nov 13 '18 at 23:39

Community

1
1

answered Apr 07 '11 at 18:56

Cade Roux

88,164
40
182
265

1

Just a little more info: SQL 2005 made a change to the TOP keyword so that it is now a function that can take a parameter. – NotMe Apr 08 '11 at 00:02

score 0 · Answer 2 · edited Apr 07 '11 at 19:08

0

Include @QuestionsPerCats in (), as defined in this link

SELECT TOP (@QuestionsPerCats) * 
From Questions 
WHERE CategoriesID = @CategoriesID ORDER BY NEWID()

edited Apr 07 '11 at 19:08

Abe Miessler

82,532
99
305
486

answered Apr 07 '11 at 18:58

Nitin Midha

2,258
20
22

With over 800 rep you probably should have figured out `code tags` by now, right? – JNK Apr 07 '11 at 19:07

Abe Miessler · Answer 3 · 2011-04-07T19:04:18.650

0

Try changing it to this:

cmd.Parameters.Add("@QuestionsPerCats", SqlDbType.SmallInt)
cmd.Parameters("@QuestionsPerCats").Value = QuestionsPerCats(i)


cmd.Parameters.Add("@CategoriesID", SqlDbType.SmallInt)
cmd.Parameters("@CategoriesID").Value = Cats(i)

The method you are using to add parameters to the command is a little weird. I suspect this may be the cause of your error.

edited Apr 07 '11 at 19:04

answered Apr 07 '11 at 18:59

Abe Miessler

82,532
99
305
486

@downvoter, is the method he used normal in VB.NET? I am a C# guy, so please correct me if I am wrong. – Abe Miessler Apr 07 '11 at 19:11

Incorrect syntax for SELECT TOP with a Parameter

3 Answers3