Click here to Skip to main content
15,746,302 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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);
            CrystalReport_Car_Details_date_end report = new CrystalReport_Car_Details_date_end();
            crystalReportViewer_Car_Details_Date_end.ReportSource = report;
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.

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 ;.
Share this answer
Andy Lanng 26-Jan-16 9:21am    
You beat me to the solution, but I explain the semi-colon ^_^
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.
Share this answer
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 ;)
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:

[some SQL] union [some SQL]
[END QUERY] <= the semi colon
union s2;  

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
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900