Error: Microsoft VBScript compilation error '800a0409'

webXtreme · February 2nd, 2009, 11:27 AM

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

%>

dparsons · February 2nd, 2009, 11:56 PM

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

Old Pedant · February 3rd, 2009, 01:51 AM

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

webXtreme · February 3rd, 2009, 11:00 AM

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

%>

Old Pedant · February 4th, 2009, 03:58 AM

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.