• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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?
0
sanctified
Asked:
sanctified
  • 4
  • 3
1 Solution
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now