By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,008 Members | 2,895 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How To Dynamically Search A Database Table

Merlin1857
P: 14
How to search multiple fields using ASP

A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and having the sql statement dynamically built according to the input provided by the user. I have used the method described here hundreds of times it is quick and adaptive. I generally use a frames page for the search, in this way the search is maintained in the upper pane whilst results show in the lower. The search can then be adjusted as required without the need to keep going back and inputting the search from scratch. This works for Access, SQL server, or of course any database. The following code would search an address for instance :[/font]

There are four pages to this solution, they should all be placed in the same directory for the refernces to work properly :

========================================
Page 1 - The main frame page
========================================

Expand|Select|Wrap|Line Numbers
  1.  <html> 
  2.  
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  5. <title>Address Search Main</title>
  6. </head>
  7.  
  8. <frameset rows="112,*" framespacing="0" border="0" frameborder="0">
  9. <frame name="addsrc_header" scrolling="no" noresize target="addsrc_main" src="02srctop.asp">
  10. <frame name="addsrc_main" src="03srcbot.asp" scrolling="auto">
  11. <noframes>
  12. <body>
  13.  
  14. <p>This page uses frames, but your browser doesn't support them.</p>
  15.  
  16. </body>
  17. </noframes>
  18. </frameset>
  19.  
  20. </html>
  21.  
  22.  
  23.  
========================================
Page 2 - The search form page
========================================
Expand|Select|Wrap|Line Numbers
  1.  <html> 
  2.  
  3. <head>
  4. <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  5. <title>Address Search Top</title>
  6. <base target="add_main">
  7.  
  8. </head>
  9.  
  10. <body>
  11.  
  12. <table border="0" width="560" cellspacing="0" cellpadding="0">
  13. <form method="POST" action="04srcres.asp" target="addsrc_main">
  14. <tr>
  15. <td colspan="6" style="border-left-width: 1px; border-right-width: 1px; border-top-width: 1px; border-bottom: 1px solid #000000">
  16. ADDRESS SEARCH -<b><font color="#000080">
  17. </font>
  18. </b>
  19. <font color="#000080">
  20. Use the fields to search for recorded addresses.</font></td>
  21. </tr>
  22. <tr>
  23. <td width="95">
  24. <input type="text" name="InpHouseName" size='13' tabindex="1"></td>
  25. <td width="133">
  26. <font color="#000080">House Name</font></td>
  27. <td width="94">
  28.      <INPUT type="text" name=InpAddArea size='13' maxlength="10" tabindex="4"></td>
  29. <td width="72">
  30. <font color="#000080">Area</font></td>
  31. <td width="93">
  32. <INPUT type="text" name=InpAddCounty size='13' maxlength="10" tabindex="7"></td>
  33. <td width="113">
  34. <font color="#000080">County</font></td>
  35. </tr>
  36. <tr>
  37. <td width="95">
  38.  
  39. <input type="text" name="InpAddNo" size='13' tabindex="2"></td>
  40. <td width="133">
  41. <font color="#000080">Number</font></td>
  42. <td width="94">
  43. <INPUT type="text" name=InpAddTown size='13' tabindex="5"></td>
  44. <td width="72">
  45. <font color="#000080">Town</font><font face="Arial" color="#000080"> </font>
  46. </td>
  47. <td width="93">
  48. <INPUT type="text" name=InpAddPostCode size='13' tabindex="8"></td>
  49. <td width="113">
  50. <font color="#000080">Post Code</font></td>
  51. </tr>
  52. <tr>
  53. <td width="95">
  54.  
  55. <INPUT type="text" name=InpAddStreet size='13' tabindex="3"></td>
  56. <td width="133">
  57. <font color="#000080">Street</font></td>
  58. <td width="94">
  59. <INPUT type="text" name=InpAddCity size='13' tabindex="6"></td>
  60. <td width="72">
  61. <font color="#000080">City</font></td>
  62. <td colspan="2">
  63.  
  64. <input type="submit" value="Search" name="B1">
  65. <input type="reset" value="Reset" name="B2">
  66. </td>
  67. </tr>
  68. </form>
  69. </table>
  70.  
  71. </body>
  72.  
  73. </html>
  74.  
  75.  
========================================
Page 3 - A holding page to inform the user what to do.
========================================
Expand|Select|Wrap|Line Numbers
  1.  <html> 
  2.  
  3. <head>
  4. <meta http-equiv="Content-Language" content="en-gb">
  5. <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  6. <title>Address Search Bottom</title>
  7. </head>
  8.  
  9. <body>
  10.  
  11. <p>The Search Results Will Show Here When The Search Button Is Clicked. </p>
  12.  
  13. </body>
  14.  
  15. </html>
  16.  
  17.  
