Malloy1446
asked on
MSAccess - SQL Statement - Syntax problem
I created this SQL statement as a query within MSAccess. It generates the exact report I need but when I paste the code into my asp page, I get an ERROR. PAGE CANNOT DISPLAY. Anyone know what the problem is?
<%
'Create recordset
Dim sql, objRS
sql = "SELECT gen.City, jls.Title, jls.cost " & _
"from ( " & _
"select ChartID, J1Title, J1Cost from tblJournals union "
"select ChartID, J2Title, J2Cost from tblJournals union "
"select ChartID, J3Title, J3Cost from tblJournals union "
"select ChartID, J4Title, J4Cost from tblJournals union "
"select ChartID, J5Title, J5Cost from tblJournals union "
"select ChartID, J6Title, J6Cost from tblJournals union "
"select ChartID, J7Title, J7Cost from tblJournals union "
"select ChartID, J8Title, J8Cost from tblJournals union "
"select ChartID, J9Title, J9Cost from tblJournals union "
"select ChartID, J10Title, J10Cost from tblJournals union "
"select ChartID, J11Title, J11Cost from tblJournals union "
"select ChartID, J12Title, J12Cost from tblJournals union "
"select ChartID, J13Title, J13Cost from tblJournals union "
"select ChartID, J14Title, J14Cost from tblJournals union "
"select ChartID, J15Title, J15Cost from tblJournals union "
"select ChartID, J16Title, J16Cost from tblJournals union "
"select ChartID, J17Title, J17Cost from tblJournals union "
"select ChartID, J18Title, J18Cost from tblJournals union "
"select ChartID, J19Title, J19Cost from tblJournals union "
"select ChartID, J20Title, J20Cost from tblJournals union "
"select ChartID, J21Title, J21Cost from tblJournals union "
"select ChartID, J22Title, J22Cost from tblJournals union "
"select ChartID, J23Title, J23Cost from tblJournals union "
"select ChartID, J24Title, J24Cost from tblJournals union "
"select ChartID, J25Title, J25Cost from tblJournals "
") jls " & _
"inner join tblGeneral as gen " & _
"on gen.ChartID=jls.ChartID "
If Request.querystring("sort") = "" then
sql = sql & "ORDER BY Title,City"
Else
sql = sql & "ORDER BY " & Request.querystring("sort")
End If
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sql, objConn
'First, display the TABLE header info:
Response.Write "<br><TABLE class='CCTable' cellspacing='0' width='400px' border='1' cellpadding='5'>"
Response.Write "<TR background-color:white;'>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b> </b></th>"
Response.Write "<TH class='CCTable' scope='col' width='50px'><b>Title</b></th>"
Response.Write "<TH class='CCTable' scope='col' width='125px'><b>Cost</b></th></tr>"
Response.Write "<TH class='CCTable' scope='col' width='150px'><b>Location</b></th>"
While Not objRS.EOF
counter = counter + 1
Response.Write "<TR><TD class='GeneralTable' scope='row' width='25px'>" & counter
Response.Write "<TD class='GeneralTable' scope='row' width='400px'>" & Title & " </TD>" & VbCrLf
Response.Write "<TD class='GeneralTable' scope='row' width='150px'>$" & Cost & ", " & objRS("State") & "</TD>" & VbCrLf
Response.Write "<TD class='GeneralTable' scope='row' width='100px'>" & City & " </TD>" & VbCrLf
'Advance the recordset
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
Response.Write "</TABLE></div>" & vbCrLf
%>ASKER
I am unable to unclick the "Enhanced Protected Mode"
I know the connection to the database is working.
I know the connection to the database is working.
try make sure the following settings in your IE settings?
try close all your IE browsers and start to retest
try close all your IE browsers and start to retest
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
sql = "SELECT gen.City, jls.Title, jls.cost " & _ "from ( " & _ "select ChartID, J1Title, J1Cost from tblJournals union "
you are missing (at least) 1 detail, the "cost" and "title" alias
sql = "SELECT gen.City, jls.Title, jls.cost " & _ "from ( " & _ "select ChartID, J1Title as Title, J1Cost as cost from tblJournals union "
you are missing (at least) 1 detail, the "cost" and "title" alias
sql = "SELECT gen.City, jls.Title, jls.cost " & _ "from ( " & _ "select ChartID, J1Title as Title, J1Cost as cost from tblJournals union "
besides that, i think your table design is not normalized, and could be largely simplified
instead of having 25*2 fields for title and cost, have them per row, and that would get you rid of this likely slow union query
note: try also union all instead of union ....
instead of having 25*2 fields for title and cost, have them per row, and that would get you rid of this likely slow union query
note: try also union all instead of union ....
ASKER
I've added UNION ALL to all the select statements.
I changed my IE settings to show HTTP settings. I am getting an "The page cannot be displayed because an internal server error has occurred." error.
I changed my IE settings to show HTTP settings. I am getting an "The page cannot be displayed because an internal server error has occurred." error.
sql = "SELECT gen.City, jls.Title as Title, jls.cost as cost " & _
"from ( " & _
"select ChartID, J1Title, J1Cost from tblJournals union all " & _
"select ChartID, J2Title, J2Cost from tblJournals union all " & _
"select ChartID, J3Title, J3Cost from tblJournals union all " & _
"select ChartID, J4Title, J4Cost from tblJournals union all " & _
"select ChartID, J5Title, J5Cost from tblJournals union all " & _
"select ChartID, J6Title, J6Cost from tblJournals union all " & _
"select ChartID, J7Title, J7Cost from tblJournals union all " & _
"select ChartID, J8Title, J8Cost from tblJournals union all " & _
"select ChartID, J9Title, J9Cost from tblJournals union all " & _
"select ChartID, J10Title, J10Cost from tblJournals union all " & _
"select ChartID, J11Title, J11Cost from tblJournals union all " & _
"select ChartID, J12Title, J12Cost from tblJournals union all " & _
"select ChartID, J13Title, J13Cost from tblJournals union all " & _
"select ChartID, J14Title, J14Cost from tblJournals union all " & _
"select ChartID, J15Title, J15Cost from tblJournals union all " & _
"select ChartID, J16Title, J16Cost from tblJournals union all " & _
"select ChartID, J17Title, J17Cost from tblJournals union all " & _
"select ChartID, J18Title, J18Cost from tblJournals union all " & _
"select ChartID, J19Title, J19Cost from tblJournals union all " & _
"select ChartID, J20Title, J20Cost from tblJournals union all " & _
"select ChartID, J21Title, J21Cost from tblJournals union all " & _
"select ChartID, J22Title, J22Cost from tblJournals union all " & _
"select ChartID, J23Title, J23Cost from tblJournals union all " & _
"select ChartID, J24Title, J24Cost from tblJournals union all " & _
"select ChartID, J25Title, J25Cost from tblJournals "
") jls " & _
"inner join tblGeneral as gen " & _
"on gen.ChartID=jls.ChartID "
you did not apply the other fix yet...
ASKER
Sorry Guy, I am at the learning stage using UNION ALL. I am unsure of what the other fix is.
please see my first post above
the uinion all is not an error fix, but a performance improvement suggestion
if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
the uinion all is not an error fix, but a performance improvement suggestion
if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
please see my first post above
the uinion all is not an error fix, but a performance improvement suggestion
if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
the uinion all is not an error fix, but a performance improvement suggestion
if the fix does not solve the error, you need to implement proper error handling so you can produce a proper error message:
https://support.microsoft.com/en-us/kb/300043
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SUCCESS!!
Had to add a WHERE clause to the SQL statement to eliminate CITY records where there was no Journal date.
THank you for your patience!
Had to add a WHERE clause to the SQL statement to eliminate CITY records where there was no Journal date.
THank you for your patience!
ASKER
THANK YOU!!!
try configure your browser so that it shows the exact error out for diagnosis.
"Internet Explorer cannot display the webpage" error
https://support.microsoft.com/en-sg/kb/956196
probably there are some syntax or connection errors within your page.