asked on

Expected End of Statement Error in SQL query

Getting Expected End of Statement Error, line 25 in the following page:

<%@ Language=VBScript %>

<%

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("DataConn")

sql = "SELECT a.ReqNum, CONVERT(varchar,a.PublishDate,101) AS PublishDate, a.Active, b.Label AS ReqStatus," &_
      "c.Label AS JobCategory, d.Label AS JobDepartment, e.Label AS JobTitle, f.Facility AS JobLocation," &_
      "g.Label AS EEOC, h.Label AS PayrollStatus, i.Label AS WageGrade, j.Label AS RFO," &_
      "a.City, a.State, a.Country, a.SalaryLow, a.SalaryHigh, a.PublishedSalary, a.OtherSkills, a.YearsExperience," &_
      "a.Education, a.Skills, a.Competencies, a.Replacing, a.HeadCount, k.JobDescText " &_
      "FROM ResAppHistory x " &_
      "INNER JOIN JobRequisitions a ON X.ReqID = a.ReqID " &_
      "LEFT OUTER JOIN _ReqStatusCodes b ON a.StatusCode=b.IDENTITYCOL" &_
      "LEFT OUTER JOIN _JobCategories c ON a.CatID=c.IDENTITYCOL " &_
      "LEFT OUTER JOIN _JobDepartments d ON a.DeptID=d.IDENTITYCOL " &_
      "LEFT OUTER JOIN _JobTitles e ON a.TitleID=e.IDENTITYCOL " &_
      "LEFT OUTER JOIN _JobLocations f ON a.LocID=f.IDENTITYCOL " &_
      "LEFT OUTER JOIN _EEOJobCodes g ON a.EEOCode=g.IDENTITYCOL " &_
      "LEFT OUTER JOIN _PayrollStatus h ON a.PrsID=h.IDENTITYCOL" &_
      "LEFT OUTER JOIN _WageGrade i ON a.GradeID=i.IDENTITYCOL" &_
      "LEFT OUTER JOIN _RFOJobCodes j ON a.RFOCode=j.IDENTITYCOL " &_
      "LEFT OUTER JOIN JobReqText k ON a.ReqID=k.ReqID" &_
      "WHERE X.ResID = "26094""

Set rsResults = Server.CreateObject("ADODB.RecordSet")
rsResults.Open sql, Conn

Set rsResults = rsResults.NextRecordSet
if not rsResults.BOF and not rsResults.EOF then
      while not rsResults.EOF
            ReqNum = rsResults("ReqNum")
            PublishDate = rsResults("PublishDate")
            Active = rsResults("Active")
            rsResults.Movenext
      wend
end if

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Req View</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link href="../_css/ats_default.css" rel="stylesheet" type="text/css">
<body bgcolor="#FFFFFF" onunload="return window_onunload()">

                  <div style="height:400px; width:350px; overflow:auto; border:solid; border-color:#005488;
                        border-width:2px; background-color:#FFF8DC; text-align:left;
                        padding-top:8px; padding-bottom:0px; padding-left:5px; padding-right:5px;">
                        <strong>Job Description</strong>
                        <br>Title: <strong>
                        <% while not .eof
                              %>
                              <tr>
                              <td valign="top" class="content">
                              <%=rsResults("ReqNum")%>">
                       <br/></td>
                              </tr>
                              <%
                              rsResults.movenext
                        wend %>

                        </strong>

                  </div>


</div>

</body>
</html>
<%
Conn.close
%>

SQL statement is good. Works in Query Analyzer and returns the results I need. Page is not complete. I will need to pull all results eventually. I am new to ASP (background is PHP) and have inherited a very large app with a lot of errors.
Needed urgently.

jitganguly

Why do you have this ?
Set rsResults = rsResults.NextRecordSet

I guess you are returnign only 1 recordsert ? Just comment that line and try
What is line 25 ?

mfkaminski

ASKER

Commented out, still no good. Line 25 is the last line of the query:
"WHERE X.ResID = "26094""

jitganguly

Oh take off double quotes

"WHERE X.ResID = 26094"

I think thats a number

mfkaminski

ASKER

Good. Got past that. Now the error is:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'OUTER'.
reqview_test.asp, line 32
And line 32 is:
rsResults.Open sql, Conn

SOLUTION

joeposter649

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jitganguly

Do a response.write of the SQL , run it in QA and check it

sql = "SELECT a.ReqNum, CONVERT(varchar,a.PublishDate,101) AS PublishDate, a.Active, b.Label AS ReqStatus," &_
"c.Label AS JobCategory, d.Label AS JobDepartment, e.Label AS JobTitle, f.Facility AS JobLocation," &_
"g.Label AS EEOC, h.Label AS PayrollStatus, i.Label AS WageGrade, j.Label AS RFO," &_
"a.City, a.State, a.Country, a.SalaryLow, a.SalaryHigh, a.PublishedSalary, a.OtherSkills, a.YearsExperience," &_
"a.Education, a.Skills, a.Competencies, a.Replacing, a.HeadCount, k.JobDescText " &_
"FROM ResAppHistory x " &_
"INNER JOIN JobRequisitions a ON X.ReqID = a.ReqID " &_
"LEFT OUTER JOIN _ReqStatusCodes b ON a.StatusCode=b.IDENTITYCOL" &_
"LEFT OUTER JOIN _JobCategories c ON a.CatID=c.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobDepartments d ON a.DeptID=d.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobTitles e ON a.TitleID=e.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobLocations f ON a.LocID=f.IDENTITYCOL " &_
"LEFT OUTER JOIN _EEOJobCodes g ON a.EEOCode=g.IDENTITYCOL " &_
"LEFT OUTER JOIN _PayrollStatus h ON a.PrsID=h.IDENTITYCOL" &_
"LEFT OUTER JOIN _WageGrade i ON a.GradeID=i.IDENTITYCOL" &_
"LEFT OUTER JOIN _RFOJobCodes j ON a.RFOCode=j.IDENTITYCOL " &_
"LEFT OUTER JOIN JobReqText k ON a.ReqID=k.ReqID" &_
"WHERE X.ResID = 26094"

