Solved

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax on LONG SQL Statement

Posted on 2007-03-26
9
460 Views
Last Modified: 2008-01-09
Have the below code.  I know the SQL statement is good as it runs fine in SQL 2000 query analyzer.  When I try to run it via ASP I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'tblDocumentInfo'.
/reports/NewDTReport.asp, line 80

Line 80 is the one that reads "set rs = conn.execute(strSQL)"

I can see no syntax errors.  Any Ideas?  I am an ASP newbie.

'Begin ASP Code

<!doctype html public "-//w3c//dtd html 3.2//en">

<html>
<head>
<title>New DT Report</title>
<STYLE TYPE="text/css">
      <!--
      .darkrow, .darkrow TD, .darkrow TH
      {
      background-color:052651;
      color:white;
      }
body {
      background-color: #990000;
}
.style1 {color: #FFFFFF}
.style3 {font-size: x-large}
a:link {
      color: #FFFFFF;
}
a:visited {
      color: #FFFFFF;
}
a:hover {
      color: #FFFFFF;
}
a:active {
      color: #FFFFFF;
}
      -->
      </STYLE>
      
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>
<body>
<table width="1054" border="0">
  <tr>
    <td><span class="style1"><img src="../images/v_logo.gif" width="96" height="70"></span></td>
    <td valign="middle"><div align="center"><span class="style1"><span class="style3">NEW DT Report </span></span></div></td>
    <td align="right" valign="bottom"><a href="show_all.asp"><img src="butt.gif" width="158" height="35" border="0"></a></td>
    <td align="right" valign="bottom"><a href="search.htm"><img src="searchbut.gif" width="125" height="35" border="0" align="bottom"></a></td>
  </tr>
  <tr>
    <td><span class="style1"><a href="Report_input.htm"><img src="runagain.gif" width="230" height="35" border="0"></a></span></td>
    <td valign="top">&nbsp;</td>
    <td valign="bottom">&nbsp;</td>
    <td valign="bottom">&nbsp;</td>
  </tr>
  <tr>
    <td width="231"><span class="style1"></span></td>
    <td width="513" valign="top"><div align="center"><span class="style1"></span></div></td>
    <td width="161" valign="bottom">&nbsp;</td>
    <td width="131" valign="bottom"><div align="right"></div></td>
  </tr>
</table>
<%
'Create Database Connection and Open the Recordset
set conn=Server.CreateObject("ADODB.Connection")
 strconn="DSN=reports;UID=reporman;PWD=reports"
 conn.Open strConn
strSQL = "select tblparty.sNameLast as 'Market Source',"&_
"sum( case when sMarketSource = 'AMERICAN TITLE COMPANY' THEN 1 ELSE 0 END) [AMERICAN TITLE]"&_
", sum( case when sMarketSource = 'ATLANTIC ALLIANCE' THEN 1 ELSE 0 END) [ATLANTIC ALLIANCE]"&_
", sum( case when sMarketSource = 'HOLGATE TITLE COMPANY' THEN 1 ELSE 0 END) [HOLGATE TITLE]"&_
", sum( case when sMarketSource = 'CANYON TITLE' THEN 1 ELSE 0 END) [CANYON TITLE]"&_
", sum( case when sMarketSource = 'DENVER TITLE COMPANY' THEN 1 ELSE 0 END) [DENVER TITLE]"&_
", sum(case when SMarketSource in"&_
"('AMERICAN TITLE COMPANY','ATLANTIC ALLIANCE','HOLGATE TITLE COMPANY','CANYON TITLE ',"&_
"'DENVER TITLE COMPANY') then 1 else 0 end) as Totals"&_
"From tblDocumentInfo inner join  tblparty on tblDocumentinfo.irecordid = tblparty.irecordid"&_
"Where( tblDocumentInfo.sMarketSource = 'American Title Company'"&_
"or tblDocumentInfo.sMarketSource = 'Atlantic Alliance'"&_
"or tblDocumentInfo.sMarketSource = 'Holgate Title Company'"&_
"or tblDocumentInfo.sMarketSource = 'Canyon Title '"&_
"or tblDocumentInfo.sMarketSource = 'Denver Title Company' )"&_
"and tblDocumentInfo.sInstrumentType in ('DEED OF TRUST','MORTGAGE')"&_
"and tblDocumentInfo.dtFiledDate >= convert(datetime, '2007-02-01' , 120)"&_
"and tblDocumentInfo.dtFiledDate < convert(datetime, '2007-03-01', 120)"&_
"and tblparty.tiPartyType = '2'"&_
"group by tblparty.sNameLast"
set rs = conn.execute(strSQL)
%>
<TABLE width="21%" border=3 cellpadding=3 cellspacing=0 bgcolor="#FFFFFF">
<tr CLASS="darkrow">
<td><div align="left"><strong>Market Source</strong></div></td>
<td><strong>AMERICAN TITLE</strong></td>
<td><strong>ATLANTIC ALLIANCE</strong></td>
<td><strong>HOLGATE TITLE</strong></td>
<td><strong>CANYON TITLE</strong></td>
<td><strong>DENVER TITLE</strong></td>
<td><strong>TOTAL</strong></td>
</tr>
<%
if (rs.eof) then
%>
<tr>
<td>
No records found for Report
</td>
</tr>
<%
'Loop through the recordset and display address information - Note the hyperlink in the name field to go to the Address_Book_Edit.asp page.
else
Do while not rs.eof
%>
<tr>
<td width="8%"><%=rs("Market Source")%>&nbsp</td>
<td width="10%"><%=rs("AMERICAN TITLE")%>&nbsp</td>
<td width="10%"><%=rs("ATLANTIC ALLIANCE")%>&nbsp</td>
<td width="10%"><%=rs("HOLGATE TITLE")%>&nbsp</td>
<td width="10%"><%=rs("CANYON TITLE")%>&nbsp</td>
<td width="10%"><%=rs("DENVER TITLE")%>&nbsp</td>
<td width="10%"><%=rs("Totals")%>&nbsp</td>
</tr>
<%
rs.movenext
loop
end if
%>
</tr>
</table>
</body>
</html>
0
Comment
Question by:ckangas7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 18796854
The error thrown on that line is telling you your SQL statement is wrong.

0
 
LVL 29

Accepted Solution

by:
rdivilbiss earned 200 total points
ID: 18796877
strSQL = "select tblparty.sNameLast as 'Market Source',sum( case when sMarketSource = 'AMERICAN TITLE COMPANY' THEN 1 ELSE 0 END) [AMERICAN TITLE], sum( case when sMarketSource = 'ATLANTIC ALLIANCE' THEN 1 ELSE 0 END) [ATLANTIC ALLIANCE], sum( case when sMarketSource = 'HOLGATE TITLE COMPANY' THEN 1 ELSE 0 END) [HOLGATE TITLE], sum( case when sMarketSource = 'CANYON TITLE' THEN 1 ELSE 0 END) [CANYON TITLE], sum( case when sMarketSource = 'DENVER TITLE COMPANY' THEN 1 ELSE 0 END) [DENVER TITLE], sum(case when SMarketSource in('AMERICAN TITLE COMPANY','ATLANTIC ALLIANCE','HOLGATE TITLE COMPANY','CANYON TITLE ','DENVER TITLE COMPANY') then 1 else 0 end) as TotalsFrom tblDocumentInfo inner join  tblparty on tblDocumentinfo.irecordid = tblparty.irecordidWhere( tblDocumentInfo.sMarketSource = 'American Title Company'or tblDocumentInfo.sMarketSource = 'Atlantic Alliance'or tblDocumentInfo.sMarketSource = 'Holgate Title Company'or tblDocumentInfo.sMarketSource = 'Canyon Title 'or tblDocumentInfo.sMarketSource = 'Denver Title Company' )and tblDocumentInfo.sInstrumentType in ('DEED OF TRUST','MORTGAGE')and tblDocumentInfo.dtFiledDate >= convert(datetime, '2007-02-01' , 120)and tblDocumentInfo.dtFiledDate < convert(datetime, '2007-03-01', 120)and tblparty.tiPartyType = '2'group by tblparty.sNameLast

And removing all your "&_ shows at the minimum you've cut out a lot of spaces when you broke you SQL command into sub strings.

I don't know if those missing spaces are causing SQL to choke or not, and I have no way to test your SQL statement without the same tables, etc.

Parsing it with Query Analyzer says.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
Server: Msg 103, Level 15, State 1, Line 1
The identifier that starts with 'select tblparty.sNameLast as 'Market Source',sum( case when sMarketSource = 'AMERICAN TITLE COMPANY' THEN 1 ELSE 0 END) [AMERICA' is too long. Maximum length is 128.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'select tblparty.sNameLast as 'Market Source',sum( case when sMarketSource = 'AMERICAN TITLE COMPANY' THEN 1 ELSE 0 END) [AMERICAN'.

So I guess you need to get your working SQL statement from your Query Analyzer or Enterprise manager and be more careful when parsing it into sub strings.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18797064
You need to output the result of strSQL before the line:
set rs = conn.execute(strSQL)

If you cannot tell what is the error from the output, post the result here.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Assisted Solution

by:walkerke
walkerke earned 50 total points
ID: 18798257
I believe you're missing some spaces in your string concatenation. There appear to be lines that end abruptly without a space and then pick up again at the beginning of the next line. For example:

>>>"'DENVER TITLE COMPANY') then 1 else 0 end) as Totals"&_
>>>"From tblDocumentInfo inner join  tblparty on tblDocumentinfo.irecordid = tblparty.irecordid"&_
>>>"Where( tblDocumentInfo.sMarketSource = 'American Title Company'"&_

will concatenat to:

'DENVER TITLE COMPANY') then 1 else 0 end) as TotalsFrom tblDocumentInfo inner join  tblparty on tblDocumentinfo.irecordid = tblparty.irecordidWhere( tblDocumentInfo.sMarketSource = 'American Title Company'

See "as TotalsFrom tblDocumentInfo" and "tblparty.irecordidWhere("

acperkins is right -- you need to output the result of the strSQL concatenation to see the actual query.
0
 

Author Comment

by:ckangas7
ID: 18800692
OK, can you please tell me how I can  output the result of strSQL before the line:
set rs = conn.execute(strSQL)?
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 18801356
response.write strSQL
on error resume next
set rs = conn.execute(strSQL)
if err then
    response.write err.number & " " & err.description & " " & err.source & "<br>" & strSQL
end if
0
 

Author Comment

by:ckangas7
ID: 18801637
All right that did it.  You were correct it was the way I parsed the SQL statement.  A few spaces here and there fixed the problem.  Awesome, I'm halfway home.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18801739
You are welcome.
0
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 18801741
LOL
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question