SQL Multi line query in C# using SQLCommand

Question

I want to load specific data from different tables in SQL Server using C# But i got an error Incorrect syntax near the keyword 'as'. Incorrect syntax near 'EP'. Incorrect syntax near the keyword 'AND'. The code run well as i have put a messageBox to show the query, a messageBox popsup and show full query but after that i have got an error as i have mentioned above Here is the Code

 private void FillGridView()
    {
        CS = ConfigurationManager.ConnectionStrings["HRMSConnectionString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(CS))
        { SqlCommand cmd = new SqlCommand(query, con);
                con.Open();
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(dt);
                gvShowAllData.DataSource = dt;
        }
    }

Here is the Query

    string query = @"Select 'ACTIVE POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact" +
",D.desig_id,D.desig_name" +
"from EMP_Master as e,EMP_Posting_Log as p,EMP_Designation AS D" +
"where e.emp_id=p.emp_id" +
"AND P.desg_id=D.desig_id" +
"and p.status='ACTIVE'" +
"AND E.emp_name LIKE '%" + tbSearchName.Text + "%'" +
"UNION" +
"Select 'INACTIVE POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact" +
",NULL,NULL" +
"from EMP_Master as e" +
"WHERE E.emp_id IN (SELECT DISTINCT EP.emp_id FROM EMP_Posting_Log AS EP" +
"WHERE EP.status='INACTIVE')" +
"AND E.emp_name LIKE '%" + tbSearchName.Text + "%'" +
"UNION" +
"Select 'NOT POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact" +
",NULL,NULL" +
"from EMP_Master as e" +
"WHERE E.emp_id NOT IN (SELECT DISTINCT EP1.emp_id FROM EMP_Posting_Log AS EP1)" +
"AND E.emp_name LIKE '%" + tbSearchName.Text + "%'";

You have no spaces on the end of each line in your strings. They will be concatenated, but with no spaces commands will run together. — Adam Schiavone
– Adam Schiavone, Commented Nov 8, 2016 at 15:24
Your code is susceptible to SQL injection. Use parameters rather than string concatenation for all user input values. — Theo
– Theo, Commented Nov 8, 2016 at 15:41

Igor · Accepted Answer · 2017-09-16 10:36:35Z

10

You need spaces between your string parts. You are expecting the concatenation to create new lines but the reality is that is not how it works. If you want a new line or a space between strings you need to add that in the string.

The more important issue is that your query is vulnerable to sql injection attacks. You should always use parameters for user input.

string query = @"Select 'ACTIVE POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact,D.desig_id,D.desig_name
 from EMP_Master as e,EMP_Posting_Log as p,EMP_Designation AS D
 where e.emp_id=p.emp_id
 AND P.desg_id=D.desig_id
 and p.status='ACTIVE'
 AND E.emp_name LIKE @tbSearchName
 UNION
 Select 'INACTIVE POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact,NULL,NULL
 from EMP_Master as e
 WHERE E.emp_id IN (SELECT DISTINCT EP.emp_id FROM EMP_Posting_Log AS EP
 WHERE EP.status='INACTIVE')
 AND E.emp_name LIKE @tbSearchName
 UNION
 Select 'NOT POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact,NULL,NULL
 from EMP_Master as e
 WHERE E.emp_id NOT IN (SELECT DISTINCT EP1.emp_id FROM EMP_Posting_Log AS EP1)
 AND E.emp_name LIKE @tbSearchName";

as for adding a parameter:

cmd.Parameters.Add(new SqlParameter("@tbSearchName", SqlDbType.VarChar) {Value = "%" + tbSearchName.Text + "%"});

edited Sep 16, 2017 at 10:36

answered Nov 8, 2016 at 15:28

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Igor Over a year ago

@BenW - one of my favorites :)

Af'faq Over a year ago

@Igor Thanks for valuable answer, I have added space but now i am getting only error that Incorrect syntax near the keyword 'as'.

Igor Over a year ago

@khaafi - then you did not copy what I have above. Copy the entire statement and use that verbatim. Then use the added parameter I supplied and add that after you create the SqlCommand instance. That should fix your problem.

Af'faq Over a year ago

but still getting the same error, i have put cmd.Parameters.Add(new SqlParameter("@tbSearchName", SqlDbType.VarChar) {Value = "%" + tbSearchName.Text + "%"}); after SqlCommand cmd = new SqlCommand(query, con);

Igor Over a year ago

@khaafi - You can test in in SSMS by copying everything between quotes (") and putting it in a new query window and at the top add DECLARE @tbSearchName varchar(2000) = '%TEST VALUE HERE%' and put your value in there. Then see if that runs. If that runs then you did not copy what I have above correctly into your code OR you did not re-compile your code after you made the change. If it does not run then see what line the error occurs on and report back and we can see what the issue is.

|

mybirthname · Accepted Answer · 2021-04-30 09:09:28Z

7

Your problems are because of the concatenation of strings. You can write strings on multiple lines without using + when you start your string with @

string query = @"
Select 'ACTIVE POSTING' as POSTING, e.emp_id, e.emp_name, 
        e.emp_fathername, e.emp_nic, e.emp_contact, D.desig_id, D.desig_name

//and so on continue with your query
";

Also look into SqlCommand.Parameters to prevent your code from sql injection.

edited Apr 30, 2021 at 9:09

answered Nov 8, 2016 at 15:27

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

Comments

Cato · Accepted Answer · 2016-11-08 15:35:19Z

3

although it appears 'in lines' in your code, you are just making one big line of SQL and your spaces are incorrect - you could try something like

        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        sb.AppendLine(@"Select 'ACTIVE POSTING' as POSTING,e.emp_id,e.emp_name,e.emp_fathername,e.emp_nic,e.emp_contact");
        sb.AppendLine(@",D.desig_id,D.desig_name");
        sb.AppendLine(@"from EMP_Master as e,EMP_Posting_Log as p,EMP_Designation AS D");
        sb.AppendLine(@"where e.emp_id=p.emp_id");
        sb.AppendLine(@"AND P.desg_id=D.desig_id");
        sb.AppendLine(@"and p.status='ACTIVE'");
        sb.AppendLine("AND E.emp_name LIKE '%" + tbSearchName.Text.Replace(@"'",@"''") + "%'");  //escape single quote to avoid SQL injection attack

        //....and so on with the rest of your lines

        string query = sb.ToString();

        //then as before

answered Nov 8, 2016 at 15:35

Cato

3,72711 silver badges12 bronze badges

4 Comments

Af'faq Over a year ago

Thanks Man .. Love YOu :) Solved

Af'faq Over a year ago

I am not able to fetch image from database to pictureBox with the same query, would you like to help me with that? i have reload all the other controls data from Database but can't load the image with the above query

Cato Over a year ago

@kh - if you are trying to return an image - what methods are you using to get the image data into the pictureBox?

Af'faq Over a year ago

when i insert an image i first convert image into memory stream through buffer and store image as a raw format , and for reloading image from database to picturebox i convert image from memory stream and store the image in a byte[] array. Firstly it works well means image reloading to picture box but when i use the above SQL Query with UNIONS, it throws an error. Please help me bro

Collectives™ on Stack Overflow

SQL Multi line query in C# using SQLCommand

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related