Sql Query error asp.net c#

Question

I am using the below code to get results from my table and bind it to a gridview control . But I get the following error:

"System.Data.SqlClient.SqlException: Incorrect syntax near 'x'" at "sqlda.Fill(ds1)".

I am new to ASP.NET and cannot write good queries.

SqlConnection con = new SqlConnection(connStr);
con.Open();
str = "WITH x AS (SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp) FROM AISDb where action = 'IN'), y As(SELECT *, rna = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp)FROM AISDb where action='OUT') SELECT y.ID,x.ID,x.PersonName,y.PersonName,x.PersonRFID, DtATEDIFF(MINUTE, x.TimeStamp, y.TimeStamp) FROM x ,y where x.PersonRFID = y.PersonRFIDAnd x.rn=y.rnaAND  cast(x.Datestamp as date) = cast(y.Datestamp as date)Order By x.PersonName";
com = new SqlCommand(str, con);
sqlda = new SqlDataAdapter(com);
ds1 = new DataSet();
sqlda.Fill(ds1);

Clearly, there is an error in your SQL syntax... Have you tried running the query directly against the database? — Brian, Apr 03 '13 at 19:06
yes, It gives error the same error, but i don't understand that what is the mistake — Bilal Ahmed Janjua, Apr 03 '13 at 19:12

score 2 · Answer 1 · answered Apr 03 '13 at 19:23

2

Based on my first look,(without running the query), I can see few errors.

There is typo in datediff.

Put the space before AND in the x.PersonRFID = y.PersonRFIDAnd x.rn=y.rnaAND

Put the space before order in date)Order By

answered Apr 03 '13 at 19:23

gmail user

2,753
4
33
42

WITH x AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp) FROM AISDb where action = 'IN' ) , y As ( SELECT *, rna = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp) FROM AISDb where action='OUT' ) SELECT y.ID,x.ID,x.PersonName,y.PersonName,x.PersonRFID, DATEDIFF(MINUTE, x.TimeStamp, y.TimeStamp) FROM x , y where x.PersonRFID = y.PersonRFID And x.rn=y.rna AND cast(x.Datestamp as date) = cast(y.Datestamp as date) Order By x.PersonName; now i m using this query and it gives fine result when queried with database but not in c# – Bilal Ahmed Janjua Apr 03 '13 at 19:33
Debug the code, put the break point, copy the query from the Locals or Watch Window, and paste it into your Management Studio to see the difference – Muhammad Amin Apr 03 '13 at 19:44
What is the exact error you're getting? I need more description to answer question? – gmail user Apr 04 '13 at 12:51

score 1 · Answer 2 · answered Apr 03 '13 at 19:20

1

You have a couple issues with your query that are probably causing your error. First change DtATEDIFF to DATEDIFF (this may have been a typo).

More importantly, fix your spacing. You have fields running into each other.

See this Fiddle with both your example and the one with spacing:

http://sqlfiddle.com/#!3/47f7e/1

You're getting that error because of this:

y.PersonRFIDAnd x

The x at the end is throwing the error. Fix this one and the one after it (before cast) and it should work.

answered Apr 03 '13 at 19:20

sgeddes

62,311
6
61
83

WITH x AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp) FROM AISDb where action = 'IN' ) , y As ( SELECT *, rna = ROW_NUMBER() OVER (PARTITION BY PersonRFID ORDER BY DateStamp) FROM AISDb where action='OUT' ) SELECT y.ID,x.ID,x.PersonName,y.PersonName,x.PersonRFID, DATEDIFF(MINUTE, x.TimeStamp, y.TimeStamp) FROM x , y where x.PersonRFID = y.PersonRFID And x.rn=y.rna AND cast(x.Datestamp as date) = cast(y.Datestamp as date) Order By x.PersonName; now i m using this query and it gives fine result when queried with database but not in c# – Bilal Ahmed Janjua Apr 03 '13 at 19:31

Sql Query error asp.net c#

2 Answers2