========================================
Page 4 - The results page.
========================================
Expand|Select|Wrap|Line Numbers
  1.  
  2. <!-- #include file="../00incfiles/adovbs.inc" -->
  3. <%
  4. 'Connection to the database (SQL Server in this case)
  5. '=============CONNECTION CODE=====================
  6. Set Conn = Server.CreateObject("ADODB.Connection") 
  7. strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASE;"&_
  8. "User Id=USERNAME;Password=PASSWORD;"
  9. Conn.Open strConn 
  10. '=============CONNECTION CODE=====================
  11.  
  12.  
  13. 'Obtain input from the referring form (index.top.asp) and feed to variables Var...
  14.  
  15. 'Note the use of the replace function below to prevent query error (removes any apostrophes input by the user)
  16. VarHouseName = Replace(Request("InpHouseName"),"'","") 
  17. VarAddressNo = Replace(Request("InpAddressNo"),"'","")
  18. VarAddressSt = Replace(Request("InpAddressStreet"),"'","")
  19. VarAddressArea = Replace(Request("InpAddressArea"),"'","")
  20. VarAddressTown = Replace(Request("InpAddressTown"),"'","")
  21. VarAddressCity = Replace(Request("InpAddressCity"),"'","")
  22. VarAddressCounty = Replace(Request("InpAddressCounty"),"'","")
  23. VarAddressPostCode = Replace(Request("InpAddressPostCode"),"'","")
  24.  
  25. 'Note the use of '=' and 'like' clauses in the statement below. Using a 'like' clause like that shown
  26. 'precludes the need for the user to enter his or her own wild card parantheses when searching
  27.  
  28. 'Dynamically formulate the SQL statement and feed into a varaible called VarSQL as its built
  29.  
  30. If Len(VarHouseNumber) > 0 then
  31. VarSQL = VarSQL & "HouseName like '%"&VarHouseNumber&"%' " 'Like clause used
  32. end if
  33.  
  34. If Len(VarAddressNo) > 0 then
  35. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  36. VarSQL = VarSQL & "AddressNo = '"&VarAddressNo&"' " '= clause used
  37. end if
  38.  
  39. If Len(VarAddressSt) > 0 then
  40. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  41. VarSQL = VarSQL & "AddressStreet like '%"&VarAddressSt&"%' "
  42. end if
  43.  
  44. If Len(VarAddressArea) > 0 then
  45. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  46. VarSQL = VarSQL & "AddressArea like '%"&VarAddressArea&"%' "
  47. end if
  48.  
  49. If Len(VarAddressTown) > 0 then
  50. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  51. VarSQL = VarSQL & "AddressTown like '%"&VarAddressTown&"%' "
  52. end if
  53.  
  54. If Len(VarAddressCity) > 0 then
  55. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  56. VarSQL = VarSQL & "AddressCity like '%"&VarAddressCity&"%' "
  57. end if
  58.  
  59. If Len(VarAddressCounty) > 0 then
  60. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  61. VarSQL = VarSQL & "AddressCounty like '%"&VarAddressCounty&"%' "
  62. end if
  63.  
  64. If Len(VarAddressPostCode) > 0 then
  65. If Len(VarSQL) > 1 then VarSQL = VarSQL & "and "
  66. VarSQL = VarSQL & "AddressPostCode like '%"&VarAddressPostCode&"%' "
  67. end if
  68.  
  69. 'Create a recordset object
  70. Set RS1 = Server.CreateObject("ADODB.RecordSet")
  71.  
  72. 'Check main address table
  73. If Len (VarSQL)<1 Then
  74. sql1 = "SELECT * FROM tblAddress" 'If the user pushes the button without any input all records are displayed
  75. Else
  76. sql1 = "Select * from tblAddress where "&VarSQL&"" 'This statement is executed when there is input from the user
  77. End If
  78. 'response.write sql1
  79.  
  80. 'Fire the query at the database using the recordset object and sql statement
  81. RS1.Open sql1, Conn, adOpenKeyset,adLockOptimistic
  82.  
  83. 'Obtain the number of records your search will reveal and feed to a variable.
  84. VarResCount = RS1.recordcount 
  85. %>
  86. <html>
  87.  
  88. <head>
  89. <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
  90. <title>Address Search Results</title>
  91. </head>
  92.  
  93. <body> 
  94.  
  95. <%If RS1.eof Then%>
  96. <table border="0" width="100%" id="table1" cellspacing="0" cellpadding="0">
  97. <tr>
  98. <td>SEARCH RESULTS</td>
  99. </tr>
  100. <tr>
  101. <td><font color="#FF0000">No addresses were found matching your search, please modify your search parameters.</font></td>
  102. </tr>
  103. </table>
  104. <%Else%>
  105. <table border="0" width="100%" id="table2" cellspacing="0" cellpadding="0">
  106. <tr>
  107. <td>SEARCH RESULTS</td>
  108. </tr>
  109. <tr>
  110. <td>The following <font color="#FF0000"><%=VarResCount%></font> addresses were found matching your search.</td>
  111. </tr>
  112. </table>
  113. <table border="1" width="100%" id="table3" cellspacing="0" cellpadding="2">
  114. <tr>
  115. <td width="16" bgcolor="#F0F0F0">ID</td>
  116. <td bgcolor="#F0F0F0">Address</td>
  117. </tr>
  118. <%
  119. Do While Not RS1.eof
  120. 'Feed results into a single variable to display below, this is optional obviously
  121. VarAddress = RS1("HouseName")&" "&RS1("AddNo")&" "&RS1("AddStreet")&" "&RS1("AddArea")&" "&RS1("AddTown")&" "&RS1("AddCity")&" "&RS1("AddCounty")&" "&RS1("AddPostCode")
  122. %>
  123. <tr>
  124. <td width="16" valign="top"><%=RS1("AddID")%>&nbsp;</td>
  125. <td valign="top"><%=VarAddress%>&nbsp;</td>
  126. </tr>
  127. <%
  128. RS1.movenext
  129. Loop
  130. End If
  131. %>
  132. </table>
  133.  
  134. </body>
  135.  
  136. </html>
  137. <%
  138. 'Clean up the connections and recordsets
  139. RS1.Close
  140. Conn.Close
  141.  
  142. Set Conn = Nothing
  143. Set RS1 = Nothing
  144. sql = sql1
  145. %>
  146.  
  147.  
Thats it really. I have adapted this from code used by me in various web sites. You may need to change references to suit yourself.
Sep 10 '07 #1
Share this Article
Share on Google+
1 Comment


P: 2
Thanks. This is very good article. I am using a BETWEEN function on numeric field in access it is working but in sql it is giving error
Oct 13 '07 #2