Line 1: Incorrect syntax near ')'.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Line 1: Incorrect syntax near ')'.

Author

Topic

SQL_Learner81
Starting Member

2 Posts

Posted - 2007-12-20 : 11:12:45

Error #: -2147217900
Error Source: Microsoft OLE DB Provider for SQL Server
Error Desc: Line 1: Incorrect syntax near ')'.

We have an ASP page that calls a VB6 dll everytime someone logs into our application, inside the dll the SQL call is made. The application runs fine on the front end and everything is outputted to the webpage correctly but that error is logged everytime a person logs into the application. Over 80% of our error log table is filled up by this error. I've went through the ASP and dll several times and I can't see anything missing or wrong.

We are using:
SQL Server 2000 sp4
Windows 2003 Server sp1

Snippet of code from the dll:
If Not rsRegions.BOF And Not rsRegions.EOF Then
arrRegions = Split(rsRegions.Fields("region_code").Value, "|")
For x = 0 To UBound(arrRegions) - 1
strRegionSQL = strRegionSQL & "region_code LIKE '%" & arrRegions(x) & "%'"
If x <> (UBound(arrRegions) - 1) Then
strRegionSQL = strRegionSQL & " OR "
End If
Next x
End If

strSQL = "SELECT bulletinnumber, pagename, effdate = convert(varchar, effectivedate, 101), " & _
"expdate = convert(VarChar, expirationdate, 101), title, body, disploc " & _
"FROM system_bulletins "

If Trim(IstrDate) <> "" Then

strSQL = strSQL & "WHERE convert(VarChar, effectivedate, 120) >= '" & strInputDate & "' " & _
"AND convert(VarChar, effectivedate, 120) <= '" & strNow & "' " & _
"AND convert(VarChar, expirationdate, 120) >= '" & strNow & "' " & _
"AND (disploc <> 'AO' OR disploc is null) AND (" & strRegionSQL & _
") ORDER BY effectivedate "
Else
strSQL = strSQL & "WHERE convert(VarChar, expirationdate, 120) >= '" & strNow & "' " & _
"AND (disploc <> 'AO' OR disploc is null) AND (" & strRegionSQL & _
") ORDER BY effectivedate "
End If

Here's the SQL after the variables are filled:
SELECT bulletinnumber, pagename, effdate = convert(varchar, effectivedate, 101), expdate = convert(VarChar, expirationdate, 101), title, body, disploc FROM system_bulletins WHERE convert(VarChar, effectivedate, 120) >= '2007-12-19 07:45:53' AND convert(VarChar, effectivedate, 120) <= '2007-12-20 09:58:06' AND convert(VarChar, expirationdate, 120) >= '2007-12-20 09:58:06' AND (disploc <> 'AO' OR disploc is null) AND (region_code LIKE '%0001%') ORDER BY effectivedate

If I put the statement into Query Analyzer, it runs just fine. Any help would be greatly appreciated.

Thanks In Advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 12:00:49

Maybe ADO doe not support this old syntax anymore?

strSQL = "SELECT bulletinnumber, pagename, effdate = convert(varchar, effectivedate, 101), " & _
"expdate = convert(VarChar, expirationdate, 101), title, body, disploc " & _
"FROM system_bulletins "

strSQL = "SELECT bulletinnumber, pagename, convert(varchar, effectivedate, 101) as effdate, " & _
"convert(VarChar, expirationdate, 101) AS expdate, title, body, disploc " & _
"FROM system_bulletins "

also remove the convert thingies in the check, for speedier query.

strSQL = strSQL & "WHERE convert(VarChar, effectivedate, 120) >= '" & strInputDate & "' " & _
"AND convert(VarChar, effectivedate, 120) <= '" & strNow & "' " & _
"AND convert(VarChar, expirationdate, 120) >= '" & strNow & "' " & _
"AND (disploc <> 'AO' OR disploc is null) AND (" & strRegionSQL & _
") ORDER BY effectivedate "
Else
strSQL = strSQL & "WHERE convert(VarChar, expirationdate, 120) >= '" & strNow & "' " & _
"AND (disploc <> 'AO' OR disploc is null) AND (" & strRegionSQL & _
") ORDER BY effectivedate "
End If

strSQL = strSQL & "WHERE effectivedate >= '" & strInputDate & "' " & _
"AND effectivedate <= '" & strNow & "' " & _
"AND expirationdate >= '" & strNow & "' " & _
"AND (disploc <> 'AO' OR disploc is null) AND (" & strRegionSQL & _
") ORDER BY effectivedate "
Else
strSQL = strSQL & "WHERE expirationdate >= '" & strNow & "' " & _
"AND (disploc <> 'AO' OR disploc is null) AND (" & strRegionSQL & _
") ORDER BY effectivedate "
End If

E 12°55'05.25"
N 56°04'39.16"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2007-12-20 : 12:34:17

Are you sure that this is the actual statement that is sent to the database when the error occurs and are you sure that all the parameters are present? If the query you send to the database works in QA but not in the ASP then it can't be the same query.

Personally I would change all the CONVERT(varchar,... to CONVERT(varchar(50),... but I don't think that helps your issue.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SQL_Learner81
Starting Member

2 Posts

Posted - 2008-01-02 : 14:47:46

Finally figured it out, the call to the dll wasn't getting the userid passed to it.

Thanks for the help, very much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources