SQL语法错误（'2'附近语法不正确）：用户输入查询行绑定GridView求助

Hey there! The error you're seeing is caused by wrapping the numeric value for the TOP clause in single quotes. SQL expects an integer for TOP, not a string literal—those quotes are making the database interpret your number as text, which leads to the syntax error.

Immediate Fix (Remove the Quotes) #

First, let's fix the syntax issue by removing the single quotes around text:

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();

This should resolve the syntax error, but there's a bigger problem here: SQL injection vulnerabilities. Concatenating user input directly into your SQL query is a huge security risk. Let's fix that properly with parameterized queries.

Secure Solution Using Parameterized Queries #

Parameterized queries prevent SQL injection by separating user input from the SQL command text. Here's how to rewrite your code safely:

Int32 text = Convert.ToInt32(this.Txtusers.Text);
con.Open();
// Use parameters for all user-supplied values
cmd = new SqlCommand("select TOP @TopCount * from Avaya_Id where LOB = @LOB and Status = 'Unassigned'", con);
cmd.Parameters.AddWithValue("@TopCount", text);
cmd.Parameters.AddWithValue("@LOB", DDLOB.SelectedItem.Value);

SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();

Why This Works #

• @TopCount is a parameter that SQL treats as a numeric value, no quotes needed.
• @LOB safely handles the dropdown value without risking injection.
• The database parses the SQL command first, then applies the parameter values, so malicious input can't alter the query structure.

As an extra tip, consider adding error handling (like try/catch blocks) to handle cases where Txtusers.Text isn't a valid integer, and use using statements to ensure your database connection is properly disposed of even if an error occurs.

内容的提问来源于stack exchange，提问作者Mohammed Salim