CODEWITHSUNDEEP
c#sqlparameter-passingdatalist
T J
T J

Reputation: 43166

Using Parameterised Query to Populate Datalist

I've been trying to pass a varying value in as sql query using parameters as follows:

SELECT TOP 5 * 
FROM Events 
WHERE EID NOT IN (SELECT TOP @limit EID FROM Events ORDER BY EID)

For finding the records after the first n rows specified by the value of the parameter @limit.

E.g. if I specify the value 10, then the query returns rows 6-10.

Right now what I'm doing is

string str = "SELECT TOP 5 * FROM Events WHERE EID NOT IN (SELECT TOP @limit EID FROM Events ORDER BY EID)"

SqlCommand cmd=new SqlCommand(str,con); // con is the connection string

cmd.Parameters.AddWithValue("limit", value);

SqlDataAdapter sda= new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
sda.Fill(ds);

return ds;

The dataset ds is then bound to a Datalist.

Currently an error is thrown saying

"incorrect syntax near @limit"

What is (are) wrong with the code..?

Upvotes: 1

Views: 373

Answers (1)

D Stanley
D Stanley

Reputation: 152626

When pulling the TOP n using a parameter you have to enclose the parameter in parentheses:

string str= "SELECT TOP 5 * FROM Events WHERE EID NOT IN (SELECT TOP (@limit) EID FROM Events ORDER BY EID)"

Upvotes: 6

Related Questions