To read the article online, visit http://www.4GuysFromRolla.com/webtech/052799-1.shtml

A Nifty Table Searching Script

By Jerry Wood


This article, by Jerry Wood, shows a nifty little script, which, with just one ASP page, allows you to dynamically search a column in a table using a string comparison. This one script can be used over and over due to its dynamic nature! Hopefully this script will save you valuable time by cutting down on the often repetitive task of creating small, searching scripts!

So, you may be wondering how to use this script! Well, trust me, it isn't difficult. Let's say that you have a database that has a table named Customer, and you want to search the column in the Customer table named CustomerName. This is a common task many ASP coders have to perform. You can use a form like this to allow a user to dictate what they want to search on:

<FORM METHOD=POST ACTION="SearchField.asp">
	<!-- We need to specify whether we want to search
		 on all words in the search string, or just any
		 word in the search string.  For this example,
		 let's search for all words!  This way, if a user
		 searches on "JOE SMITH", "JOE RIVERS" won't be
		 returned...-->
		 
	<INPUT TYPE=HIDDEN NAME="SType" VALUE="AllWords">
	<INPUT TYPE=HIDDEN NAME="Column" VALUE="CustomerName">
	<INPUT TYPE=HIDDEN NAME="Table" VALUE="Customer">
	
	Enter your Search String:
	<INPUT TYPE=TEXT NAME="SValue" SIZE=30>
	<P>
	<INPUT TYPE=SUBMIT>
</FORM>

Now, onto the code!! :)

SearchField.asp

<HTML>
<BODY>
<%
' Pass in the table to search, and the field to search,
' and, optionally, the column to sort by
strFieldName = Request("Column")
strTableName = Request("Table")
strOrderByField = Request("OrderBy")
' SValue = Seach String From Form / QueryString
' SType = Search Type (All Words or Any Word) From Form / QueryString
SValue = Request("SValue")
SType = Request("SType")

' For Loop To Move Through Search String, Counting Spaces, and
' Incrementing WordCounter By One At Each Occuranceh
For i = 1 to len(SValue)
   If mid(SValue, i, 1) = " " Then 
     WordCounter = WordCounter + 1
   End If
Next

' Now, We Add One To Include The First Word
WordCounter = WordCounter + 1

' Next We Dim Word As An Array, With The Maximum Number Of Words
' To Allow - In This Case, 100
Dim Word(100)

' Now, We Fill The Array With The Words
CurrentWord = 1
For i = 1 to len(SValue)
     If mid(SValue, i, 1) = " " Then
	CurrentWord = CurrentWord + 1
     Else
	Word(CurrentWord) = Word(CurrentWord) + mid(SValue, i, 1)
     End If
Next

' Now Lets Build The SQL Statement Based On What Search Type (SType)
' Was Selected

' First Part Of SQL
SQL = "SELECT * FROM " & strTableName & " WHERE "

' For Loop To Concatenate SQL String Together
	For i = 1 to WordCounter
		If SType = "AllWords" Then
			If i <> WordCounter Then
				SQL1 = SQL1 & strFieldName & " LIKE '%" & Word(i) & "%' AND "
			ElseIf i = WordCounter Then
				SQL1 = SQL1 & strFieldName & " LIKE '%" & Word(i) & "%'"
			End If
		ElseIf SType = "AnyWord" Then
			If i <> WordCounter Then
				SQL1 = SQL1 & strFieldName & " LIKE '%" & Word(i) & "%' OR "
			ElseIf i = WordCounter Then
				SQL1 = SQL1 & strFieldName & " LIKE '%" & Word(i) & "%'"
			End If
		End If
	Next
	
' Finishing Part Of SQL Statement.
if Len(strOrderByField) > 0 then
	'We need to perform an ORDER BY!
	SQL = SQL & SQL1 & " ORDER BY " & strOrderByField
Else
	SQL = SQL & SQL1
End If
%>

<%
'Connect to our database.  In this example I used the Northwind DSN
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open "DSN=NorthWind"

'Create an explicit recordset object...
Set rsRecords = Server.CreateObject("ADODB.Recordset")
rsRecords.Open SQL, dbConn, 3
%>

<%
If Not rsRecords.EOF And Not rsRecords.BOF Then
	'We have at least one record, so display it...
%>
	<TABLE WIDTH="100%" CELLPADDING="0" CELLSPACING="1" BORDER=1>
	<TR>
	<% For i = 0 to rsRecords.Fields.Count-1 %>
		<TH><FONT FACE="Arial"><%=rsRecords.Fields(i).Name%> 
			</FONT></TH>
	<% Next %>
	</TR>
	<TR></TR><TR></TR><TR></TR>
	<%
	Do While Not rsRecords.EOF
	%>
		<TR>
		<% For i = 0 to rsRecords.Fields.Count-1 %>
			<TD ALIGN=CENTER><%Response.Write(rsRecords(i))%>
		<% Next %>
		</TR>
		<%rsRecords.MoveNext%>
	<%
	Loop
	%>
	</TABLE>
<%
Else
	'The Search was futile, no records returned.
	Response.Write("<FONT SIZE=5><B>Unable to find an entry</B>")
	Response.Write("</FONT></CENTER>")
End If
%>

</BODY>
</HTML>

Happy Programming!


Attachments:

  • ASP Code in Text Format


  • Article Information
    Article Title: A Nifty Table Searching Script
    Article Author: Jerry Wood
    Published Date: Thursday, May 27, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/052799-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers