When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Thursday, May 27, 1999

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

- continued -

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


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article