80040e14 - SQL statement syntax error

Question

Post reply

80040e14 - SQL statement syntax error

Bob Howden

SSC Journeyman

Points: 87
More actions
November 27, 2008 at 6:33 am

#198223

Hello Everyone,
Happy Thanksgiving to everyone in the US.
I am really stuck here and could use an extra set of eyeballs to spot the error. The following is the error message:
|79|80040e14|[Microsoft][ODBC_SQL_Server_Driver][SQL_Server]Incorrect_syntax_near_')'
Here is the SQL Statement:
rs.Open "SELECT variable1, variable2 FROM dbo.FullDump WHERE (eventdate >= { fn NOW() } - 45) AND (LocationID = " & Session("LocationID") & ") AND (Status <> N'Duplicate') AND (Status <> N'Decline') AND (Status <> N'Delete') OR (eventdate >= { fn NOW() } - 45) AND (LocationID = " & Session("LocationID") & ") AND (Status IS NULL) ORDER BY EventID DESC", cn, 3, 3
I am at a loss - go figure there are so many close brackets!
Any help would be GREATLY appreciated!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Alexander G. SSCrazy Points: 2705 More actions · Answer 1

First try replacing the " by ' in

AND (LocationID = " & S

Bob Howden SSC Journeyman Points: 87 More actions · Answer 2

Hi Alexander,

I tried that and got the following error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

rs.Open "SELECT varible1, variable2, eventdate FROM dbo.FullDump WHERE (eventdate >= { fn NOW() } - 45) AND (LocationID = ' & Session("LocationID") & ') AND (Status <> N'Duplicate') AND (Status <> N'Decline') AND (Status <> N'Delete') OR (eventdate >= { fn NOW() } - 45) AND (LocationID = " & Session("LocationID") & ") ORDER BY EventID DESC", cn, 3, 3

Alexander G. SSCrazy Points: 2705 More actions · Answer 3

There are another two " in the last line to be substituted.

Bob Howden SSC Journeyman Points: 87 More actions · Answer 4

Hi Alexander,

I REALLY appreciate the help...thanks!

I replaced the double quotes with singles ... as in ...

AND (LocationID = " & Session("LocationID") & ")

became:

AND (LocationID = ' & Session("LocationID") & ')

in both cases where it occurs and had the same error.

Alexander G. SSCrazy Points: 2705 More actions · Answer 5

OK, now I see: It's not an SQL but an vb error: You try to use " inside the vb string argument. You have to escape these. I am not quit sure but I think you have to use "" instead of the single " enclosing the LocationID.

like

rs.Open "SELECT varible1, variable2, eventdate FROM dbo.FullDump WHERE (eventdate >= { fn NOW() } - 45) AND (LocationID = ' & Session(""LocationID"") & ') AND (Status <> N'Duplicate') AND (Status <> N'Decline') AND (Status <> N'Delete') OR (eventdate >= { fn NOW() } - 45) AND (LocationID = ' & Session(""LocationID"") & ') ORDER BY EventID DESC", cn, 3, 3

HTH

Bob Howden SSC Journeyman Points: 87 More actions · Answer 6

Hi Alexander,

I tried that and got the following error:

Microsoft VBScript compilation error '800a03ee'

Expected ')'

The arrow pointed to just after the first set of double quotes on :

AND (LocationID = " & Session(""LocationID"")

hhhmmm

Alexander G. SSCrazy Points: 2705 More actions · Answer 7

Now you again use the " in the wrong position:

AND (LocationID = " & Session(""LocationID"")

Look at my last post 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Let's break this up so that it's easier to see. (note, my VB is exceedingly rusty, so check for obvious errors)

single quotes (') are SQL string delimitors. Double quotes (") are VB string delimiters. So you have to use the " to end off the VB string constant when you concatenate the session variable in.

Dim sSQL as String

sSQL = "SELECT varible1, variable2, eventdate FROM dbo.FullDump WHERE (eventdate >= { fn NOW() } - 45) AND (LocationID = " 'double quotes to end off the vb string

sSQL = sSQL & Session("LocationID") ' does this need casting to a character string, or will VB do that automatically

sSQL = sSQL & ") AND (Status <> N'Duplicate') AND (Status <> N'Decline') AND (Status <> N'Delete') OR (eventdate >= { fn NOW() } - 45) AND (LocationID = "

sSQL = sSQL & Session("LocationID")

sSQL = sSQL + ") AND (Status IS NULL) ORDER BY EventID DESC"

debug.print sSQL ' or the VB equivalent.

rs.Open sSQL, cn,3,3

That should work. If it doesn't, at least it's easier to find exactly where the error is.

If it gives a SQL error, post the query outputed by the print.

Two last words: SQL Injection.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bob Howden SSC Journeyman Points: 87 More actions · Answer 9

Thanks Gail and Alexander...that has got it!

I REALLY appreciate the assistance.

Have a great day!