ASP syntax error when move from Access to SQL Server

[jennypretty]

Hello,

Thanks for reading my post.

I use ASP. I have been getting the Syntax error near keyword 'Then'. The query works fine with MS Access database. But when I upgraded Access to SQL Server 2000 as a back-end, it causes this error. I think Iff does not work on SQL so I converted it to CASE. It keeps showing the syntax error.

Can you please take a look at this code to see why I keep getting syntax error near keyword 'then'?

Thanks very much.


MS Access:
<%
" (Sum(IIf((tbl_A.AStart>'" & Request.Form("ERange") & "' " & _
" And tbl_A.AEnd>'" & Request.Form("ERange") & "') Or " & _
" (tbl_A.AStart<'" & Request.Form("SRange") & "' And " & _
" tbl_A.AEnd<'" & Request.Form("SRange") & "'),0, " & _
"IIf(tbl_A.AStart>='" & Request.Form("SRange") & "' And " & _
" tbl_A.AEnd>'" & Request.Form("ERange") & "', " & _
"DateDiff('d',tbl_A.AStart,'" & Request.Form("ERange") & "')+1,1))))))) AS Range"




SQL Server
"(Sum(CASE WHEN ((tbl_A.AStart>'" & Request.Form("ERange") & "' And " & _
"tbl_A.AEnd>'" & Request.Form("ERange") & "') Or" & _
"(tbl_A.AStart<'" & Request.Form("SRange") & "' And " & _
"tbl_A.AEnd<'" & Request.Form("SRange") & "') Then 0 END," & _
"CASE WHEN (tbl_A.AStart>='" & Request.Form("SRange") & "' And " & _
"tbl_A.AEnd>'" & Request.Form("ERange") & "' Then " & _
"DateDiff('d',tbl_A.AStart,'" & Request.Form("ERange") & "')+1,1))))))) AS Range END," & _
%>


Thanks very much.

[jennypretty]

I changed the order of End but it still shows the same error.

Here are the string and the last part of the query, but I don't think they caused the syntax error so I din't post them at the beginning.

strRpt="Time from <BR>" & Request.Form("SRange") & " and " & Request.Form("ERange") & ""
If Request.QueryString("Action")="Rpt" Then
strTeam=Request.Form("ST")
If strST="All" Then
strST="%"
End If

Last part of query:

"FROM ((tbl_A INNER JOIN tbl_T ON tbl_A.TID = tbl_T.TID) INNER JOIN" & _
" tbl_S ON tbl_A.SID = tbl_S.SID) INNER JOIN tbl_P ON" & _
" tbl_T.PID = tbl_P.PID"
" WHERE ((tbl_S.st) LIKE '" & strSt & "') "
" AND ((tbl_S.PEnd) Is Null Or (tbl_S.PEnd)>=Date()) "
" GROUP BY tbl_A.SID, '" & Request.Form("SRange") & "', '" & Request.Form("ERange") & "'"
" ORDER BY " & strSort & ";"

rsReport.Open strSQL, strCon, 3


Thanks very much.

[Old Pedant]

So where is your DEBUG????

Where do you Response.Write the SQL *before* executing it, so you can see what you are actually trying to do?

[jennypretty]

I didn't include every thing from the code, only what I changed cause errors.

Response.Write (strST)

thanks.

[Old Pedant]

Ummm...*YOU* have to COPY/PASTE what that SHOWS you to here. I can't run that one line of code on my machine and have it do anything.

[jennypretty]

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'Then'.

travet.asp, line 199

[Old Pedant]

*SIGH*

Let's try this again.

I want you to do

Response.Write "DEBUG SQL: " & strSQL & "<HR>"
' just before this line:
rsReport.Open strSQL, strCon, 3

And tell me what the DEBUG line shows.

If you do not get any output from the debug line, then *temporarily* add one more line, thus:

Response.Write "DEBUG SQL: " & strSQL & "<HR>"
Response.End
' just before this line:
rsReport.Open strSQL, strCon, 3

I want to see the *actual* SQL you are using, AFTER your ASP code has mangled it.

[Old Pedant]

I *THINK* you need *SOMETHING* like this:

erange = CDate(Request.Form("ERange"))
srange = CDate(Request.Form("SRange"))

strSQL =
...
" SUM(" CASE & _
" WHEN ( tbl_A.AStart > '" & erange "' AND tbl_A.AEnd > '" & erange & "') " & _
" OR " & _
" (tbl_A.AStart < '" & srange & "' AND tbl_A.AEnd < '" & srange & "') " & _
" THEN 0 " & _
" WHEN tbl_A.AStart >= '" & srange & "' AND " & tbl_A.AEnd > '" & erange & "' " & _
" THEN DateDiff(d,tbl_A.AStart,'" erange & "') + 1 " & _
" ELSE 1 " & _
" END ) AS Range," & _
...
%>

But when you show only partial code it is hard to tell.

[Old Pedant]

