...

View Full Version : query problem using form inputs



robojob
08-16-2005, 11:18 AM
SORTED IT MYSELF!!


i have a simple form with one field called item. i want to be able to enter something in there, click submit and it displays all records in the database with the item that i entered for example, if i enter printer it displays all printers.

here is the code for the page that displays the results.

i can response.write the form value anywhere on the page so i know that is coming through but it wont filter the databse, it just comes up with no items... i think i have done something wrong in the query but cant work it out!!

any suggestions?


<%

%>
<html>
<head>
<title>Gosford Hill School Inventory</title>
</head>
<body bgcolor="white" text="Black" >



<br>
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsinvent 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
Dim strItem 'Holds the item from the form

strItem = Request.Form("item")


'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ghs.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=inventory"

'Create an ADO recordset object
Set rsinvent = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM inventory where item like 'strItem';"

'Open the recordset with the SQL query
rsinvent.Open strSQL, adoCon %>






<table width="100%" border="1" bordercolor="white">
<tr bordercolor="blue">
<td width="10%" align="center"><font face="verdana" size="2">Department</font></td>
<td width="10%" align="center"><font face="verdana" size="2">Room</font></td>
<td width="10%" align="center"><font face="verdana" size="2">Item</font></td>
<td width="10%" align="center"><font face="verdana" size="2">Make</font></td>
<td width="10%" align="center"><font face="verdana" size="2">Serial Number</font></td>
<td width="5%" align="center"><font face="verdana" size="2">Purchased</font></td>
<td width="5%" align="center"><font face="verdana" size="2">Disposed</font></td>

</tr>


<% Do While not rsinvent.EOF %>
<tr bordercolor="blue">
<td width="10%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Department"))%></font></td>
<td width="10%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Room"))%></font></td>
<td width="10%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Item"))%></font></td>
<td width="10%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Make"))%></font></td>
<td width="10%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Serialnumber"))%></font></td>
<td width="5%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Datepurchased"))%></font></td>
<td width="5%" align="center"><font face="verdana" size="2"><% Response.Write (rsinvent("Datedisposed"))%></font></td>

</tr>


<%



'Move to the next record in the recordset
rsinvent.MoveNext

Loop

'Reset server objects
rsinvent.Close
Set rsinvent = Nothing
Set adoCon = Nothing
%>

</table>
</body>
</html>

Bullschmidt
08-17-2005, 10:31 PM
Perhaps change this:

strSQL = "SELECT * FROM inventory where item like 'strItem';"

To be more like this instead:

strSQL = "SELECT * FROM inventory where item like '" & strItem & "';"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum