"Incorrect syntax near '12'." exception thrown

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

30 Replies - 14005 Views - Last Post: 22 June 2014 - 09:23 AM Rate Topic: -----

#1 higherprimate   User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

"Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 02:06 AM

It works when I run it in SQL SERVER:

select * from tbgame where visitorteamid=2 and HomeTeamID=15 or visitorteamid=2 and HomeTeamID=15 and GameDate='04/19/2014'



But I get the error when trying it in c#:

 SqlDataAdapter da = new SqlDataAdapter("select * from tbgame where visitorteamid=" + lbVisitorTeam.SelectedValue +
                        "and hometeamid=" + lbHomeTeam.SelectedValue + "OR visitorteamid=" + lbHomeTeam.SelectedValue + "and hometeamid="
                        + lbVisitorTeam.SelectedValue + "and gamedate=" + dateTimePicker1.Value.Date, conn);



Is This A Good Question/Topic? 0
  • +

Replies To: "Incorrect syntax near '12'." exception thrown

#2 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 02:11 AM

What is the error?
Was This Post Helpful? 0
  • +
  • -

#3 higherprimate   User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 02:20 AM

Incorrect syntax at '12'. I'm guessing it has to do with the time part of the date time value which is 12 am

Actually I'm sure it is because once I remove the date part of the sql query in visual studio it works
Was This Post Helpful? 0
  • +
  • -

#4 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 02:29 AM

Can you avoid constructing your sql query like this, it's a bad practise and can lead to sql injection attacks. Most sql syntax error can be resolved by using parameterized sql queries
Was This Post Helpful? 3
  • +
  • -

#5 Charles:)   User is offline

  • D.I.C Regular

Reputation: 149
  • View blog
  • Posts: 359
  • Joined: 26-November 09

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 04:59 AM

It's probably because you haven't included spaces, e.g. change

"OR visitorteamid="


To

" OR visitorteamid="

Was This Post Helpful? 2
  • +
  • -

#6 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7445
  • View blog
  • Posts: 25,083
  • Joined: 05-May 12

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 05:33 AM

Charles:) has the eagle eye! The problem is very likely that. Any which way, Michael26's advice is spot on on how to avoid these problems.
Was This Post Helpful? 0
  • +
  • -

#7 Charles:)   User is offline

  • D.I.C Regular

Reputation: 149
  • View blog
  • Posts: 359
  • Joined: 26-November 09

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 05:36 AM

Agreed, always better to use parameters, but if you absolutely must build your string the way you're doing, use the System.String.Format function rather than all these concatenations - it's much easier to read and will allow you to spot these sorts of problems easily.
Was This Post Helpful? 1
  • +
  • -

#8 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 06:25 AM

I wrote the article that Michael linked. Please read it. Parameterized queries are not harder to use or write, but they grant you a level of protection and ease of reading that is totally worth it. It makes me sad to see that there are still tutorials out there giving the bad advice to just concatenate your SQL statements together. That's begging for a SQL injection attack. And not just that, they can ruin your query by including a simple single-quote character, completely by accident, and it will make your SQL statement junk.

Please read the linked article and use it.

Second note: when you have problems with strings, the easiest way to diagnose it is to print it out. Print it to the console, or a TextBox, or a MessageBox.Show, or whatever. Use the debugger and a breakpoint to see exactly what it is. However you do it, strings are the easiest thing to debug, because we can just look at them and read them. If you looked at your string printed out, you'd see it's missing spaces.
Was This Post Helpful? 3
  • +
  • -

#9 higherprimate   User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 09:29 AM

I get the same error when adding spaces...
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 09:32 AM

So...have you tried what I've said at all? You can at least print/display your SQL string so you can visually look and see what's wrong with it. You can even copy the output and run it directly in Sql Server Management Studio to get more detailed error messages.

This appears to be a Windows Forms application. Just use MessageBox.Show and print your SQL statement before you execute it. The error should be really obvious.

But you really, really, really should read the linked tutorial and abandon string concatenation for queries. It's bad, bad design and you should avoid it.

Side note: you've been a member here long enough to know that your last response isn't good enough for us to help with. Show us the new code! We're not mind readers, we can't see what you changed.
Was This Post Helpful? 0
  • +
  • -

#11 higherprimate   User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 11:12 AM

SqlDataAdapter da = new SqlDataAdapter("select * from tbgame where visitorteamid=" + lbVisitorTeam.SelectedValue +
2
                       " and hometeamid= " + lbHomeTeam.SelectedValue + " OR visitorteamid= " + lbHomeTeam.SelectedValue + " and hometeamid= "
3
                       + lbVisitorTeam.SelectedValue + " and gamedate= " + dateTimePicker1.Value.Date, conn);




Was This Post Helpful? 0
  • +
  • -

#12 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7445
  • View blog
  • Posts: 25,083
  • Joined: 05-May 12

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 12:13 PM

Notice that in the first post where you showed us the SQL that does work, the date was in single quotes. I don't see where you are putting single quotes in your string in post #11. If you were using parameterized queries, you wouldn't even have to worry about the single quotes.
Was This Post Helpful? 0
  • +
  • -

#13 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 12:14 PM

Not like this, show us what is the output of that string(in a messagebox or string) and please read the tutorial i linked about making safe queries.
Was This Post Helpful? 0
  • +
  • -

#14 higherprimate   User is offline

  • D.I.C Head

Reputation: -8
  • View blog
  • Posts: 109
  • Joined: 14-December 13

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 01:03 PM

View PostSkydiver, on 19 June 2014 - 12:13 PM, said:

Notice that in the first post where you showed us the SQL that does work, the date was in single quotes. I don't see where you are putting single quotes in your string in post #11. If you were using parameterized queries, you wouldn't even have to worry about the single quotes.



The original post included both the SQL Server query code (which was just used to test) and the C# code. The code in post #11 was just the C# code. This is the reason the DateTime value is in single quotes. As far as printing the output of the string on a label, do you mean taking the following string and assigning it to a label on the form?

"select * from tbgame where visitorteamid=" + lbVisitorTeam.SelectedValue +
" and hometeamid= " + lbHomeTeam.SelectedValue + " OR visitorteamid= " + lbHomeTeam.SelectedValue + " and hometeamid= "
+ lbVisitorTeam.SelectedValue + " and gamedate= " + dateTimePicker1.Value.Date


Was This Post Helpful? 0
  • +
  • -

#15 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: "Incorrect syntax near '12'." exception thrown

Posted 19 June 2014 - 02:03 PM

Take whatever the value is in that store it in string variable then display that.
Something like this

string sqlQuery = "select * from tbgame where visitorteamid=" + lbVisitorTeam.SelectedValue +
                              " and hometeamid= " + lbHomeTeam.SelectedValue + " OR visitorteamid= " +
                              lbHomeTeam.SelectedValue + " and hometeamid= "
                              + lbVisitorTeam.SelectedValue + " and gamedate= " + dateTimePicker1.Value.Date;



Put a breakpoint on sqlQuery, run that statement and see the output, or you can just put sqlQuery in MessageBox.Show(); and what the MessageBox prints out.
Was This Post Helpful? 1
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3