Command.ExecuteNonQuery(); Error: Incorrect syntax near '='

Question

My query is perfect (I have verified it in SQL Server Management Studio). My code is perfect, still I am getting this syntax error:

Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '='.

public partial class Temporaryche : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ddlTDept.Items.Clear();
        ddlTBranch.Items.Clear();

        string connectionString = GlobalVariables.databasePath;
        SqlConnection sqlCon = new SqlConnection(connectionString);
        string query = "select fac.fac_name, dp.dp_name, br.br_name from STUDENT s, DIVISON dv, BRANCH br, DEPT dp, FACULTY fac, CLASS cls, DEGREE dg where dg.dg_id = cls.dg_id and cls.cls_id = s.cls_id and fac.fac_id = dp.fac_id and dp.dp_id = br.dp_id and br.br_id = dv.br_id and s.dv_id = dv.dv_id and s.prn_no = " + txtSearch.Text;

        sqlCon.Open();
        SqlCommand cmd = new SqlCommand(query, sqlCon);
        SqlDataReader reader = cmd.ExecuteReader();

        string facultyName = reader.GetValue(0).ToString();
        string deptName = reader.GetValue(1).ToString();
        string branchName = reader.GetValue(2).ToString();

        ddlTFaculty.SelectedValue = facultyName;

        query = "select dp_name from DEPT where fac_id=(select fac_id where fac_name='" + facultyName + "')";
        cmd = new SqlCommand(query, sqlCon);
        reader = cmd.ExecuteReader();
        ddlTDept.Items.Clear();

        while (reader.Read())
        {
            ddlTDept.Items.Add(reader.GetValue(0).ToString());
        }

        ddlTDept.SelectedValue = deptName;
        sqlCon.Close();
    }
}

And my Error is like this: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '='. — AlexR, Oct 24 '15 at 20:25
You should be using SQL parameters, not inlining facultyName into the SQL query. Otherwise you're vulnerable to SQL injection attacks. https://xkcd.com/327/ — Bryce Wagner, Oct 24 '15 at 20:29
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Oct 24 '15 at 21:16
hmm, i'm sorry @marc_s, but can you please tell me your way JOIN syntex for this query = "select fac.fac_name, dp.dp_name, br.br_name from STUDENT s, DIVISON dv, BRANCH br, DEPT dp, FACULTY fac, CLASS cls, DEGREE dg where dg.dg_id = cls.dg_id and cls.cls_id = s.cls_id and fac.fac_id = dp.fac_id and dp.dp_id = br.dp_id and br.br_id = dv.br_id and s.dv_id = dv.dv_id and s.prn_no = " + txtSearch.Text; — AlexR, Oct 24 '15 at 21:34
It would be something like `SELECT ..... FROM Student s INNER JOIN Class cls ON s.cld_id = cls.cls_id ....` and so forth - use `INNER JOIN`, `LEFT OUTER JOIN` and the other join types defined in the ANSI SQL standard, and define the **join condition** right there, on the JOIN line. — marc_s, Oct 24 '15 at 21:36

Steve · Answer 1 · 2015-10-24T21:33:53.490

You don't have any ExecuteNonQuery in your code, so the only point where a Syntax error could occur is in this line

query = @"select dp_name from DEPT 
         where fac_id=(select fac_id where fac_name='" + facultyName + "')";

In this line you miss the FROM in the subquery, so you should write as

query = @"select dp_name 
          from DEPT 
          where fac_id= (select fac_id 
                         FROM DEPT where fac_name= '" + facultyName + "')";

Of course this just fix your immediate problem, but as other have said, you should start immediately to use a parameterized query.

For example

query = @"SELECT dp_name FROM DEPT 
          wHERE fac_id = (SELECT TOP 1 fac_id 
                          FROM DEPT 
                          WHERE fac_name=@faculty";
cmd = new SqlCommand(query, sqlCon);
cmd.Parameters.Add("@faculty", SqlDbType.NVarChar).Value = facultyName;
....

EDIT
I should rewrite my first statement. There is another point where the syntax error could occur. It is in the first line if the txtSearch.Text is empty.
In this case the query remains incomplete and triggers the syntax error.
This could happen because the code is called in a Page_Load event when there is still nothing in the text box. So probably we should add something to prevent the execution of the Whole block of code if the txtSearch is empty

protected void Page_Load(object sender, EventArgs e)
{
    if(!string.IsNullOrWhiteSpace(txtSearch.Text))
    {
        .... code that executes the queries ...
    }
}

All in all this code doesn't fit well in the Page_Load event, perhaps this code should be moved to a button click event when the user asks to perform the search....

Finally, as mentioned in comments, you should use the JOIN syntax to better divide the WHERE condition from the relations between your tables. The query could be written as

SELECT  fac.fac_name,
        dp.dp_name,
        br.br_name 
FROM    STUDENT s INNER JOIN DIVISION dv ON s.dv_id = dv.dv_id
        INNER JOIN BRANCH br ON dv.br_id = br.br_id
        INNER JOIN DEPT dp ON br.dp_id = dp.dp_id
        INNER JOIN FACULTY fac ON dp.fac_id = fac.fac_id
        INNER JOIN CLASS cls ON s.cls_id = cls.cls_id
        INNER JOIN DEGREE dg ON cls.dg_id = dg.dg_id
WHERE   s.prn_no=@search";

Done, but please do not remove the comment. It is right and you should leave it to let future readers understand the evelotion of the answer. — Steve, Oct 24 '15 at 20:59
yes,i used parameterized query version as you said. Actual value for faculty name is - "FTE". — AlexR, Oct 24 '15 at 21:02
but the error it shown is at upper query line as i i write above — AlexR, Oct 24 '15 at 21:05
If you try to run that query in Sql Server Management Studio, did you get some kind of errors? — Steve, Oct 24 '15 at 21:06
no. that's why i'm confused. in server management studio, it shows me perfect reply as i want. — AlexR, Oct 24 '15 at 21:08
And of course you have some predefined text in that textbox, right? Because if not then you have a truncated query and remember, a Page_Load event is triggered everytime you have a server side event to execute, not just on the first Page_Load event — Steve, Oct 24 '15 at 21:10
hmmm,yeah you are right, because i don's have a predefined text in textbox. — AlexR, Oct 24 '15 at 21:12

score 1 · Answer 2 · answered Oct 24 '15 at 20:33

You don't mention the line the error is occurring on so hard to be 100% sure, also some other info missing. However in the first SQL text you have the following at the end:

s.prn_no=" + txtSearch.Text;

Since that is text it should be in quotes ''

s.prn_no='" + txtSearch.Text +"'"

That said you should NEVER be building SQl queries like this due to SQL Injection. Instead you should be using Parameter objects instead.

Command.ExecuteNonQuery(); Error: Incorrect syntax near '='

2 Answers2