|
|
 |
|
|
Published: Thursday, May 27, 1999 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:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Whitepapers and eBooks
|
