Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Jul 2005
    Location
    Oxfordshire, UK
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query problem using form inputs

    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?

    Code:
    <%
    
    %>
    <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>
    Last edited by robojob; 08-16-2005 at 12:26 PM.

  • #2
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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 & "';"
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •