htillberg
asked on
Building a Keyword Search in ASP - Having trouble constructing array
Hello:
I have been struggling this week with piecing together code to put together a keyword search of a database using ASP.
I downloaded a version of a search engine class that someone had posted on the internet and successfully adapted it to pull from my database. However, in trying to pull it into my display page, I could not get it to cooperate and pull from my database. I'm not sure how much of the code you need to see to help, but I have a feeling it is because some of the information in my original search page (which is based on a list/menu) is redundant with the class file. Theoretically I know what I want to do and I feel like it should be easy to have the computer read the input, parse it, match it against the fields in the database and return a result as an array...but it hasn't worked out that way!
I guess I have to attach all the code...and I apologize in advance because it is lengthy...
In additiion to some tips as to how I might fix this to properly create a keyword search, I guess I would like to know if it is better in general to use the class file or to just build necessary functions into the search page. The other searches that are built in the site do not use classes. Thanks for your help!!!
first...the search file
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="./cls_dbsearchengine
<%
'variable declaration
Dim rsSearchAll, ct, results, noCriteria
'create instance of the class
Set rsSearchAll = New DBSearchEngine
With rsSearchAll
'db connection string
.DBConnectionString = "Provider=Microsoft.Jet.OL
'search type
.SearchType = Request("type")
'determine what to search
if request("OrganizationName"
if request("HistoricalSignifi
'gather results into a string
results = .Search( request("keywords") )
'get the count of records returned
ct = .Count
'find out whether or not the minimum search criteria
'was entered...
noCriteria = .bNoFieldsToSearch
End With
'free class instance (failure to explicitly free objects in
'ASP is generally considered a bad idea)
Set rsSearchAll = Nothing
'write the search results to the browser...
With Response
if noCriteria then .Write("<p>NO FIELDS SELECTED, SEARCH NOT REFINED!</p>")
.Write(ct & " records found.<BR>")
.Write(results)
End With
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = 10
Dim Repeat1__index
Repeat1__index = 0
rsSearchAll_numRows = rsSearchAll_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
' set the record count
Dim rsSearchAll_total
rsSearchAll_total = rsSearchAll.RecordCount
' set the number of rows displayed on this page
If (rsSearchAll_numRows < 0) Then
rsSearchAll_numRows = rsSearchAll_total
Elseif (rsSearchAll_numRows = 0) Then
rsSearchAll_numRows = 1
End If
' set the first and last displayed record
rsSearchAll_first = 1
rsSearchAll_last = rsSearchAll_first + rsSearchAll_numRows - 1
' if we have the correct record count, check the other stats
If (rsSearchAll_total <> -1) Then
If (rsSearchAll_first > rsSearchAll_total) Then rsSearchAll_first = rsSearchAll_total
If (rsSearchAll_last > rsSearchAll_total) Then rsSearchAll_last = rsSearchAll_total
If (rsSearchAll_numRows > rsSearchAll_total) Then rsSearchAll_numRows = rsSearchAll_total
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them
If (rsSearchAll_total = -1 AND (NOT rsSearchAll.EOF)) Then
' count the total records by iterating through the recordset
rsSearchAll_total=0
While (Not rsSearchAll.EOF)
rsSearchAll_total = rsSearchAll_total + 1
rsSearchAll.MoveNext
Wend
' reset the cursor to the beginning
If (rsSearchAll.CursorType > 0) Then
rsSearchAll.MoveFirst
Else
rsSearchAll.Requery
End If
' set the number of rows displayed on this page
If (rsSearchAll_numRows < 0 Or rsSearchAll_numRows > rsSearchAll_total) Then
rsSearchAll_numRows = rsSearchAll_total
End If
' set the first and last displayed record
rsSearchAll_first = 1
rsSearchAll_last = rsSearchAll_first + rsSearchAll_numRows - 1
If (rsSearchAll_first > rsSearchAll_total) Then rsSearchAll_first = rsSearchAll_total
If (rsSearchAll_last > rsSearchAll_total) Then rsSearchAll_last = rsSearchAll_total
End If
%>
<%
' *** Move To Record and Go To Record: declare variables
Set MM_rs = rsSearchAll
MM_rsCount = rsSearchAll_total
MM_size = rsSearchAll_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
MM_paramIsDefined = (Request.QueryString(MM_pa
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter
if (Not MM_paramIsDefined And MM_rsCount <> 0) then
' use index parameter if defined, otherwise use offset parameter
r = Request.QueryString("index
If r = "" Then r = Request.QueryString("offse
If r <> "" Then MM_offset = Int(r)
' if we have a record count, check if we are past the end of the recordset
If (MM_rsCount <> -1) Then
If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or move last
If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a full repeat region
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If
' move the cursor to the selected record
i = 0
While ((Not MM_rs.EOF) And (i < MM_offset Or MM_offset = -1))
MM_rs.MoveNext
i = i + 1
Wend
If (MM_rs.EOF) Then MM_offset = i ' set MM_offset to the last possible record
End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range
If (MM_rsCount = -1) Then
' walk to the end of the display range for this page
i = MM_offset
While (Not MM_rs.EOF And (MM_size < 0 Or i < MM_offset + MM_size))
MM_rs.MoveNext
i = i + 1
Wend
' if we walked off the end of the recordset, set MM_rsCount and MM_size
If (MM_rs.EOF) Then
MM_rsCount = i
If (MM_size < 0 Or MM_size > MM_rsCount) Then MM_size = MM_rsCount
End If
' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) > 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If
' reset the cursor to the beginning
If (MM_rs.CursorType > 0 AND (NOT rsSearchAll.EOF)) Then
MM_rs.MoveFirst
Else
MM_rs.Requery
End If
' move the cursor to the selected record
i = 0
While (Not MM_rs.EOF And i < MM_offset)
MM_rs.MoveNext
i = i + 1
Wend
End If
%>
<%
' *** Move To Record: update recordset stats
' set the first and last displayed record
rsSearchAll_first = MM_offset + 1
rsSearchAll_last = MM_offset + MM_size
If (MM_rsCount <> -1) Then
If (rsSearchAll_first > MM_rsCount) Then rsSearchAll_first = MM_rsCount
If (rsSearchAll_last > MM_rsCount) Then rsSearchAll_last = MM_rsCount
End If
' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="
MM_keepURL="":MM_keepForm=
' add the URL parameters to the MM_keepURL string
For Each Item In Request.Search
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,Nex
MM_keepURL = MM_keepURL & NextItem & Server.URLencode(Request.S
End If
Next
' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,Nex
MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.F
End If
Next
' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> "") Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links
MM_keepMove = MM_keepBoth
MM_moveParam = "index"
' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 0) Then
MM_moveParam = "offset"
If (MM_keepMove <> "") Then
params = Split(MM_keepMove, "&")
MM_keepMove = ""
For i = 0 To UBound(params)
nextItem = Left(params(i), InStr(params(i),"=") - 1)
If (StrComp(nextItem,MM_moveP
MM_keepMove = MM_keepMove & "&" & params(i)
End If
Next
If (MM_keepMove <> "") Then
MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
End If
End If
End If
' set the strings for the move to links
If (MM_keepMove <> "") Then MM_keepMove = MM_keepMove & "&"
urlStr = Request.ServerVariables("U
MM_moveFirst = urlStr & "0"
MM_moveLast = urlStr & "-1"
MM_moveNext = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev < 0) Then prev = 0
MM_movePrev = urlStr & Cstr(prev)
%>
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset
<title>African American Heritage in Virginia</title>
<link rel="stylesheet" href="aahv.css">
<script language="JavaScript" src="nav.js"></script>
</head>
<body bgcolor="#cc9933" text="#363636" link="#336666" alink="#996600" vlink="#663300" leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
<table border="0" cellpadding="1" cellspacing="0" width="95%" bgcolor="#996600">
<td height="90">
<tr>
<td> <table border="0" cellpadding="0" cellspacing="0" width="100%" bgcolor="#cc9933">
<tr height="50">
<td width="116" rowspan="3" background="images/chrome_
<table border="0" cellpadding="0" cellspacing="0" width="115" height="154">
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/index.html" onMouseOver="imgOn('homeLI
</tr>
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/about.html" onMouseOver="imgOn('aboutL
</tr>
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/data.html" onMouseOver="imgOn('dataLI
</tr>
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/grants.html" onMouseOver="imgOn('grants
</tr>
<tr height="26">
<td height="26" width="115"><a href="http://www.virginia.edu/vfh/aahv/exhibit.html" onMouseOver="imgOn('exhibi
</tr>
<tr height="24">
<td height="24" width="115"><a href="http://www.virginia.edu/vfh/aahv/trails.html" onMouseOver="imgOn('trails
</tr>
</table>
<br> <br> </td>
<td valign="top" height="50" width="100%"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="53">
<tr height="13">
<td background="images/banner_
<td rowspan="3"><img src="images/banner_cap.gif
</tr>
<tr height="35">
<td width="100%" height="35" bgcolor="#996600"><a href="http://www.virginia.edu/vfh/aahv/index.html"><img src="images/banner_aahv.gi
</tr>
<tr height="5">
<td height="5" background="images/banner_
</tr>
</table></td>
</tr>
<tr>
<td width="100%" valign="top"> <table border="0" cellpadding="6" cellspacing="0" width="100%">
<tr>
<td valign="top"> <table border="0" cellpadding="2" cellspacing="0" width="100%" bgcolor="#336666">
<tr>
<td valign="top"> <table border="0" cellpadding="10" cellspacing="0" width="100%" bgcolor="#ffffea">
<tr>
<td valign="top"> <p><span class="header1">Search
Results: Sites & By Keywords </span><br>
<span class="header2"><%=(Reques
<% If rsSearchAll.EOF And rsSearchAll.BOF Then %>
<p align="center"><br>
<span class="header2">No matching records found<br>
</span><a href="http://www.virginia.edu/vfh/aahv/data.html"><br>
Search again</a><br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</p>
<% End If ' end rsSearchAll.EOF And rsSearchAll.BOF %>
<% If Not rsSearchAll.EOF Or Not rsSearchAll.BOF Then %>
<p><b>Displaying Records <%=(rsSearchAll_first)%>
to <%=(rsSearchAll_last)%> of <%=(rsSearchAll_total)%></
<br>
<table width="98%" border="1" cellspacing="0" cellpadding="6">
<tr bgcolor="#990000">
<td width="45%" height="14" class="table_caption">Keyw
<td width="30%" height="14" class="table_caption">Site
<td height="14" width="25%" class="table_caption">City
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSearchAll.EOF))
%>
<tr valign="top">
<td width="45%" height="14"><%=results = NewKeywordArray%>
</td>
<td><b><a href="sites.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "MailingListID=" & rsSearchAll.Fields.Item("M
<td><%=(rsSearchAll.Fields
<td>
<td height="14" width="15%"> </td>
</tr>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rsSearchAll.MoveNext()
Wend
%>
</table>
<br> <table border="0" width="50%" align="center">
<tr>
<td width="23%" align="center">
<% If MM_offset <> 0 Then %>
<a href="<%=MM_moveFirst%>">F
<% End If ' end MM_offset <> 0 %>
</td>
<td width="31%" align="center">
<% If MM_offset <> 0 Then %>
<a href="<%=MM_movePrev%>">Pr
<% End If ' end MM_offset <> 0 %>
</td>
<td width="23%" align="center">
<% If Not MM_atTotal Then %>
<a href="<%=MM_moveNext%>">Ne
<% End If ' end Not MM_atTotal %>
</td>
<td width="23%" align="center">
<% If Not MM_atTotal Then %>
<a href="<%=MM_moveLast%>">La
<% End If ' end Not MM_atTotal %>
</td>
</tr>
</table>
<% End If ' end Not rsSearchAll.EOF Or NOT rsSearchAll.BOF %>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr height="20">
<td width="100%" valign="bottom" align="center" height="20"> <table border="0" cellpadding="0" cellspacing="0">
<tr height="20">
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/about.html">About
the Program</a></td>
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/data.html">Sites
Database</a></td>
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/grants.html">Mini-Grants</a></t
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/exhibit.html">Exhibit</a></td>
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/trails.html">Heritage
Trails</a></td>
</tr>
</table></td>
</tr>
<tr height="5">
<td colspan="2" height="5" background="images/chrome_
</tr>
<tr>
<td colspan="2" bgcolor="#663300"> <table border="0" cellpadding="6" cellspacing="0" width="100%">
<tr>
<td width="50%"><span class="small_light">Africa
in Virginia<br>
Virginia Foundation for the Humanities<br>
145 Ednam Drive, Charlottesville, VA 22903-4629</span></td>
<td align="right"><a href="mailto:aahv@virginia
804.924.3296 &#
Last modified August 2, 2001 •&nbs
</tr>
</table></td>
</tr>
</table>
</body>
</html>
Second, the class file:
<%
'#################### START CLASS DEFINITION ########################
Class DBSearchEngine
'private variables
Private hTmpCount, bSearchTitles, bSearchDescriptions
'public variables become properties of the class
Public DBConnectionString
Public DBConnectionAccount
Public DBConnectionAcctPassword
Public bNoFieldsToSearch
Public SearchType
Public Property Get Version
Version = "2.0"
End Property
'public methods
Public Sub SearchTitles
bSearchTitles = true
End Sub
Public Sub SearchDescriptions
bSearchDescriptions = true
End Sub
Public Function Search(ByVal sKeyword)
' ado constant declaration. do not modify next line.
Const adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001
Dim strSQL, objConn, objRs, strTemp
strTemp = ""
'this is a nested call to 2 functions... if you start in the
'middle, first the MakeKeywordArray function is called
'and then the sqlString function is called using the returned
'values from MakeKeywordArray...
strSQL = sqlString( MakeKeywordArray( sKeyword ) )
'open db
Set objConn = Server.CreateObject("ADODB
objConn.Open DBConnectionString
Set objRs = Server.CreateObject("ADODB
objRs_numRows=0
objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
'retrieve recordcount
hTmpCount = objRs.RecordCount
if objRs.BOF then
strTemp = "<h3>No Matching Records</h3>"
else
'start at first record...
objRs.MoveFirst
'loop through all records
Do While NOT objRs.BOF AND NOT objRs.EOF
' call the function HTMLdisplay to properly
' format a valid entry for the results
'build return string, with nicely
'formatted html (not)
'build return string, with nicely
'formatted html (not)
strTemp = strTemp & HTMLdisplay(objRs)
objRs.MoveNext
Loop
end if
'close em up...
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
'return the built string of results...
Search = strTemp
End Function
Public Function Count
Count = hTmpCount
End Function
'private class routines
Private Sub Class_Initialize()
'class_initialize event is best used to set
'up default values for class scoped variables
bSearchTitles = false
bSearchDescriptions = false
End Sub
Private Function EscapeApostrophe(ByVal toEscape)
'make "my dog's fleas" become "my dog''s fleas",
'escaping the apostrophe character because
'it's meaning is special in TSQL...
EscapeApostrophe = replace(toEscape, "'", "''")
End Function
Private Function sqlString(ByVal keywords)
' this function creates an sql string based on various criteria
Dim i
'the base string
sqlString = "SELECT * FROM Sites_Orgs "
'check for any keywords entered...
if IsArray(keywords) then
if NOT bSearchTitles AND NOT bSearchDescriptions then
' repair the sql string if a client forgets to
' select a search field
sqlString = TRIM(sqlString) & ";"
bNoFieldsToSearch = True
'leave
Exit Function
end if
' don't forget the where if we're still here
sqlString = sqlString & "WHERE "
' make the criteria part of the statement based on the
' array of keywords and the chosen search fields
for i = 0 to UBOUND(keywords)
if bSearchTitles then sqlString = sqlString & _
"OrganizationName LIKE '%" &EscapeApostrophe(keywords
"%' " &SearchType & " "
next
if bSearchTitles AND bSearchDescriptions then
'need to clean the sql string and remove
'the last AND or OR to avoid an error...
sqlString = Left(sqlString, Len(sqlString)-4)
sqlString = TRIM(sqlString)
sqlString = sqlString & " OR ("
end if
for i = 0 to UBOUND(keywords)
if bSearchDescriptions then sqlString = sqlString & _
"HistoricalSignificance LIKE '%" &EscapeApostrophe(keywords
"%' " &SearchType & " "
next
sqlString = Left(sqlString, Len(sqlString)-4)
if SearchType = "OR" then sqlString = sqlString & " "
if bSearchTitles AND bSearchDescriptions then sqlString = sqlString & ") "
end if
' let's make the order by : most recent examples on top
sqlString = sqlString & "ORDER BY OrganizationName DESC;"
End Function
Private Function HTMLdisplay(ByRef objRs)
'the byreference argument above, passes a good-to-go record object
'so in here is where you would display field values from the db.
'for example: objRs.Fields(0).value
' creates the interface that displays linked results
' for examples that fit criteria
'i'm not big on html display, instead my focus is strictly
'functionality so the html leaves much to be desired but
'you can spruse it up....
HTMLdisplay = "<P>"
if objRs("IsNew") then
HTMLdisplay = HTMLdisplay & "NEW "
elseif objRs("IsUpdated") then
HTMLdisplay = HTMLdisplay & "UPDATED "
end if
if LCase(objRs("ExampleLangua
HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/exampl
elseif LCase(objRs("ExampleLangua
HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/exampl
elseif LCase(objRs("ExampleLangua
HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/exampl
end if
HTMLdisplay = HTMLdisplay & objRs("MailingListID") & ".asp""><FONT COLOR=""#60786B""><BIG><B>
HTMLdisplay = HTMLdisplay & objRs("HistoricalSignifica
HTMLdisplay = HTMLdisplay & "<BR>" & objRs("HistoricalSignifica
End Function
Private Function MakeKeywordArray(ByVal sWordVarTmp)
'makes an array of any entered keywords
Dim sDelim, x
sDelim = ","
if TRIM(sWordVarTmp) = "" then Exit Function
if not instr(sWordVarTmp, sDelim) then sDelim = " "
x = Split(sWordVarTmp, sDelim)
MakeKeywordArray = x
End Function
End Class
'#################### END CLASS DEFINITION ########################
%>
I have been struggling this week with piecing together code to put together a keyword search of a database using ASP.
I downloaded a version of a search engine class that someone had posted on the internet and successfully adapted it to pull from my database. However, in trying to pull it into my display page, I could not get it to cooperate and pull from my database. I'm not sure how much of the code you need to see to help, but I have a feeling it is because some of the information in my original search page (which is based on a list/menu) is redundant with the class file. Theoretically I know what I want to do and I feel like it should be easy to have the computer read the input, parse it, match it against the fields in the database and return a result as an array...but it hasn't worked out that way!
I guess I have to attach all the code...and I apologize in advance because it is lengthy...
In additiion to some tips as to how I might fix this to properly create a keyword search, I guess I would like to know if it is better in general to use the class file or to just build necessary functions into the search page. The other searches that are built in the site do not use classes. Thanks for your help!!!
first...the search file
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="./cls_dbsearchengine
<%
'variable declaration
Dim rsSearchAll, ct, results, noCriteria
'create instance of the class
Set rsSearchAll = New DBSearchEngine
With rsSearchAll
'db connection string
.DBConnectionString = "Provider=Microsoft.Jet.OL
'search type
.SearchType = Request("type")
'determine what to search
if request("OrganizationName"
if request("HistoricalSignifi
'gather results into a string
results = .Search( request("keywords") )
'get the count of records returned
ct = .Count
'find out whether or not the minimum search criteria
'was entered...
noCriteria = .bNoFieldsToSearch
End With
'free class instance (failure to explicitly free objects in
'ASP is generally considered a bad idea)
Set rsSearchAll = Nothing
'write the search results to the browser...
With Response
if noCriteria then .Write("<p>NO FIELDS SELECTED, SEARCH NOT REFINED!</p>")
.Write(ct & " records found.<BR>")
.Write(results)
End With
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = 10
Dim Repeat1__index
Repeat1__index = 0
rsSearchAll_numRows = rsSearchAll_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables
' set the record count
Dim rsSearchAll_total
rsSearchAll_total = rsSearchAll.RecordCount
' set the number of rows displayed on this page
If (rsSearchAll_numRows < 0) Then
rsSearchAll_numRows = rsSearchAll_total
Elseif (rsSearchAll_numRows = 0) Then
rsSearchAll_numRows = 1
End If
' set the first and last displayed record
rsSearchAll_first = 1
rsSearchAll_last = rsSearchAll_first + rsSearchAll_numRows - 1
' if we have the correct record count, check the other stats
If (rsSearchAll_total <> -1) Then
If (rsSearchAll_first > rsSearchAll_total) Then rsSearchAll_first = rsSearchAll_total
If (rsSearchAll_last > rsSearchAll_total) Then rsSearchAll_last = rsSearchAll_total
If (rsSearchAll_numRows > rsSearchAll_total) Then rsSearchAll_numRows = rsSearchAll_total
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them
If (rsSearchAll_total = -1 AND (NOT rsSearchAll.EOF)) Then
' count the total records by iterating through the recordset
rsSearchAll_total=0
While (Not rsSearchAll.EOF)
rsSearchAll_total = rsSearchAll_total + 1
rsSearchAll.MoveNext
Wend
' reset the cursor to the beginning
If (rsSearchAll.CursorType > 0) Then
rsSearchAll.MoveFirst
Else
rsSearchAll.Requery
End If
' set the number of rows displayed on this page
If (rsSearchAll_numRows < 0 Or rsSearchAll_numRows > rsSearchAll_total) Then
rsSearchAll_numRows = rsSearchAll_total
End If
' set the first and last displayed record
rsSearchAll_first = 1
rsSearchAll_last = rsSearchAll_first + rsSearchAll_numRows - 1
If (rsSearchAll_first > rsSearchAll_total) Then rsSearchAll_first = rsSearchAll_total
If (rsSearchAll_last > rsSearchAll_total) Then rsSearchAll_last = rsSearchAll_total
End If
%>
<%
' *** Move To Record and Go To Record: declare variables
Set MM_rs = rsSearchAll
MM_rsCount = rsSearchAll_total
MM_size = rsSearchAll_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
MM_paramIsDefined = (Request.QueryString(MM_pa
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter
if (Not MM_paramIsDefined And MM_rsCount <> 0) then
' use index parameter if defined, otherwise use offset parameter
r = Request.QueryString("index
If r = "" Then r = Request.QueryString("offse
If r <> "" Then MM_offset = Int(r)
' if we have a record count, check if we are past the end of the recordset
If (MM_rsCount <> -1) Then
If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or move last
If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a full repeat region
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If
' move the cursor to the selected record
i = 0
While ((Not MM_rs.EOF) And (i < MM_offset Or MM_offset = -1))
MM_rs.MoveNext
i = i + 1
Wend
If (MM_rs.EOF) Then MM_offset = i ' set MM_offset to the last possible record
End If
%>
<%
' *** Move To Record: if we dont know the record count, check the display range
If (MM_rsCount = -1) Then
' walk to the end of the display range for this page
i = MM_offset
While (Not MM_rs.EOF And (MM_size < 0 Or i < MM_offset + MM_size))
MM_rs.MoveNext
i = i + 1
Wend
' if we walked off the end of the recordset, set MM_rsCount and MM_size
If (MM_rs.EOF) Then
MM_rsCount = i
If (MM_size < 0 Or MM_size > MM_rsCount) Then MM_size = MM_rsCount
End If
' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) > 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If
' reset the cursor to the beginning
If (MM_rs.CursorType > 0 AND (NOT rsSearchAll.EOF)) Then
MM_rs.MoveFirst
Else
MM_rs.Requery
End If
' move the cursor to the selected record
i = 0
While (Not MM_rs.EOF And i < MM_offset)
MM_rs.MoveNext
i = i + 1
Wend
End If
%>
<%
' *** Move To Record: update recordset stats
' set the first and last displayed record
rsSearchAll_first = MM_offset + 1
rsSearchAll_last = MM_offset + MM_size
If (MM_rsCount <> -1) Then
If (rsSearchAll_first > MM_rsCount) Then rsSearchAll_first = MM_rsCount
If (rsSearchAll_last > MM_rsCount) Then rsSearchAll_last = MM_rsCount
End If
' set the boolean used by hide region to check if we are on the last record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="
MM_keepURL="":MM_keepForm=
' add the URL parameters to the MM_keepURL string
For Each Item In Request.Search
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,Nex
MM_keepURL = MM_keepURL & NextItem & Server.URLencode(Request.S
End If
Next
' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,Nex
MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.F
End If
Next
' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> "") Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and previous links
MM_keepMove = MM_keepBoth
MM_moveParam = "index"
' if the page has a repeated region, remove 'offset' from the maintained parameters
If (MM_size > 0) Then
MM_moveParam = "offset"
If (MM_keepMove <> "") Then
params = Split(MM_keepMove, "&")
MM_keepMove = ""
For i = 0 To UBound(params)
nextItem = Left(params(i), InStr(params(i),"=") - 1)
If (StrComp(nextItem,MM_moveP
MM_keepMove = MM_keepMove & "&" & params(i)
End If
Next
If (MM_keepMove <> "") Then
MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
End If
End If
End If
' set the strings for the move to links
If (MM_keepMove <> "") Then MM_keepMove = MM_keepMove & "&"
urlStr = Request.ServerVariables("U
MM_moveFirst = urlStr & "0"
MM_moveLast = urlStr & "-1"
MM_moveNext = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev < 0) Then prev = 0
MM_movePrev = urlStr & Cstr(prev)
%>
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset
<title>African American Heritage in Virginia</title>
<link rel="stylesheet" href="aahv.css">
<script language="JavaScript" src="nav.js"></script>
</head>
<body bgcolor="#cc9933" text="#363636" link="#336666" alink="#996600" vlink="#663300" leftmargin="0" marginwidth="0" topmargin="0" marginheight="0">
<table border="0" cellpadding="1" cellspacing="0" width="95%" bgcolor="#996600">
<td height="90">
<tr>
<td> <table border="0" cellpadding="0" cellspacing="0" width="100%" bgcolor="#cc9933">
<tr height="50">
<td width="116" rowspan="3" background="images/chrome_
<table border="0" cellpadding="0" cellspacing="0" width="115" height="154">
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/index.html" onMouseOver="imgOn('homeLI
</tr>
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/about.html" onMouseOver="imgOn('aboutL
</tr>
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/data.html" onMouseOver="imgOn('dataLI
</tr>
<tr height="26">
<td width="115" height="26"><a href="http://www.virginia.edu/vfh/aahv/grants.html" onMouseOver="imgOn('grants
</tr>
<tr height="26">
<td height="26" width="115"><a href="http://www.virginia.edu/vfh/aahv/exhibit.html" onMouseOver="imgOn('exhibi
</tr>
<tr height="24">
<td height="24" width="115"><a href="http://www.virginia.edu/vfh/aahv/trails.html" onMouseOver="imgOn('trails
</tr>
</table>
<br> <br> </td>
<td valign="top" height="50" width="100%"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="53">
<tr height="13">
<td background="images/banner_
<td rowspan="3"><img src="images/banner_cap.gif
</tr>
<tr height="35">
<td width="100%" height="35" bgcolor="#996600"><a href="http://www.virginia.edu/vfh/aahv/index.html"><img src="images/banner_aahv.gi
</tr>
<tr height="5">
<td height="5" background="images/banner_
</tr>
</table></td>
</tr>
<tr>
<td width="100%" valign="top"> <table border="0" cellpadding="6" cellspacing="0" width="100%">
<tr>
<td valign="top"> <table border="0" cellpadding="2" cellspacing="0" width="100%" bgcolor="#336666">
<tr>
<td valign="top"> <table border="0" cellpadding="10" cellspacing="0" width="100%" bgcolor="#ffffea">
<tr>
<td valign="top"> <p><span class="header1">Search
Results: Sites & By Keywords </span><br>
<span class="header2"><%=(Reques
<% If rsSearchAll.EOF And rsSearchAll.BOF Then %>
<p align="center"><br>
<span class="header2">No matching records found<br>
</span><a href="http://www.virginia.edu/vfh/aahv/data.html"><br>
Search again</a><br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
</p>
<% End If ' end rsSearchAll.EOF And rsSearchAll.BOF %>
<% If Not rsSearchAll.EOF Or Not rsSearchAll.BOF Then %>
<p><b>Displaying Records <%=(rsSearchAll_first)%>
to <%=(rsSearchAll_last)%> of <%=(rsSearchAll_total)%></
<br>
<table width="98%" border="1" cellspacing="0" cellpadding="6">
<tr bgcolor="#990000">
<td width="45%" height="14" class="table_caption">Keyw
<td width="30%" height="14" class="table_caption">Site
<td height="14" width="25%" class="table_caption">City
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsSearchAll.EOF))
%>
<tr valign="top">
<td width="45%" height="14"><%=results = NewKeywordArray%>
</td>
<td><b><a href="sites.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "MailingListID=" & rsSearchAll.Fields.Item("M
<td><%=(rsSearchAll.Fields
<td>
<td height="14" width="15%"> </td>
</tr>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rsSearchAll.MoveNext()
Wend
%>
</table>
<br> <table border="0" width="50%" align="center">
<tr>
<td width="23%" align="center">
<% If MM_offset <> 0 Then %>
<a href="<%=MM_moveFirst%>">F
<% End If ' end MM_offset <> 0 %>
</td>
<td width="31%" align="center">
<% If MM_offset <> 0 Then %>
<a href="<%=MM_movePrev%>">Pr
<% End If ' end MM_offset <> 0 %>
</td>
<td width="23%" align="center">
<% If Not MM_atTotal Then %>
<a href="<%=MM_moveNext%>">Ne
<% End If ' end Not MM_atTotal %>
</td>
<td width="23%" align="center">
<% If Not MM_atTotal Then %>
<a href="<%=MM_moveLast%>">La
<% End If ' end Not MM_atTotal %>
</td>
</tr>
</table>
<% End If ' end Not rsSearchAll.EOF Or NOT rsSearchAll.BOF %>
</td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
<tr height="20">
<td width="100%" valign="bottom" align="center" height="20"> <table border="0" cellpadding="0" cellspacing="0">
<tr height="20">
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/about.html">About
the Program</a></td>
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/data.html">Sites
Database</a></td>
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/grants.html">Mini-Grants</a></t
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/exhibit.html">Exhibit</a></td>
<td height="20"><img src="images/spacer_dot.gif
<td height="20"><a class="navlink" href="http://www.virginia.edu/vfh/aahv/trails.html">Heritage
Trails</a></td>
</tr>
</table></td>
</tr>
<tr height="5">
<td colspan="2" height="5" background="images/chrome_
</tr>
<tr>
<td colspan="2" bgcolor="#663300"> <table border="0" cellpadding="6" cellspacing="0" width="100%">
<tr>
<td width="50%"><span class="small_light">Africa
in Virginia<br>
Virginia Foundation for the Humanities<br>
145 Ednam Drive, Charlottesville, VA 22903-4629</span></td>
<td align="right"><a href="mailto:aahv@virginia
804.924.3296 &#
Last modified August 2, 2001 •&nbs
</tr>
</table></td>
</tr>
</table>
</body>
</html>
Second, the class file:
<%
'#################### START CLASS DEFINITION ########################
Class DBSearchEngine
'private variables
Private hTmpCount, bSearchTitles, bSearchDescriptions
'public variables become properties of the class
Public DBConnectionString
Public DBConnectionAccount
Public DBConnectionAcctPassword
Public bNoFieldsToSearch
Public SearchType
Public Property Get Version
Version = "2.0"
End Property
'public methods
Public Sub SearchTitles
bSearchTitles = true
End Sub
Public Sub SearchDescriptions
bSearchDescriptions = true
End Sub
Public Function Search(ByVal sKeyword)
' ado constant declaration. do not modify next line.
Const adOpenStatic = 3, adLockReadOnly = 1, adCmdText = &H0001
Dim strSQL, objConn, objRs, strTemp
strTemp = ""
'this is a nested call to 2 functions... if you start in the
'middle, first the MakeKeywordArray function is called
'and then the sqlString function is called using the returned
'values from MakeKeywordArray...
strSQL = sqlString( MakeKeywordArray( sKeyword ) )
'open db
Set objConn = Server.CreateObject("ADODB
objConn.Open DBConnectionString
Set objRs = Server.CreateObject("ADODB
objRs_numRows=0
objRs.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
'retrieve recordcount
hTmpCount = objRs.RecordCount
if objRs.BOF then
strTemp = "<h3>No Matching Records</h3>"
else
'start at first record...
objRs.MoveFirst
'loop through all records
Do While NOT objRs.BOF AND NOT objRs.EOF
' call the function HTMLdisplay to properly
' format a valid entry for the results
'build return string, with nicely
'formatted html (not)
'build return string, with nicely
'formatted html (not)
strTemp = strTemp & HTMLdisplay(objRs)
objRs.MoveNext
Loop
end if
'close em up...
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
'return the built string of results...
Search = strTemp
End Function
Public Function Count
Count = hTmpCount
End Function
'private class routines
Private Sub Class_Initialize()
'class_initialize event is best used to set
'up default values for class scoped variables
bSearchTitles = false
bSearchDescriptions = false
End Sub
Private Function EscapeApostrophe(ByVal toEscape)
'make "my dog's fleas" become "my dog''s fleas",
'escaping the apostrophe character because
'it's meaning is special in TSQL...
EscapeApostrophe = replace(toEscape, "'", "''")
End Function
Private Function sqlString(ByVal keywords)
' this function creates an sql string based on various criteria
Dim i
'the base string
sqlString = "SELECT * FROM Sites_Orgs "
'check for any keywords entered...
if IsArray(keywords) then
if NOT bSearchTitles AND NOT bSearchDescriptions then
' repair the sql string if a client forgets to
' select a search field
sqlString = TRIM(sqlString) & ";"
bNoFieldsToSearch = True
'leave
Exit Function
end if
' don't forget the where if we're still here
sqlString = sqlString & "WHERE "
' make the criteria part of the statement based on the
' array of keywords and the chosen search fields
for i = 0 to UBOUND(keywords)
if bSearchTitles then sqlString = sqlString & _
"OrganizationName LIKE '%" &EscapeApostrophe(keywords
"%' " &SearchType & " "
next
if bSearchTitles AND bSearchDescriptions then
'need to clean the sql string and remove
'the last AND or OR to avoid an error...
sqlString = Left(sqlString, Len(sqlString)-4)
sqlString = TRIM(sqlString)
sqlString = sqlString & " OR ("
end if
for i = 0 to UBOUND(keywords)
if bSearchDescriptions then sqlString = sqlString & _
"HistoricalSignificance LIKE '%" &EscapeApostrophe(keywords
"%' " &SearchType & " "
next
sqlString = Left(sqlString, Len(sqlString)-4)
if SearchType = "OR" then sqlString = sqlString & " "
if bSearchTitles AND bSearchDescriptions then sqlString = sqlString & ") "
end if
' let's make the order by : most recent examples on top
sqlString = sqlString & "ORDER BY OrganizationName DESC;"
End Function
Private Function HTMLdisplay(ByRef objRs)
'the byreference argument above, passes a good-to-go record object
'so in here is where you would display field values from the db.
'for example: objRs.Fields(0).value
' creates the interface that displays linked results
' for examples that fit criteria
'i'm not big on html display, instead my focus is strictly
'functionality so the html leaves much to be desired but
'you can spruse it up....
HTMLdisplay = "<P>"
if objRs("IsNew") then
HTMLdisplay = HTMLdisplay & "NEW "
elseif objRs("IsUpdated") then
HTMLdisplay = HTMLdisplay & "UPDATED "
end if
if LCase(objRs("ExampleLangua
HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/exampl
elseif LCase(objRs("ExampleLangua
HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/exampl
elseif LCase(objRs("ExampleLangua
HTMLdisplay = HTMLdisplay & "<A HREF=""/aspEmporium/exampl
end if
HTMLdisplay = HTMLdisplay & objRs("MailingListID") & ".asp""><FONT COLOR=""#60786B""><BIG><B>
HTMLdisplay = HTMLdisplay & objRs("HistoricalSignifica
HTMLdisplay = HTMLdisplay & "<BR>" & objRs("HistoricalSignifica
End Function
Private Function MakeKeywordArray(ByVal sWordVarTmp)
'makes an array of any entered keywords
Dim sDelim, x
sDelim = ","
if TRIM(sWordVarTmp) = "" then Exit Function
if not instr(sWordVarTmp, sDelim) then sDelim = " "
x = Split(sWordVarTmp, sDelim)
MakeKeywordArray = x
End Function
End Class
'#################### END CLASS DEFINITION ########################
%>
Hi
What columns are you attempting to search? You seem to have an awful lot of code for what is usually a simple operation! I've done several search pages and they usually fall into one of two categories:
(1) You need to search on several columns of different types. In this case I build a WHERE string by having a control for each column and checking each condition.
(2) You are just looking for a string in any of several columns. Here I just concatenate the columns and look for the string, like:
SELECT ColA + ColB + C AS Cols FROM TableName WHERE Cols LIKE '%String%'
With this approach you don't need special classes and the code is fairly short. If this is what you are looking for, ask and I'll post some sample code.
What columns are you attempting to search? You seem to have an awful lot of code for what is usually a simple operation! I've done several search pages and they usually fall into one of two categories:
(1) You need to search on several columns of different types. In this case I build a WHERE string by having a control for each column and checking each condition.
(2) You are just looking for a string in any of several columns. Here I just concatenate the columns and look for the string, like:
SELECT ColA + ColB + C AS Cols FROM TableName WHERE Cols LIKE '%String%'
With this approach you don't need special classes and the code is fairly short. If this is what you are looking for, ask and I'll post some sample code.
ASKER
crescendo:
if you have some simple examples of code just looking for a string of text (and just searching for the same "type" of data") --- how does one concatenate the columns?
Just using the above select statement modified to my database, I am getting the following error:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
Any advice would be greatly appreciated.
Thanks!
if you have some simple examples of code just looking for a string of text (and just searching for the same "type" of data") --- how does one concatenate the columns?
Just using the above select statement modified to my database, I am getting the following error:
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
Any advice would be greatly appreciated.
Thanks!
ASKER
actually, it does have to search for strings of text between two different type of data...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
he means numeric vs text
and their respective db field type
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<TABLE id="Table1" cellSpacing="0" cellPadding="5" width="550" border="0">
<TR>
<TD class="PageHeader" style="HEIGHT: 24px" colSpan="2">Search Products</TD>
</TR>
<TR>
<TD width="192">Query</TD>
<TD width="338"><asp:textbox id="SearchQuery" runat="server" CssClass="ThinInput" Columns="15" MaxLength="50"></asp:textb
<asp:requiredfieldvalidato
ErrorMessage="A query is required."></asp:requiredf
</TR>
<TR>
<TD width="192">Match</TD>
<TD width="338"><asp:dropdownl
<asp:ListItem Value="ProductName" Selected="True">Product Name</asp:ListItem>
<asp:ListItem Value="Description">Descri
<asp:ListItem Value="Either">Either</asp
</asp:dropdownlist></TD>
</TR>
<TR>
<TD align="right" colSpan="2"><INPUT id="FullQuery" type="hidden" name="FullQuery" runat="server"><asp:button
Text="Search"></asp:button
</TR>
</TABLE>
<BR>
<asp:datagrid id="Products" runat="server" Width="550px" BorderColor="DarkGray" AutoGenerateColumns="False
CellPadding="5" GridLines="Horizontal" AllowPaging="True">
<AlternatingItemStyle BackColor="White"></Altern
<ItemStyle BackColor="WhiteSmoke"></I
<HeaderStyle Font-Bold="True"></HeaderS
<Columns>
<asp:TemplateColumn HeaderText="Product Name">
<HeaderStyle Width="440px"></HeaderStyl
<ItemTemplate>
<asp:Label id="ProductInfo" runat="server"></asp:Label
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<ItemStyle HorizontalAlign="Right"></
<ItemTemplate>
<asp:LinkButton id="EditItem" runat="server" CommandName="Edit">Edit</a
<asp:LinkButton id="DeleteItem" runat="server" CommandName="Delete">Delet
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
<PagerStyle PageButtonCount="20" Mode="NumericPages"></Page
</asp:datagrid></TD>
</TR>
</TABLE>
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Query As String
If Not IsPostBack Then LoadProductData()
End Sub
Region " Products DataGrid "
Private Sub Products_ItemDataBound(ByV
Dim DeleteItem As LinkButton
Dim ProductName As Label
Dim ImageSrc As HtmlInputHidden
If e.Item.ItemType = ListItemType.AlternatingIt
DeleteItem = DirectCast(e.Item.FindCont
DeleteItem.Attributes.Add(
"product?');")
ProductName = DirectCast(e.Item.FindCont
ProductName.Text = CStr(DataBinder.Eval(e.Ite
ProductName.Text &= "<BR> &nb
CStr(DataBinder.Eval(e.Ite
If Not DataBinder.Eval(e.Item.Dat
ProductName.Text &= " » " & CStr(DataBinder.Eval(e.Ite
End If
If Not DataBinder.Eval(e.Item.Dat
ProductName.Text &= " » " & CStr(DataBinder.Eval(e.Ite
End If
ImageSrc = DirectCast(e.Item.FindCont
ImageSrc.Value = CStr(DataBinder.Eval(e.Ite
End If
End Sub
Private Sub Products_ItemCommand(ByVal
If IsRefresh Then Return
If e.CommandName = "Delete" Then
Dim cnn As New OleDb.OleDbConnection(Conf
Dim cmd As New OleDb.OleDbCommand
Dim ImageSrc As String = DirectCast(e.Item.FindCont
Dim ProductImage As IO.FileInfo
cmd.CommandType = CommandType.Text
cmd.CommandText = "DELETE FROM Products WHERE ProductID = " & _
CStr(Products.DataKeys(e.I
cmd.Connection = cnn
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
cmd.Dispose()
cnn.Dispose()
Try
ProductImage = New IO.FileInfo(Server.MapPath
If ProductImage.Exists Then ProductImage.Delete()
ProductImage = New IO.FileInfo(Server.MapPath
If ProductImage.Exists Then ProductImage.Delete()
Catch ex As Exception
End Try
LoadProductData()
ElseIf e.CommandName = "Edit" Then
Response.Redirect("EditPro
End If
End Sub
Private Sub Products_PageIndexChanged(
Products.CurrentPageIndex = e.NewPageIndex
LoadProductData()
End Sub
Private Sub LoadProductData(Optional ByVal Query As String = "")
Dim cnn As New OleDb.OleDbConnection(Conf
Dim cmd As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter(cmd
Dim dt As New DataTable
'If the query is not passed, check to see if a previous query has been executed and stored
If Query = "" Then Query = FullQuery.Value
'If it's still empty, use the default query
If Query = "" Then
Query = "SELECT Tier1.Name AS Tier1Name, Tier2.Name AS Tier2Name, Tier3.Name AS Tier3Name, " & _
"Products.Name AS ProductName, Products.ProductID, Products.ImageSrc FROM Tier3 RIGHT " & _
"JOIN (Tier2 RIGHT JOIN (Tier1 INNER JOIN Products ON Tier1.Tier1ID = Products.Tier1ID) " & _
"ON Tier2.Tier2ID = Products.Tier2ID) ON Tier3.Tier3ID = Products.Tier3ID ORDER BY " & _
"Products.Name"
End If
cmd.CommandType = CommandType.Text
cmd.CommandText = Query
cmd.Connection = cnn
da.Fill(dt)
Products.DataKeyField = "ProductID"
Products.DataSource = dt
Products.DataBind()
cmd.Dispose()
cnn.Dispose()
If Products.Items.Count = 0 Then
Products.Visible = False
Else
Products.Visible = True
End If
End Sub
#End Region
#Region " SearchProducts Button "
Private Sub SearchProducts_Click(ByVal
QueryValidator.Enabled = True
Tier1Validator.Enabled = False
ProductNameValidator.Enabl
Validate()
If Not IsValid Then Return
Dim Query As String
Query = "SELECT Tier1.Name, Tier2.Name, Tier3.Name, Products.Name, Products.ProductID " & _
"FROM Tier3 RIGHT JOIN (Tier2 RIGHT JOIN (Tier1 INNER JOIN Products ON Tier1.Tier1ID = " & _
"Products.Tier1ID) ON Tier2.Tier2ID = Products.Tier2ID) ON Tier3.Tier3ID = " & _
"Products.Tier3ID WHERE "
Select Case FieldsToMatch.SelectedValu
Case "ProductName"
Query &= "ProductName LIKE '%" & SearchQuery.Text & "%'"
Case "Description"
Query &= "Products.Description LIKE '%" & SearchQuery.Text & "%'"
Case "Either"
Query &= "Products.Description LIKE '%" & SearchQuery.Text & "%' OR ProductName LIKE '%" & _
SearchQuery.Text & "%'"
End Select
Query &= " ORDER BY Products.Name"
LoadProductData(Query)
FullQuery.Value = Query
End Sub
#End Region
If you have any questions, let me know.
Regards,
Aeros