CODEWITHSUNDEEP
c#mysql
Cyrus
Cyrus

Reputation: 1

mysql select error on c# but running on query builder in navicat

I am getting this error every-time I run this query on c#.

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':= 0 + 1 as rnk1 from attendance_table, (select 0 := 0) r1 order by User_ID, V' at line 1"

When I run it on query builder problem does not occur everything is working fine. I have already added this line 

cmd.Parameters.AddWithValue("@r1", 0);
cmd.Parameters.AddWithValue("@r2", 0);

can somebody help me with this? Here's the select command. 

select 
t1.User_ID,concat(employee_profile.fname,' ',employee_profile.mname,' ',employee_profile.lname) as fullname,
CASE WHEN t1.Verify_State = '0'  THEN t1.Verify_Date END AS time_in,
CASE WHEN t2.Verify_State = '1' THEN t2.Verify_Date END AS time_out,round(TIMESTAMPDIFF(minute,t1.Verify_Date,t2.Verify_Date)/60,2) as Total_hours_worked,timeshift.time_in,
timeshift.time_ou
from 
(select * , @r1:=@r1+1 as rnk1 from attendance_table , (select @r1:=0) r1  order by User_ID, Verify_Date) as  t1 join 
(select * , @r2:=@r2+1 as rnk2 from attendance_table , (select @r2:=0) r2 order by User_ID, Verify_Date) as t2 
on t1.User_ID=t2.User_ID 
and t1.rnk1+1=t2.rnk2 
and t1.Verify_State=0 
and t2.Verify_State=1 
left Join 
employee_profile ON employee_profile.emp_id_no = t1.User_ID 
left JOIN employee_timeshift ON employee_timeshift.emp_id_no = t1.User_ID 
left JOIN timeshift on timeshift.id = employee_timeshift.timeshift_id
left JOIN timeshift_day on timeshift_day.timeshift_id = timeshift.id
where t1.Work_date BETWEEN  '2018-04-09' AND '2018-04-14' and t1.Work_time <> t2.Work_time and timeshift_day.day_id = if(DATE_FORMAT(t1.Verify_Date,'%H:%i:%s') BETWEEN '02:00:00' and '06:00:00',(DAYOFWEEK(t1.Verify_Date) -1)-1,DAYOFWEEK(t1.Verify_Date)-1)
order by  employee_profile.lname asc, t1.Verify_Date,t2.Verify_Date

Upvotes: 0

Views: 59

Answers (1)

sticky bit
sticky bit

Reputation: 37477

With

cmd.Parameters.AddWithValue("@r1", 0);

you're replacing the @r1s in the query with 0 and thus create an assignment to a literal. E.g.

@r1:=@r1+1

becomes

0:=0+1

(assign 0to 0). That's no valid expression as values can only be assigned to variables.

Analog for @r2.

I guess you don't do that replacement, when you run the query directly, so it works there.

As you didn't post what you actually intended to do, I cannot give you any further advise.

Upvotes: 1

Related Questions