Incorrect syntax near the keyword 'union'. in C#

Question

1.00/5 (1 vote)

See more:

C#

private void FRM_RPT_Car_Details_Date_end_Load(object sender, EventArgs e)
        {
            s2 = "select Cars.Car_id,Job_Card,convert(varchar, Change_Date,101) as Change_date,convert(varchar,Reload_License.Car_E_date_Licen,101) as Car_E_date_Licen, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(Current_dist - Prev_dist) as Change_dist,Value_Fuel as Value,Qty from Full_Fuel inner join cars on Full_Fuel.Car_id= Cars.Car_id inner join Reload_License  on Cars.Car_id = Reload_License.Car_id inner join Service_Type on Full_Fuel.Servce_id = Service_Type.Servce_id where Reload_License.Car_E_date_Licen = '" + x + "';";
            s1 = "select Cars.Car_id,Job_Card,convert(varchar, Change_date,101) as Change_date,convert(varchar,Reload_License.Car_E_date_Licen,101) as Car_E_date_Licen, Service_Type.Servce_desc,Current_Dist as Current_dist,Prev_Dist as Prev_dist,(Current_dist - Prev_Dist) as Change_dist,Value_Oil as Value,QTY as Qty from Change_Oil inner join cars on Change_Oil.Car_id= Cars.Car_id inner join Reload_License  on Cars.Car_id = Reload_License.Car_id inner join Service_Type on Change_Oil.Servce_id = Service_Type.Servce_id where Reload_License.Car_E_date_Licen = '" + x + "'; ";
            s = "select Cars.Car_id,Job_card,convert(varchar, Change_date,101) as Change_date ,convert(varchar,Reload_License.Car_E_date_Licen,101) as Car_E_date_Licen, Service_Type.Servce_desc, Current_dist,Prev_distance as Prev_dist,(Current_dist - Prev_distance) as Change_dist,value_Tashhem as Value,NULL as [Qty] from Tashhem inner join cars on Tashhem.Car_id= Cars.Car_id inner join Service_Type on Tashhem.Servce_id = Service_Type.Servce_id inner join Reload_License on Cars.Car_id = Reload_License.Car_id where Reload_License.Car_E_date_Licen = '" + x + "' union " + s1 + " union "+ s2 +";";
            DataSet_Car_Details_end_date ds = new DataSet_Car_Details_end_date();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(s, con);
            dataAdapter.Fill(ds.Tables["Car_End_date"]);
            CrystalReport_Car_Details_date_end report = new CrystalReport_Car_Details_date_end();
            report.SetDataSource(ds.Tables["Car_End_date"]);
            crystalReportViewer_Car_Details_Date_end.ReportSource = report;
            crystalReportViewer_Car_Details_Date_end.Refresh(); 
        }

Posted 26-Jan-16 2:48am

Member 12244977

Add a Solution

Comments

Andy Lanng 26-Jan-16 8:57am

There's a syntax error near in the sql. What? you wanted more?
check the result of s, paste it into a query and check the syntax there. You'll see the error.

Member 12244977 26-Jan-16 9:06am

the error here :- where Reload_License.Car_E_date_Licen = '" + x + "' union " + s1 + " union "+ s2 +";";

Andy Lanng 26-Jan-16 9:11am

yes it is. that is what the error says.

Ok - I'll write a solution >_<

Richard Deeming 26-Jan-16 9:50am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Philippe Mori 26-Jan-16 12:31pm

Virtual 5.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2016-01-26T03:10:00

This is a very, very easy issue for you to solve on your own. Do as Andy suggested and paste the entire sql (what's in your variable s) into Sql Management Studio as a new query and then you'll be able to see what the issue is.

We can't see what the result is so we can't tell you what is wrong. Also, we can't see what x is so we have no idea what you have done wrong.

However, you should be able to solve it faster than it takes to post the question here.

Hint: Don't put ; at the end of sql statements that are being unioned. In fact, I don't know of a reason to ever use ;.

John C Rayan · Answer 2 · 2016-01-26T03:14:00

Solution 2

It appears that whatever value you pass in for x breaks the SQL . You need to check that to make sure it doesn't break your SQL.

Apart from that you C# and SQL seems to be fine.

Posted 26-Jan-16 3:14am

John C Rayan

Updated 26-Jan-16 3:15am

v2

Comments

Andy Lanng 26-Jan-16 9:23am

Actually turns out the syntax error is on (or just before) the second 'union'

I'm guessing that the OP tested all three statements prior to the union. RyanDev's post hints at the actual issue ;)

Andy Lanng · Answer 3 · 2016-01-26T03:20:00

You can step through the code and check the value of s before it executes.

Building and Debugging (Visual C#)[^]

This is VERY useful for debugging.

Ok. So I'll give you a hint.

There are two way to end a statement in T-SQl. The keyword GO and the semi colon ;. Their use is slightly different, but that's not important here.

So you have s1 and s2 which are in the syntax "[some SQL];". I'm sure you tested these but note the semi colon on the end.

then you have s which is "[some SQL] union s1; union s2;"

Do you see it?

This is what SQL sees:

[START QUERY] 
[some SQL] union [some SQL]
[END QUERY] <= the semi colon
[START QUERY]
union s2;  
[SYNTAX ERROR NEAR UNION]

When you run a single query at a time, you don't even need a semi colon. The statement will terminate if there is nothing left to do. You would only add it in a query if you wanted to perform more that one action.

Just remove the semi colon

Incorrect syntax near the keyword 'union'. in C#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0