'Incorrect syntax near '2'.'

Question

Im trying to retrieve no of rows from sql based user input & display in gridview

Please help!

Int32 text = Convert.ToInt32(this.Txtusers.Text);
con.Open();
cmd = new SqlCommand("select TOP '" + text + "' * from Avaya_Id where LOB = '" + DDLOB.SelectedItem.Value + "' and Status = 'Unassigned'", con);
SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();

`SELECT TOP '2' ...` should be `SELECT TOP 2 ...` (without the quotes) — DavidG, May 15 '18 at 17:48
1. `TOP` requires a number, but you set `text` in single quotes. 2. Your code is open to [SQL Injection](http://www.bobby-tables.com). Please use parameterized queries instead of putting user input directly into your query string! — René Vogt, May 15 '18 at 17:49
If you're using [tag:sql-server], you can parameterize the argument to `TOP` as shown in [C# SQL Top as parameter](https://stackoverflow.com/q/1275381/3744182) and [Dynamic SELECT TOP @var In SQL Server](https://stackoverflow.com/q/175962/3744182). See also [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/q/7505808/3744182) for why parameterized SQL queries should always be used instead of directly constructed queries using user input. — dbc, May 15 '18 at 17:51

Zohar Peled · Answer 1 · 2018-05-15T18:06:40.150

Here is how it should be written.

int text;
if(int.TryParse(this.Txtusers.Text, out text)
{
    using(var con = new SqlConnection(connectionString)
    {
        using(var cmd  = new SqlCommand("select TOP (@top) * from Avaya_Id where LOB = @LOB and Status = 'Unassigned'", con))
        {
            cmd.Parameters.Add("@top", SqlDbType.Int).Value = text;
            cmd.Parameters.Add("@LOB", SqlDbType.Int).Value = DDLOB.SelectedItem.Value;
            con.Open();
            using(var rdr = cmd.ExecuteReader())
            {
                GridView1.DataSource = rdr;
                GridView1.DataBind();
            }
        }
    }
}

Points of interest:

Using parameters to avoid the risk of Sql Injection.
Changed Convert.ToInt32 to int.TryParse. Never trust user input.
Use the using statement for every instance that implements the IDisposable interface.
Please note that using top x without an order by clause means you get x arbitrary records from the database - since database tables are unordered by nature and the only way to ensure the order of the rows returned from a select statement is to use the order by clause.

Please note I've guessed that the second parameter is an int, if it's not, change the data type.

'Incorrect syntax near '2'.'

1 Answers1