Link to home
Start Free TrialLog in
Avatar of sanctified
sanctified

asked on

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?
Avatar of sanctified
sanctified

ASKER

Adjusted points to 100
Avatar of NickRackham
NickRackham
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

Avatar of NickRackham
NickRackham
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.
Avatar of sanctified
sanctified

ASKER

is the colon removal the ONLY change?
ASKER CERTIFIED SOLUTION
Avatar of NickRackham
NickRackham
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sanctified
sanctified

ASKER

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
Avatar of NickRackham
NickRackham
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