Response.Write sql
Response.end

mfkaminski

ASKER

Change JobReq line. No good.

Yes, query was good in QA, but when copied from response.write output, get same error:
Incorrect syntax near the keyword 'OUTER'.

jitganguly

Can I see output from Respons.write sql. May be you need to add spaces etc.

mfkaminski

ASKER

Yes, it is weird. It is losing 'LEFT' at _jobCategories, _WageGrade, and _RFOJobCodes.

ASKER CERTIFIED SOLUTION

jitganguly

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mfkaminski

ASKER

Sorry, not losing left but losing space:
SELECT a.ReqNum, CONVERT(varchar,a.PublishDate,101) AS PublishDate, a.Active, b.Label AS ReqStatus,c.Label AS JobCategory, d.Label AS JobDepartment, e.Label AS JobTitle, f.Facility AS JobLocation,g.Label AS EEOC, h.Label AS PayrollStatus, i.Label AS WageGrade, j.Label AS RFO,a.City, a.State, a.Country, a.SalaryLow, a.SalaryHigh, a.PublishedSalary, a.OtherSkills, a.YearsExperience,a.Education, a.Skills, a.Competencies, a.Replacing, a.HeadCount, k.JobDescText FROM ResAppHistory x INNER JOIN JobRequisitions a ON X.ReqID = a.ReqID LEFT OUTER JOIN _ReqStatusCodes b ON a.StatusCode=b.IDENTITYCOLLEFT OUTER JOIN _JobCategories c ON a.CatID=c.IDENTITYCOL LEFT OUTER JOIN _JobDepartments d ON a.DeptID=d.IDENTITYCOL LEFT OUTER JOIN _JobTitles e ON a.TitleID=e.IDENTITYCOL LEFT OUTER JOIN _JobLocations f ON a.LocID=f.IDENTITYCOL LEFT OUTER JOIN _EEOJobCodes g ON a.EEOCode=g.IDENTITYCOL LEFT OUTER JOIN _PayrollStatus h ON a.PrsID=h.IDENTITYCOLLEFT OUTER JOIN _WageGrade i ON a.GradeID=i.IDENTITYCOLLEFT OUTER JOIN _RFOJobCodes j ON a.RFOCode=j.IDENTITYCOL LEFT OUTER JOIN _JobReqText k ON a.ReqID=k.ReqID WHERE X.ResID = 26094

jitganguly

So put space in ASP code liek this

sql = "SELECT a.ReqNum, CONVERT(varchar,a.PublishDate,101) AS PublishDate, a.Active, b.Label AS ReqStatus, " &_
"c.Label AS JobCategory, d.Label AS JobDepartment, e.Label AS JobTitle, f.Facility AS JobLocation, " &_
"g.Label AS EEOC, h.Label AS PayrollStatus, i.Label AS WageGrade, j.Label AS RFO, " &_
"a.City, a.State, a.Country, a.SalaryLow, a.SalaryHigh, a.PublishedSalary, a.OtherSkills, a.YearsExperience, " &_
"a.Education, a.Skills, a.Competencies, a.Replacing, a.HeadCount, k.JobDescText " &_
"FROM ResAppHistory x " &_
"INNER JOIN JobRequisitions a ON X.ReqID = a.ReqID " &_
"LEFT OUTER JOIN _ReqStatusCodes b ON a.StatusCode=b.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobCategories c ON a.CatID=c.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobDepartments d ON a.DeptID=d.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobTitles e ON a.TitleID=e.IDENTITYCOL " &_
"LEFT OUTER JOIN _JobLocations f ON a.LocID=f.IDENTITYCOL " &_
"LEFT OUTER JOIN _EEOJobCodes g ON a.EEOCode=g.IDENTITYCOL " &_
"LEFT OUTER JOIN _PayrollStatus h ON a.PrsID=h.IDENTITYCOL " &_
"LEFT OUTER JOIN _WageGrade i ON a.GradeID=i.IDENTITYCOL " &_
"LEFT OUTER JOIN _RFOJobCodes j ON a.RFOCode=j.IDENTITYCOL " &_
"LEFT OUTER JOIN JobReqText k ON a.ReqID=k.ReqID " &_
"WHERE X.ResID = 26094"

joeposter649

Now why didn't I think of that? ;)

jitganguly

B'cos its TGIF :-)

mfkaminski

ASKER

Yes, groovy. Spaces fixed that. Finally not returning errors, but is not returning list of ReqNum. And this is where my unfamiliarity with ASP comes in.

The query returns a complete list of records with all information. There are 5 records and I need to loop through and show complete info for all records. How do I loop through the fields and then call them specifically in the body later.

jitganguly

Somethign like thsi will work
<table>
<%
if not rsResults.BOF and not rsResults.EOF then
while not rsResults.EOF
%>
<tr><td><%=rsResults("ReqNum")%></td>
<td><%= rsResults("PublishDate")%></td>
<td><%=rsResults("Active")%></td>
</tr>
<% rsResults.Movenext
wend
%>
</table>

mfkaminski

ASKER

Actually, I know this is starting a new question. So I can award these points and start a new question if you guys will stay with me.

jitganguly

Sure, go ahead

jitganguly

Why split ? I deserve full points here ?

mfkaminski

ASKER

Actually, if you look at joeposter649 first response, he said the same.