"Error Type:
Microsoft VBScript compilation (0x800A03EE)
Expected ')'
I was developing a simple html form that fed an asp page with three values to limit the SQL string, but I keep getting the above error. There are two date fields (from a text input) and a name (select option). For some reason the SQL String that is requesting the data from an Access BE, is incorrect for the title section called "SUBREGION". If I take it out, the the date fields are requested fine and the page will write HTML fine and display the data limited by the date fields. If I replace the Subregion variable with a literal value then it also works fine. I dont know what I am missing in my attempt to parse this variable. HELP!!
Here is the html:
<html>
<head>
<title>.: Index Pricing Database for Example :.</title>
</head>
<body bgcolor="#666666">
<form action = "navigant_2_1.asp" method="GET">
<table width="471" height="36" bgcolor="#FFFFFF">
<tr>
<td width="272" height="32" bgcolor="#000000">
<b><font face="Arial" size="3" color="#FFFFFF">Choose WECC Sub Region</b>
</font>
</td>
<td width="185" ALIGN="CENTER">
<select name="sub_region"><OPTION VALUE="1">COB<OPTION VALUE="2">FOUR CORNERS
<OPTION VALUE="3">MID COLUMBIA<OPTION VALUE="4">MIDWAY<OPTION VALUE="5">NORTH PATH 15
<OPTION VALUE="6">PALO VERDE<OPTION VALUE="7">SOUTH PATH 15
</td>
</tr>
<tr>
<td width="272" height="32" bgcolor="#000000">
<b><font face="Arial" size="3" color="#FFFFFF">Enter Starting Date<BR><font size ="1">(MM/DD/YYYY format):</b>
</font>
</td>
<td width="185" height="32" bgcolor="#FFFFFF" align="center" valign="middle">
<input name="start_date"
size="25" maxlength="50">
</td>
</tr>
<tr>
<td width="272" height="32" bgcolor="#000000">
<b><font face="Arial" size="3" color="#FFFFFF">Enter Ending Date<BR><font size ="1">(MM/DD/YYYY format):</b>
</font>
</td>
<td width="185" height="32" bgcolor="#FFFFFF" align="center" valign="middle">
<input name="end_date"
size="25" maxlength="50">
</td>
</tr>
</table>
<table bgcolor="#333333" align="left" valign="middle" width="471" height="49">
<tr align="CENTER" valign="middle">
<td height="50%">
<input type="submit" value="Submit"><br>
</td>
</tr>
<tr align="CENTER">
<td height="50%">
<font face="Veranda" color="#FFFFFF" size="2">Please Click For Your Answer</font>
</tr>
</form>
</table>
*******************************************
Here is the ASP
*******************************************
<%@ language="vbscript" %>
<% Option Explicit %>
<%
'Dimension Var's, ya bastards!
Dim Date_1 ' Beginning Date
Dim Date_2 ' End Date
Dim SubRegionNum ' Area # to limit to
Dim SubRegionStr ' Sting of Region title
Dim cnnGetRows ' ADO connection
Dim rstGetRows ' ADO recordset
Dim strDBPath ' Path to our Access DB (*.mdb) file
Dim arrDBData ' Array that we dump all the data into
' Temp vars to speed up looping over the array
Dim I, J
Dim iRecFirst, iRecLast
Dim iFieldFirst, iFieldLast
'Get the previous values from the user html
Date_1 = TRIM( Request("start_date"
)Date_2 = TRIM( Request("end_date"
)SubRegionNum = TRIM( Request("sub_region"
)'Set the possible hard coded options
IF SubRegionNum ="1" THEN
SubRegionStr = "COB"
END IF
IF SubRegionNum ="2" THEN
SubRegionStr = "FOUR CORNERS"
END IF
IF SubRegionNum ="3" THEN
SubRegionStr = "MID COLUMBIA"
END IF
IF SubRegionNum ="4" THEN
SubRegionStr = "MIDWAY"
END IF
IF SubRegionNum ="5" THEN
SubRegionStr = "NORTH PATH 15"
END IF
IF SubRegionNum ="6" THEN
SubRegionStr = "PALO VERDE"
END IF
IF SubRegionNum ="7" THEN
SubRegionStr = "SOUTH PATH 15"
END IF
' MapPath to our mdb file's physical path.
strDBPath = Server.MapPath("navigant_database.mdb"
' Create a Connection using OLE DB
Set cnnGetRows = Server.CreateObject("ADODB.Connection"
' Open the Access Base
cnnGetRows.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
' Here is the SQL String Causing all of the probs:
Set rstGetRows = cnnGetRows.Execute ("SELECT tblPrices.DATE, tblPrices.SECTOR, tblPrices.REGION, tblPrices.SUBREGION, tblPrices.T, tblPrices.INDEX_PRICE " &_
"FROM tblPrices " &_
"GROUP BY tblPrices.DATE, tblPrices.SECTOR, tblPrices.REGION, tblPrices.SUBREGION, tblPrices.T, tblPrices.INDEX_PRICE " &_
'Here is the problem line right here:
"WHERE tblPrices.SUBREGION =" '"&SubRegionStr&"' " &_
"AND tblPrices.DATE Between #"&Date_1&"# AND #"&Date_2&"# " &_
"ORDER BY tblPrices.DATE;"
arrDBData = rstGetRows.GetRows()
' Close our recordset and connection and dispose of the objects.
rstGetRows.Close
Set rstGetRows = Nothing
cnnGetRows.Close
Set cnnGetRows = Nothing
iRecFirst = LBound(arrDBData, 2)
iRecLast = UBound(arrDBData, 2)
iFieldFirst = LBound(arrDBData, 1)
iFieldLast = UBound(arrDBData, 1)
' Display a table of the data in the array.
%>
<html>
<head>
<title>.: Search Results for Price Index :.</title>
</head>
<body bgcolor="#ffffff">
<table border="1" bgcolor="#EDEDED">
<%
Response.Write "<b><i>This is the Data for</i> " &SubRegion& "</b>"
' Loop through the records (second dimension of the array)
For I = iRecFirst To iRecLast
' A table row for each record
Response.Write "<tr>" & vbCrLf
' Loop through the fields (first dimension of the array)
For J = iFieldFirst To iFieldLast
' A table cell for each field
Response.Write vbTab & "<td>" & arrDBData(J, I) & "</td>" & vbCrLf
Next ' J
Response.Write "</tr>" & vbCrLf
Next ' I
%>
</table>