p2p.wrox.com: Archives - asp_web

asp_web_howto thread: SEARCHING

Message #1 by "Jonathan Marshall" <jdmarsh2g@h...> on Tue, 27 Nov 2001 18:56:28 +0000

<html><div style='background-color:'><DIV>My main dilemna right now is searching
through a database. In the database I have numerous amounts<BR>of fields. What I want to be able to do is for the user to be
able to search throught the criteria by<BR>whatever they want to look for (ex: partno,phone# etc..)<BR>I have (11) form
elements &amp;&amp; (11) database fields. <BR>&nbsp;The search.htm page with the form elements information from
that page is sent to the <BR>&nbsp;the search.asp page which takes the values and searches against the database and
displays <BR>&nbsp;the information. "I have no clue how to do this" Where do I start. The database already has
<BR>&nbsp;the data in it. I want the user to be able to enter a serial number or what have you and <BR>&nbsp;get
that recordset displayed into the browser.</DIV>

<DIV>Here is the code below with the search.htm &amp; the search.asp</DIV>

<DIV>Search.htm<BR>********************************************************************************************<BR>
;&lt;form action=2F141_inventory_search.asp method=post name=inventory_search&gt;</DIV>

<DIV>&nbsp;</DIV>

<DIV><BR>&lt;table&gt;<BR>&lt;tr&gt;&lt;td&gt;<BR>&lt;b&gt;Nomenclature:&
;lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="60" NAME="NOMEN"&gt;<BR>&lt;/td&gt;&lt;/tr&gt;</DIV>

<DIV>&lt;tr&gt;&lt;td&gt;<BR>&lt;b&gt;Cage Code:&lt;/b&gt;&lt;INPUT TYPE="TEXT"
SIZE="10" NAME="CAGE"&gt;<BR>&lt;b&gt;Part#:&lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="10"
NAME="PARTNO"&gt;<BR>&lt;b&gt;Serial#:&lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="10"
NAME="SERIALNO"&gt;<BR>&lt;/td&gt;&lt;/tr&gt;</DIV>

<DIV>&lt;tr&gt;<BR>&lt;td&gt;<BR>&lt;td&gt;<BR>&lt;/td&gt;<BR>&lt;
/td&gt;<BR>&lt;/tr&gt;</DIV>

<DIV>&nbsp;</DIV>

<DIV>&lt;tr&gt;&lt;td&gt;<BR>&lt;b&gt;Item#:&lt;/b&gt;&lt;INPUT TYPE="TEXT"
SIZE="10" NAME="ITEM"&gt;<BR>&lt;b&gt;Required#:&lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="10"
NAME="REQD"&gt;<BR>&lt;b&gt;Category#:&lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="10"
NAME="CATEGORY"&gt;<BR>&lt;/td&gt;&lt;/tr&gt;</DIV>

<DIV>&lt;tr&gt;<BR>&lt;td&gt;<BR>&lt;td&gt;<BR>&lt;/td&gt;<BR>&lt;
/td&gt;<BR>&lt;/tr&gt;</DIV>

<DIV><BR>&lt;tr&gt;&lt;td&gt;<BR>&lt;b&gt;Location:&lt;/b&gt;&lt;INPUT
TYPE="TEXT" SIZE="10" NAME="LOCATION"&gt;<BR>&lt;b&gt;Remarks:&lt;/b&gt;&lt;INPUT TYPE="TEXT"
SIZE="10" NAME="REMARKS"&gt;<BR>&lt;b&gt;Change:&lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="10"
NAME="CHANGE"&gt;<BR>&lt;b&gt;Contractor:&lt;/b&gt;&lt;INPUT TYPE="TEXT" SIZE="35"
NAME="CONTRACTOR"&gt;<BR>&lt;/td&gt;&lt;/tr&gt;</DIV>

<DIV>&lt;/tr&gt;<BR>&lt;/table&gt;</DIV>

<DIV><BR>&lt;center&gt;&lt;INPUT TYPE="submit" VALUE="Execute
Search"&gt;&lt;/center&gt;</DIV>

<DIV>&lt;/form&gt;<BR>******************************************************************************************
**</DIV>

<DIV><BR>Search.asp<BR>*******************************************************************************************
*<BR>&lt;%<BR>'Declare variables needed<BR>dim oRSapp,sql</DIV>

<DIV>'Set recordset<BR>set oRSapp=Server.CreateObject("ADODB.Recordset")<BR>sql = ("SELECT * inventory WHERE 0 =
0")</DIV>

<DIV><BR>if Request.Form("nomen") IS NOT&nbsp; "" AND inventory.nomen LIKE Request.Form("nomen") end if<BR>if
Request.Form("item") IS NOT "" AND inventory.item LIKE Request.Form("item") end if<BR>if Request.Form("reqd") IS NOT "" AND
inventory.reqd LIKE Request.Form("reqd") end if<BR>if Request.Form("category") IS NOT "" AND inventory.category LIKE
Request.Form("category") end if<BR>if Request.Form("location") IS NOT "" AND inventory.location LIKE Request.Form("location")
end if<BR>if Request.Form("remarks") IS NOT "" AND inventory.remarks LIKE Request.Form("remarks") end if<BR>if
Request.Form("change") IS NOT "" AND inventory.change LIKE Request.Form("change") end if<BR>if Request.Form("contractor") IS
NOT "" AND inventory.contractor LIKE Request.Form("contractor") end if<BR>if Request.Form("cage") IS NOT "" AND inventory.cage
LIKE Request.Form("cage") end if<BR>if Request.Form("partno") IS NOT "" AND inventory.partno LIKE Request.Form("partno") end
if<BR>if Request.Form("serialno") IS NOT "" AND inventory.serialno LIKE Request.Form("serialno") end if</DIV>

<DIV>&nbsp;</DIV>

<DIV>oRSapp.open sql, "DSN=inventory"</DIV>

<DIV>&nbsp;</DIV>

<DIV>&nbsp;</DIV>

<DIV>'Close and dereference database objects<BR>Set oRSapp = Nothing<BR>oRSapp.Close</DIV>

<DIV><BR>%&gt;<BR>********************************************************************************************
<BR></DIV></div><br clear=all><hr>Get your FREE download of MSN Explorer at <a
href='http://go.msn.com/bql/hmtag_itl_EN.asp'>http://explorer.msn.com</a><br></html>

Message #2 by "Jason Salas" <jason@k...> on Wed, 28 Nov 2001 09:36:39 +1000

This is a multi-part message in MIME format.



------=_NextPart_000_007A_01C177F0.2E81CF00

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Hi Jonathan,



You might also have to instantiate the a connection object in SEARCH.ASP 

(herein referred to as your "results page"), before you instantiate the 

Recordset object:

<%

Dim objConn, varSearch

Set objConn =3D Server.CreateObject("ADODB.Connection")

varSearch =3D Request.Form("SERIALNO")

%>



The only thing then would be to build the results page in the format you 

would want and then indicate how many records per page you would want to 

display.  This is known as "paging" through a recordset.  For example, 

if your search returned 1,500,000 found records, you might crash you app 

loading all of those into the same page.  It would least take a long 

time.



You should modify your SQL string to



SQL =3D "SELECT * FROM inventory WHERE serialno=3D'" & varSearch & "'"



In this case, "varSearch" would be the string a user entered into the 

text field, as defined in the scriptblock above.  You would then have to 

build the presentation format of your search results page to display the 

data:



<%

Response.Write("<b>" & oRSapp("nomen") & "<br>" & oRSapp("item") & 

"<br>" & oRSapp("reqd") & "<br>" & oRSapp("category") & "<br>" & 

oRSapp("location") & "<br>" & oRSapp("remarks") & "<br>" & 

oRSapp("change") & "<br>" & oRSapp("contractor") & "<br>" oRSapp("cage") 

& "<br>" & oRSapp("partno") & "<br>" oRSapp("serialno") & "</b>")

%>



The only condition about setting the search service up the way you've 

done it would be that you would have to use a SELECT CASE statement to 

make sure that when a particular form field is filled out and then the 

saubmit button is clicked, a certain script would fire, searching your 

DB for that particular field.  In essence, you'd have to re-write the 

above SQL statement 10 more times, 11 times total .  Not too hard.



HTH,

Jason



  ----- Original Message -----

  From: Jonathan Marshall

  To: ASP Web HowTo

  Sent: Wednesday, November 28, 2001 4:56 AM

  Subject: [asp_web_howto] SEARCHING





  My main dilemna right now is searching through a database. In the 

database I have numerous amounts

  of fields. What I want to be able to do is for the user to be able to 

search throught the criteria by

  whatever they want to look for (ex: partno,phone# etc..)

  I have (11) form elements && (11) database fields.

   The search.htm page with the form elements information from that page 

is sent to the

   the search.asp page which takes the values and searches against the 

database and displays

   the information. "I have no clue how to do this" Where do I start. 

The database already has

   the data in it. I want the user to be able to enter a serial number 

or what have you and

   get that recordset displayed into the browser.

  Here is the code below with the search.htm & the search.asp

  Search.htm

  

*************************************************************************

*******************

  <form action=3D2F141_inventory_search.asp method=3Dpost 

name=3Dinventory_search>





  <table>

  <tr><td>

  <b>Nomenclature:</b><INPUT TYPE=3D"TEXT" SIZE=3D"60" NAME=3D"NOMEN">

  </td></tr>

  <tr><td>

  <b>Cage Code:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"CAGE">

  <b>Part#:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"PARTNO">

  <b>Serial#:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"SERIALNO">

  </td></tr>

  <tr>

  <td>

  <td>

  </td>

  </td>

  </tr>



  <tr><td>

  <b>Item#:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"ITEM">

  <b>Required#:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"REQD">

  <b>Category#:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"CATEGORY">

  </td></tr>

  <tr>

  <td>

  <td>

  </td>

  </td>

  </tr>



  <tr><td>

  <b>Location:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"LOCATION">

  <b>Remarks:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"REMARKS">

  <b>Change:</b><INPUT TYPE=3D"TEXT" SIZE=3D"10" NAME=3D"CHANGE">

  <b>Contractor:</b><INPUT TYPE=3D"TEXT" SIZE=3D"35" 

NAME=3D"CONTRACTOR">

  </td></tr>

  </tr>

  </table>



  <center><INPUT TYPE=3D"submit" VALUE=3D"Execute Search"></center>

  </form>

  

*************************************************************************

*******************



  Search.asp

  

*************************************************************************

*******************

  <%

  'Declare variables needed

  dim oRSapp,sql

  'Set recordset

  set oRSapp=3DServer.CreateObject("ADODB.Recordset")

  sql =3D ("SELECT * inventory WHERE 0 =3D 0")



  if Request.Form("nomen") IS NOT  "" AND inventory.nomen LIKE 

Request.Form("nomen") end if

  if Request.Form("item") IS NOT "" AND inventory.item LIKE 

Request.Form("item") end if

  if Request.Form("reqd") IS NOT "" AND inventory.reqd LIKE 

Request.Form("reqd") end if

  if Request.Form("category") IS NOT "" AND inventory.category LIKE 

Request.Form("category") end if

  if Request.Form("location") IS NOT "" AND inventory.location LIKE 

Request.Form("location") end if

  if Request.Form("remarks") IS NOT "" AND inventory.remarks LIKE 

Request.Form("remarks") end if

  if Request.Form("change") IS NOT "" AND inventory.change LIKE 

Request.Form("change") end if

  if Request.Form("contractor") IS NOT "" AND inventory.contractor LIKE 

Request.Form("contractor") end if

  if Request.Form("cage") IS NOT "" AND inventory.cage LIKE 

Request.Form("cage") end if

  if Request.Form("partno") IS NOT "" AND inventory.partno LIKE 

Request.Form("partno") end if

  if Request.Form("serialno") IS NOT "" AND inventory.serialno LIKE 

Request.Form("serialno") end if



  oRSapp.open sql, "DSN=3Dinventory"





  'Close and dereference database objects

  Set oRSapp =3D Nothing

  oRSapp.Close



  %>

  

*************************************************************************

*******************







-------------------------------------------------------------------------

-----

  Get your FREE download of MSN Explorer at http://explorer.msn.com




$subst('Email.Unsub')

  Read the future with ebooks at B&N

  

http://service.bfast.com/bfast/click?bfmid=3D2181&sourceid=3D38934667&cat

egoryid=3Drn_ebooks

Return to Index

P2P Archives