ASP query/criteria issue from Access97 HTML wizard.

From Access97, I output a query that had user specified criteria within it (that the user could open the query and view specified record based on their selected criteria entered at a pop-up window prompt. Now, when I choose the "Save as HTML" from the File menu, and output the query as an ASP file, I get 2 files. One is an HTML the retrieves the criteria from the user, and an ASP file to retrieve the recordset based on the criteria.But, when I run these in FrontPage98 I get an error message that the code is incorrect..the examples are below:

HTML file code...

<html>

<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<title>Affected Items Edit</title>
</head>

<body>

<form METHOD="GET" ACTION="Affected%20Items%20Edit.ASP">
  <p>[ECO Number:] <input TYPE="Text" NAME="[ECO Number:]" size="20"></p>
  <p><input TYPE="Submit" VALUE="Run Query"> </p>
</form>
</body>
</html>

ASP File Code:

<html>

<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<title>Affected Items Edit</title>
</head>

<body>
<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
%>
<%
If IsObject(Session("PMNetwork_conn")) Then
    Set conn = Session("PMNetwork_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "PMNetwork","",""
    Set Session("PMNetwork_conn") = conn
End If
%>
<%
    sql = "SELECT [Affected ECO Items].[ECO Number], [Affected ECO Items].[ECO Item Number], [Affected ECO Items].[Customer Item], [Affected ECO Items].[SLR Item], [Affected ECO Items].[Rev From], [Affected ECO Items].[Rev To], [Affected ECO Items].[Item Description], [Affected ECO Items].[Change in Ship Rev], [Affected ECO Items].[Mat Effectivity Code], [Affected ECO Items].[Mat Effectivity Date], [Affected ECO Items].[ECN Applied], [Affected ECO Items].[ECN Date]  FROM [Affected ECO Items]  WHERE ((([Affected ECO Items].[ECO Number])=" & Request.QueryString("[ECO Number:]") & "))  "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
        sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
    End If
    sql = sql & " ORDER BY [Affected ECO Items].[ECO Item Number]    "
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<table BORDER="1" BGCOLOR="#ffffff" CELLSPACING="0">
  <font FACE="Arial" COLOR="#000000"><caption><b>Affected Items Edit</b></caption>
<THEAD>
  <tr>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">ECO
    Number</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">ECO
    Item Number</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Customer
    Item</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">SLR
    Item</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Rev
    From</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Rev
    To</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Item
    Description</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Change
    in Ship Rev</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Mat
    Effectivity Code</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Mat
    Effectivity Date</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">ECN
    Applied</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">ECN
    Date</font></th>
  </tr>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
 %>
  <tr VALIGN="TOP">
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("ECO Number").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("ECO Item Number").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Customer Item").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("SLR Item").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Rev From").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Rev To").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Item Description").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Change in Ship Rev").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Mat Effectivity Code").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Mat Effectivity Date").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("ECN Applied").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0" ALIGN="RIGHT"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("ECN Date").Value)%><br>
    </font></td>
  </tr>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT>
</TFOOT>
</table>
</font>
</body>
</html>

And when I runt the HTML file, and enter the criteria, I get this error message in IE4.0...

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access 97 Driver] Too few parameters. Expected 1.

/mfg-lucent/test/task/Affected Items Edit.ASP, line 27

What do I do to fix this?
sanctifiedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sanctifiedAuthor Commented:
Adjusted points to 100
0
NickRackhamCommented:
Sanctified, this should resolve it,

<html>

<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<title>BOM Changes Query</title>
</head>

<body>
<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
%>
<%
Session.timeout = 15
If IsObject(Session("PMNetwork_conn")) Then
    Set conn = Session("PMNetwork_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "PMNetwork","",""
    Set Session("PMNetwork_conn") = conn
End If
%>
<%
    sql = "SELECT [BOM Changes].[ECO Number], [BOM Changes].[ECO Item], [BOM Changes].[Cust Parent Part], [BOM Changes].[SLR Parent Part], [BOM Changes].[Cust Child Part], [BOM Changes].[SLR Child Part], [BOM Changes].[Item Sequence], [BOM Changes].Action, [BOM Changes].[Qty From], [BOM Changes].[Qty To], [BOM Changes].[Reference Designators]  FROM [BOM Changes]  WHERE ((([BOM Changes].[ECO Number])='" & Request.QueryString("[ECO Number]") & "'))   "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
        sql = sql & " And [" & cstr(Param) & "] = " & cstr(Data)
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<table BORDER="1" BGCOLOR="#ffffff" CELLSPACING="0">
  <font FACE="Arial" COLOR="#000000"><caption><b>BOM Changes Query</b></caption>
<THEAD>
  <tr>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">ECO
    Number</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">ECO
    Item</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Cust
    Parent Part</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">SLR
    Parent Part</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Cust
    Child Part</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">SLR
    Child Part</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Item
    Sequence</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Action</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Qty
    From</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Qty
    To</font></th>
    <th BGCOLOR="#c0c0c0" BORDERCOLOR="#000000"><font SIZE="2" FACE="Arial" COLOR="#000000">Reference
    Designators</font></th>
  </tr>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
 %>
  <tr VALIGN="TOP">
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("ECO Number").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("ECO Item").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Cust Parent Part").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("SLR Parent Part").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Cust Child Part").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("SLR Child Part").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Item Sequence").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Action").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Qty From").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Qty To").Value)%><br>
    </font></td>
    <td BORDERCOLOR="#c0c0c0"><font SIZE="2" FACE="Arial" COLOR="#000000"><%=Server.HTMLEncode(rs.Fields("Reference Designators").Value)%><br>
    </font></td>
  </tr>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT>
</TFOOT>
</table>
</font>
</body>
</html>


Nick

0
NickRackhamCommented:
And this,

<html>

<head>
<meta HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<title>BOM Changes Query</title>
</head>

<body>

<form METHOD="GET" ACTION="BOM%20Changes%20Query_1.ASP">
  <p>[ECO Number] <input TYPE="Text" NAME="[ECO Number]" size="20"></p>
  <p><input TYPE="Submit" VALUE="Run Query"> </p>
</form>
</body>
</html>


No : after the field name.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

sanctifiedAuthor Commented:
is the colon removal the ONLY change?
0
NickRackhamCommented:
I'm not sure as I've used the code you posted to me rather than the code above. I'll check and compare later and post back here.

Nick
       
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sanctifiedAuthor Commented:
I got it...After a close compare in the line:

WHERE ((([Affected ECO Items].[ECO Number])=" & Request.QueryString("[ECO Number:]") & "))  "

MSAccess97 doesn't put the apostrophy before and after the quotes to look like:

WHERE ((([Affected ECO Items].[ECO Number])='" & Request.QueryString("[ECO Number]") & "'))  "

But, no problem..Thanks a bunch!!! This took care of the whole problem!

James
0
NickRackhamCommented:
Glad to help.

Before you start getting too wrapped up in the asp pages access produces it may bebe worth while looking at writing your own asp pages. In particular the SQL side. If you notice, access fills the code with superfluous cr*p. ie tablename columnname, tablename columnname etc etc. where a lot of code could be saved by having select columnname columnname from tablename where.....

Anyway, till the next time. You've got my mail address if you get stuck

Nick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.