I don't understand though. If tblA.AEnd is *ALWAYS* >= tblA.AStart then why do you need that complex test???

You should be able to simplify it to
erange = CDate(Request.Form("ERange"))
srange = CDate(Request.Form("SRange"))

strSQL =
...
" SUM(" CASE & _
" WHEN tbl_A.AStart > '" & erange & "' OR tbl_A.AEnd < '" & srange & "' " & _
" THEN 0 " & _
" WHEN tbl_A.AStart >= '" & srange & "' AND " & tbl_A.AEnd > '" & erange & "' " & _
" THEN DateDiff(d,tbl_A.AStart,'" erange & "') + 1 " & _
" ELSE 1 " & _
" END ) AS Range," & _
...
%>

[jennypretty]

Here it is:
(There are some more variables on this string because I removed some from the code I posted above).

SELECT tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co,(Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') Then 0 END,CASE WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' then DateDiff('d','1/1/2009','12/29/2009')+1 END,CASE WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' Then DateDiff('d',tbl_A.AStart,tbl_A.AEnd) END,CASE WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' Then DateDiff('d','1/1/2009',tbl_A.AEnd)+1 END,CASE WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' Then DateDiff('d',tbl_A.AStart,'12/29/2009')+1,1))))))) END AS Range,(Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') Then 0 END,CASE WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' Then DateDiff('d','1/1/2009','12/29/2009')+1 END,CASE WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' Then DateDiff('d',tbl_A.AStart,tbl_A.AEnd) END,CASE WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' Then DateDiff('d','1/1/2009',tbl_A.AEnd)+1 END, CASE WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' Then DateDiff('d',tbl_A.AStart,'12/29/2009')+1,1)))))))/(DateDiff('d','1/1/2009','12/29/2009')+1) END AS Percent FROM ((tbl_A INNER JOIN tbl_T ON tbl_A.TID = tbl_T.TID) INNER JOIN tbl_S ON tbl_A.SID = tbl_S.SID) INNER JOIN tbl_P ON tbl_T.PID = tbl_P.PID WHERE ((tbl_S.ST) LIKE '%') AND ((tbl_S.Co) LIKE '%') AND ((tbl_S.PEnd) Is Null Or (tbl_S.PEnd)>=Date()) AND ((tbl_P.PID)<>262)) GROUP BY tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co, '1/1/2009', '12/29/2009' ORDER BY tbl_S.Surname , tbl_S.Nickname;

[Old Pedant]

No, you are using CASE WHEN incorrectly.

It is used like this:
CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
WHEN condition3 THEN value3
...
ELSE value_default
END


NO COMMAS!! Do *NOT* repeat CASE or END in the same expression.

Also, serveral of your pairs of parentheses do not line up correctly. In fact, your use of parenthese is just plain strange and makes no sense at all.

Also, there is no such function as DATE() in SQL Server. GETDATE() is the closest thing, but it is really the same as NOW() [it is date AND time].

Also, in SQL Server, DATEDIFF( ) does *NO* use apostrophes around the time unit. THat is, just d and not 'd'.

Also, in SQL Server you don't need the parentheses around the mutiple JOINs.


SELECT tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co,
Sum(
CASE
WHEN (tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009')
Or
(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009')
Then 0
WHEN tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009'
then DateDiff(d,'1/1/2009','12/29/2009')+1
WHEN tbl_A.AStart>='1/1/2009' And tbl_A.AEnd<='12/29/2009'
Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd)
WHEN tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009'
Then DateDiff(d,'1/1/2009',tbl_A.AEnd)+1
WHEN tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009'
Then DateDiff(d,tbl_A.AStart,'12/29/2009')+1
ELSE 1
END ) AS Range,
Sum(
( CASE
WHEN (tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009')
Or
(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009')
Then 0
WHEN tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009'
Then DateDiff(d,'1/1/2009','12/29/2009')+1
WHEN tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009'
Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd)
WHEN tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009'
Then DateDiff(d,'1/1/2009',tbl_A.AEnd)+1
WHEN tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009'
Then DateDiff(d,tbl_A.AStart,'12/29/2009')+1
ELSE 1
END
) / (DateDiff(d,'1/1/2009','12/29/2009')+1)
) AS Percent
FROM tbl_A INNER JOIN tbl_T ON tbl_A.TID = tbl_T.TID
INNER JOIN tbl_S ON tbl_A.SID = tbl_S.SID
INNER JOIN tbl_P ON tbl_T.PID = tbl_P.PID
WHERE tbl_S.ST LIKE '%'
AND tbl_S.Co LIKE '%'
AND ( tbl_S.PEnd Is Null Or tbl_S.PEnd >= getDate() )
AND tbl_P.PID <> 262
GROUP BY tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co
ORDER BY tbl_S.Surname , tbl_S.Nickname;


Not sure I've got that all right. Try it directly in SQL Server--do *NOT* use ASP code for testing!--and adjust it until it is right.

[jennypretty]

it worked like charm.

Woh!

Big thanks.