SQL limit error

Question

I use this code in my project (SQL COMPACT):

"select Name 
from Drug 
where Name 
like '" + Dname + "%'  
limit 10"

Dname is a string value. The result is this error:

There was an error parsing the query.

[ Token line number = 1,Token line offset = 44,Token in error = LIMIT ]

Why is this happening and how can I fix it?

To what kind of database are you connecting (MySQL, Oracle,...)? — BellevueBob, Mar 16 '13 at 18:21

Thousand · Answer 1 · 2013-03-16T18:35:36.020

4

i think what you want is

"select TOP (10)  
from Drug 
where Name 
like '" + Dname + "%'  "

you should also try using parametrized queries:

 string qry = "select TOP(10) from Drugs where name like @dname";
 SqlCommand oCmd = new SqlCommand(qry, ConnectionString);
 oCmd.Parameters.AddWithValue("@dname", dname +  '%');

edited Mar 16 '13 at 18:35

answered Mar 16 '13 at 18:22

Thousand

6,562
3
38
46

using this code too give me an error: There was an error parsing the query. [ Token line number = 1,Token line offset = 12,Token in error = 10 ] – Mar 16 '13 at 18:24
when i remove limit or top it worked corrct.but when i use them again ...error. – Mar 16 '13 at 18:27
again: what's the error? can you try the run the same query in your local SQL server and see if it works there? – Thousand Mar 16 '13 at 18:31
There was an error parsing the query[ Token line number = 1,Token line offset = 12,Token in error = 10 ] i haven't a sql server.its sql compact 3.5 – Mar 16 '13 at 18:34

score 3 · Answer 2 · answered Mar 16 '13 at 18:27

Never use string concatenations to build SQL queries. Always use parametrized queries:

string connectionString = ....
using (var conn = new SqlConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT TOP 10 FROM drug WHERE name LIKE @name";
    cmd.Parameters.AddWithValue("@name", Dname + '%');
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            ...
        }
    }
}

score 2 · Accepted Answer · edited May 23 '17 at 12:01

2

According to this previous question, the proper syntax is TOP(n), so try this:

"select TOP(10) Name 
from Drug 
where Name 
like '" + Dname + "%' "

edited May 23 '17 at 12:01

Community

1
1

answered Mar 16 '13 at 18:33

BellevueBob

9,498
5
29
56

SQL limit error

3 Answers3