SQL line returning an syntax error

Question

I'm trying to make a datagrid view that returns records for a certain user after today. The column names are correct. But it's giving me an error reading "incorrect syntax near '>'"

here is the code.

txtdate.Text = DateTime.Today.ToString("dd-MM-yyyy");
SqlConnection conn = Database.GetConnection();
SqlCommand cmd = new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" + IDBox1.Text+"AND ScheduledDateTime=>"+txtdate , conn);

try this ("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" + IDBox1.Text+"AND ScheduledDateTime>="+txtdate , conn) — zxc
– zxc, Commented Aug 16, 2013 at 10:59
technet.microsoft.com/en-us/library/ms174134(v=sql.105).aspx first > then = — slavoo
– slavoo, Commented Aug 16, 2013 at 11:00
You might want to read up on how to use parameters with SqlCommand: msdn.microsoft.com/en-us/library/… — jpw
– jpw, Commented Aug 16, 2013 at 11:05
with that the syntax error changed to "incorrect syntax near ','" — George Ian Guy Marsden
– George Ian Guy Marsden, Commented Aug 16, 2013 at 11:16

Joachim Isaksson · Accepted Answer · 2013-08-16 11:01:52Z

2

There are a few problems;

new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" +
                IDBox1.Text+"AND ScheduledDateTime=>"+txtdate , conn);

You forgot a space before AND, and the operator greater than or equal is >=, not =>;

new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" +
                IDBox1.Text+" AND ScheduledDateTime>="+txtdate , conn);

Also, you're not quoting the strings you're injecting into the SQL, you need to surround them by ';

new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID='" +
                IDBox1.Text+"' AND ScheduledDateTime>='"+txtdate+"'" , conn);

This query should run, but will still be vulnerable to SQL injection. You should really look into using parameters for your SQL commands instead of building SQL parameters as strings.

answered Aug 16, 2013 at 11:01

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

3 Comments

jpw Over a year ago

+1 for pointing out the injection problem, was just about to post an answer about that as well but you beat me to it :)

George Ian Guy Marsden Over a year ago

it's not giving any errors, but it's not showing the results in the datagridview. The second i remove the ""' AND ScheduledDateTime>='"+txtdate+"'" " bit it shows the records in the datagride view. I don't know what's up with it. but you seem inteligent.

Joachim Isaksson Over a year ago

@GeorgeIanGuyMarsden The problem when building SQL queries as strings is that if the date format of the web server and the database differ, you may not get any hits comparing dates as strings. If you add the date as a parameter instead, that problem won't exist.

valex · Accepted Answer · 2013-08-16 10:59:39Z

1

As soon ScheduledDateTime is a datetime field you should compare it with a for example a string constant so you should add ' around this constant.

And also change => to >=

SqlCommand cmd = new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" + IDBox1.Text+" AND ScheduledDateTime>='"+txtdate+"'" , conn);

answered Aug 16, 2013 at 10:59

valex

24.2k7 gold badges47 silver badges62 bronze badges

3 Comments

George Ian Guy Marsden Over a year ago

it's not giving any errors, but it's not showing the results in the datagridview

valex Over a year ago

@GeorgeIanGuyMarsden: What DataBase you use? It's important when you use String->DateTime conversion to select "dd-MM-yyyy" format or another.

George Ian Guy Marsden Over a year ago

ok, well I changed for format, here are records. but some of these dates are before today. Where as the SQL command asks for only records of today and after. what can i do?

Jonny · Accepted Answer · 2013-08-16 10:59:39Z

0

Do you want greater than or equals?

SqlCommand cmd = new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" + IDBox1.Text+"AND ScheduledDateTime>="+txtdate , conn);

Just put the equals and the greater than the other way around.

answered Aug 16, 2013 at 10:59

Jonny

2,5191 gold badge25 silver badges43 bronze badges

1 Comment

George Ian Guy Marsden Over a year ago

it's not giving any errors, but it's not showing the results in the datagridview

Milen · Accepted Answer · 2013-08-16 11:00:16Z

0

You've got the sequence wrong first > then = in you last where condition..

txtdate.Text = DateTime.Today.ToString("dd-MM-yyyy");


        SqlConnection conn = Database.GetConnection();
        SqlCommand cmd = new SqlCommand("SELECT * FROM HomeCareVisit WHERE MedicalStaffID=" + IDBox1.Text+"AND ScheduledDateTime>="+txtdate , conn);

answered Aug 16, 2013 at 11:00

Milen

8,8977 gold badges47 silver badges59 bronze badges

1 Comment

George Ian Guy Marsden Over a year ago

it's not giving any errors, but it's not showing the results in the datagridview

Collectives™ on Stack Overflow

SQL line returning an syntax error

4 Answers 4

3 Comments

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related