|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0 |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
February 2nd, 2009, 11:27 AM
|
Authorized User
|
|
Join Date: Nov 2006
Posts: 14
Thanks: 2
Thanked 0 Times in 0 Posts
|
|
Error: Microsoft VBScript compilation error '800a0409'
I'm getting the following error message when I try to run my ASP page:
Microsoft VBScript compilation error '800a0409'
Unterminated string constant
/policy/ecwsi/policy_states/state_policy.asp, line 20
strSQL = "select [idPS] as ID,[state],[titlePS] as title,[integPS],[qualityPS],[diversityPS],[compPS],'Professional Standards' as rowHeader,1 as sortKey from [profStandards] where [state]='Arkansas'
----------------------------------------------------------^
Here's my code: (PLEASE HELP - I need this fixed for our funders)
<%
'Dimension variables
Dim rs, connStr, strSQL
' Recordset Object
Set connStr = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
' Defining database connection
connStr.ConnectionString = path
connStr.Provider = provider
connStr.open
strSQL = "select [idPS] as ID,[state],[titlePS] as title,[integPS],[qualityPS],[diversityPS],[compPS],'Professional Standards' as rowHeader,1 as sortKey from [profStandards] where [state]='Arkansas'
Union
select [idF] as ID, [state],[titleF] as title,[integF],[qualityF],[diversityF],[compF],"Finance" as rowHeader,6 as sortKey
from [finance]
where [state]="Arkansas"
UNION select[idAS] as ID, [state],"Overarching: " & [titleAS] & "<br><br>Professional Development Specific: "&[title2AS] as title,[integAS],[qualityAS],[diversityAS],[compAS],"Advisory Structure" as rowHeader,4 as sortKey
from [advisory]
where [state]="Arkansas"
ORDER BY sortKey;
Union
select [idAR] as ID, [state],[titleAR] as title,[integAR],[qualityAR],[diversityAR],[compAR],"Articulation" as rowHeader, 3 as sortKey
from [articulation]
where [state]="Arkansas"
Union
select [idCP] as ID, [state],[titleCP] as title,[integCP],[qualityCP],[diversityCP],[compCP],"Career Pathways" as rowHeader,2 as sortKey
from [careerPathways]
where [state]="Arkansas"
Union
select [idD] as ID, [state],[titleD] as title,[integD],[qualityD],[diversityD],[compD],"Data" as rowHeader,5 as sortKey
from [data]
where [state]="Arkansas"
order by sortKey, title
rs.Open strSQL, connStr
%>
__________________
webXtreme
|
February 2nd, 2009, 11:56 PM
|
Wrox Author
|
|
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
Wow. I am not sure what Database you are using but you might want to move this into a Stored Procedure or a Query (access) because this is going to be a management nightmare!
There are alot of things wrong with your code, most noteably is that you are trying to span multiple lines of code. To span multiple lines of code in VBScript you have to do something like:
Dim foo
foo = "this is a very long string " &_
"that I have put on two lines" &_
"but this is what it would look like with a third line"
To this end you will need an opening and closing " " on each line of your SQL followed by &_ that tells the interpeter that the string is carried on on the next line.
Second if you have to use " in the query itself, such as you do with where [state]="Arkansas" you must escape the " so that VBScript knows you actually want to have " inside of the string. The above examples becomes:
where [state]=""Arkansas""
Clean those bugs up and see what happens.
hth.
-Doug
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
|
The Following User Says Thank You to dparsons For This Useful Post:
|
|
February 3rd, 2009, 01:51 AM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
Actually, using [state]="Arkansas" is almost surely a mistake.
Access, for example, allows this usage. But it's not standard SQL and *CAN* cause problems (for example in SQL Server, where by default that would be doing the same as [state]=Alabama meaning you be look for a *FIELD* named Alabama).
I would *STRONGLY* recommend that you use [state]='Alabama'
p.s., I am guessing this is Access, because of the [state], which indeed is needed with Access in some circumstances. In which case I *know* I am right that you should *NOT* use "..." for strings. Use '...' as I suggested.
&&&&&&&&&&&&&&&&&&&&&
As another minor point, you do NOT need to put "AS" clauses in any SELECT except the very first one. In fact, if you do so they are COMPLETELY IGNORED.
That is, if you do something like:
Code:
SELECT name, age FROM people
UNION
SELECT company, revenue FROM companies
Then the result set from that will *ALWAYS* look like
Code:
name :: age
john :: 32
IBM :: 17,375,281.22
The names of the fields are ONLY picked up from the very first SELECT.
So it truly is pointless to do (example)
Code:
select [idAR] as ID, [state],[titleAR] as title,[integAR],[qualityAR],[diversityAR],[compAR],"Articulation" as rowHeader, 3 as sortKey
|
The Following User Says Thank You to Old Pedant For This Useful Post:
|
|
February 3rd, 2009, 11:00 AM
|
Authorized User
|
|
Join Date: Nov 2006
Posts: 14
Thanks: 2
Thanked 0 Times in 0 Posts
|
|
OK. everyone I fixed my code and it worked. To answer your question- yes, I'm using Access. No more error message. THANKS A BUNCH!!
NEW CODE:
strSQL = "SELECT advisory.*, data.*, finance.*, articulation.*, careerPathways.*, profStandards.* FROM advisory, articulation, careerPathways, data, finance, profStandards WHERE advisory.state='Arkansas' AND data.state='Arkansas' AND finance.state='Arkansas' AND articulation.state='Arkansas' AND careerPathways.state='Arkansas' AND profStandards.state='Arkansas';"
I now need to figure out how to display all the fields where state = Arkansas from each table in the database. I currently have 6 tables (which you can see above). I would like to Response.Write and display each record in each table that list Arkansas. Any suggestions? Should I start another Thread:
My code:
<%
Response.Write " <table width='90%' border='1' cellspacing='0' cellpadding='0'>"
Response.Write "<tr><th rowspan='2' bgcolor='#CCCCCC'>Blueprint Essential Policy Area </th>"
Response.Write "<th rowspan='2' bgcolor='#CCCCCC'>State Policy </th>"
Response.Write "<th colspan='4' bgcolor='#99FFFF'>Blueprint Policy-Making Principles * </th>"
Response.Write "</tr>"
Response.Write "<tr><th bgcolor='#99FFFF'>Integration<br /></th>"
Response.Write "<th bgcolor='#99FFFF'>Quality Assurance</th>"
Response.Write "<th bgcolor='#99FFFF'>Diversity, Inclusion, and Access</th>"
Response.Write "<th bgcolor='#99FFFF'>Compensation Parity</th>"
Response.Write "</tr><tr> "
Response.Write "<th><div align='left'>Professional Standards </div></th>"
Response.Write "<th class=><div align='left'>"& rs("titlePS") &" </div></th>"
Response.Write "<th>" & rs("integPS") & "</th>"
Response.Write "<th>" & rs("qualityPS") & "</th>"
Response.Write " <th>" & rs("diversityPS") & "</th>"
Response.Write "<th>" & rs("compPS") & "</th>"
Response.Write "</tr><tr>"
Response.Write " <th><div align='left'>Career Pathways </div></th>"
Response.Write "<th class=><div align='left'>"& rs("titleCP") &" </div></th>"
Response.Write "<th>" & rs("integCP") & "</th>"
Response.Write "<th>" & rs("qualityCP") & "</th>"
Response.Write " <th>" & rs("diversityCP") & "</th>"
Response.Write "<th>" & rs("compCP") & "</th>"
Response.Write "</tr><tr>"
Response.Write "<th><div align='left'>Articulation</div></th>"
Response.Write "<th class=><div align='left'>"& rs("titleAR") &" </div></th>"
Response.Write "<th>" & rs("integAR") & "</th>"
Response.Write "<th>" & rs("qualityAR") & "</th>"
Response.Write " <th>" & rs("diversityAR") & "</th>"
Response.Write "<th>" & rs("compAR") & "</th>"
Response.Write "</tr><tr>"
Response.Write "<th><div align='left'>Advisory Structure</div></th>"
Response.Write "<th class=><div align='left'>"& rs("titleAS") &" </div></th>"
Response.Write "<th>" & rs("integAS") & "</th>"
Response.Write "<th>" & rs("qualityAS") & "</th>"
Response.Write " <th>" & rs("diversityAS") & "</th>"
Response.Write "<th>" & rs("compAS") & "</th>"
Response.Write "</tr><tr>"
Response.Write "<th><div align='left'>Advisory Structure</div></th>"
Response.Write "<th class=><div align='left'>"& rs("title2AS") &" </div></th>"
Response.Write "<th>" & rs("integAS") & "</th>"
Response.Write "<th>" & rs("qualityAS") & "</th>"
Response.Write " <th>" & rs("diversityAS") & "</th>"
Response.Write "<th>" & rs("compAS") & "</th>"
Response.Write "</tr><tr>"
Response.Write "<th><div align='left'>Data</div></th>"
Response.Write "<th class=><div align='left'>"& rs("titleD") &" </div></th>"
Response.Write "<th>" & rs("integD") & "</th>"
Response.Write "<th>" & rs("qualityD") & "</th>"
Response.Write " <th>" & rs("diversityD") & "</th>"
Response.Write "<th>" & rs("compD") & "</th>"
Response.Write "</tr><tr>"
Response.Write "<th><div align='left'>Financing</div></th>"
Response.Write "<th class=><div align='left'>"& rs("titleF") &" </div></th>"
Response.Write "<th>" & rs("integF") & "</th>"
Response.Write "<th>" & rs("qualityF") & "</th>"
Response.Write " <th>" & rs("diversityF") & "</th>"
Response.Write "<th>" & rs("compF") & "</th>"
Response.Write "</tr></table> "
Response.Write "<p> </p>"
'Move to the next record in the recordset
rs.MoveNext
'Reset server objects
rs.Close
Set rs = Nothing
Set connStr = Nothing
%>
__________________
webXtreme
|
February 4th, 2009, 03:58 AM
|
Friend of Wrox
|
|
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
|
|
Sorry, but no. That new code is very very wrong.
You will end up getting the CARTESIAN PRODUCT of all matches.
You need to go back to your ORIGINAL code and just fix it. It was RIGHT except for the really MINOR syntax problems we showed you.
But you DO need to *STOP* using RESPONSE.WRITE to output blocks of HTML code. It's harder to write, harder to read, and somewhat slower. (Despite what certain websites written in 1998 and 1999 would say, even though the were right back in those dark ages.)
So:
This is my guess as to something that would work. But it's just a guess. Surely an error or two in it since I just typed it in off the top of my head.
Code:
iniSQL = "SELECT idPS,[state],titlePS,integPS,qualityPS,diversityPS," _
& " compPS, 'Professional Standards' as rowHeader, 1 as sortKey " _
& "FROM profStandards " _
& "WHERE [state]='$STATE$' " _
& " UNION " _
& "SELECT idF,[state],titleF,integF,qualityF,diversityF,compF,'Finance',6 " _
& "FROM finance " _
& "WHERE [state]='$STATE$' " _
& " UNION " _
& "SELECT idAS, [state], 'Overarching: ' & titleAS & '<br><br>Professional Development Specific: ' & title2AS, " _
& " integAS,qualityAS,diversityAS,compAS,'Advisory Structure',4 " _
& "FROM advisory " _
& "WHERE [state]='$STATE$' " _
& " UNION " _
& "SELECT idAR, [state],titleAR,integAR,qualityAR,diversityAR,compAR,'Articulation',3 " _
& "FROM articulation " _
& "WHERE [state]='$STATE$' " _
& " UNION " _
& "SELECT idCP, [state],titleCP,integCP,qualityCP,diversityCP,compCP,'Career Pathways',2 "
& "FROM careerPathways " _
& "WHERE [state]='$STATE$' " _
& " UNION " _
& "SELECT idD, [state],titleD,integD,qualityD,diversityD,compD,'Data',5 " _
& "FROM data " _
& "WHERE [state]='$STATE$' " _
& "ORDER BY sortKey, title "
theState = Trim( Request("State") ) ' or however you let somebody choose a particular state
SQL = Replace( iniSQL, "$STATE$", theState )
Set RS = yourAlreadyOpenConnectionObject.Execute( SQL )
If RS.EOF Then
Response.Write "No results"
Response.End
End If
%>
<table width='90%' border='1' cellspacing='0' cellpadding='0'>
<tr>
<th rowspan='2' bgcolor='#CCCCCC'>Blueprint Essential<br/>Policy Area </th>
<th rowspan='2' bgcolor='#CCCCCC'>State Policy </th>
<th colspan='4' bgcolor='#99FFFF'>Blueprint Policy-Making Principles * </th>
</tr>
<tr>
<th bgcolor='#99FFFF'>Integration<br /></th>
<th bgcolor='#99FFFF'>Quality Assurance</th>
<th bgcolor='#99FFFF'>Diversity, Inclusion, and Access</th>
<th bgcolor='#99FFFF'>Compensation Parity</th>
</tr>
<%
Do Until RS.EOF
%>
<tr>
<th><%=RS("rowHeader")%></th>
<th><%=rs("titlePS")%></th>
<th><%=rs("integPS")%></th>
<th><%=rs("qualityPS")%></th>
<th><%=rs("diversityPS")%></th>"
<th><%=rs("compPS")%></th>
</tr>
<%
RS.MoveNext
Loop
RS.close
yourConnection.close
%>
</table>
You will notice that I didn't bother using *ANY* "AS" aliases. No point, really. The field names from the first table should work just fine.
I do have to say that I think your DB design could be improved a *LOT* by either consolidating all those tables into one (depends on what other fields they have) or extracting the fields you show there into a common table that links to the others. But I could be wrong on that. Depends a lot on how the tables are used for other purposes.
Last edited by Old Pedant; February 4th, 2009 at 04:00 AM..
|
The Following User Says Thank You to Old Pedant For This Useful Post:
|
|
|
|