Linked Search Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Linked Search Results

Author

Topic

crtdude
Starting Member

13 Posts

Posted - 2001-01-08 : 12:27:05

Good day. I need a bit of help with the display of data retrieved from the search of a data column. I have customized a search script originally published by Jerry Wood (in 4 Guys site) and though it works very well, does not display the data where I can link to a sucessor page detailing more information about a selection. I would like my data to display as 4 columns (course_id, course_name, course_city, course_state) and have the course_id column link to a page called seemore.asp where I can pass the id # off in the URL.

Could use some Real pro help here.. Thanks in advance.

Here's the code:

<HTML>
<HEAD><TITLE>Search Results</TITLE></HEAD>
<BODY BGCOLOR="#FFFFFF" LINK="#003300" VLINK="#CC6666">
<%
' 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 course_name, course_city, course_state 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 "DRIVER={SQL Server};SERVER=BLAH;DATABASE=BLAH;UID=BLAH;PWD=BLAH;"

'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...
%>
<CENTER>
<TABLE WIDTH="450" CELLPADDING="0" CELLSPACING="0" BORDER=0>
<TR>
<TD align="center">
<P><IMG SRC="../images/search_results.gif" ALT="Search Results" WIDTH="194" HEIGHT="31" BORDER="0" HSPACE="0" VSPACE="0"><BR><BR></P>
</td>
</tr>
<TR>
<%
Do While Not rsRecords.EOF
%>
<TR>
<% For i = 0 to rsRecords.Fields.Count-1 %>
<TD ALIGN=LEFT><%Response.Write(rsRecords(i))%>
<% Next %>
</TR>
<%rsRecords.MoveNext%>
<%
Loop
%>
</TABLE>
</CENTER>
<%
Else
'The Search was futile, no records returned.
Response.Write("<TABLE WIDTH=450 CELLPADDING=0 CELLSPACING=0 BORDER=0>")
Response.Write("<TR>")
Response.Write("<TD align=center>")
Response.Write("<P><IMG SRC=../images/search_results.gif ALT=Search Results WIDTH=194 HEIGHT=31 BORDER=0 HSPACE=0 VSPACE=0></P>")
Response.Write("<FONT SIZE=3><B><CENTER>Your search produced no matches.</CENTER></B>")
Response.Write("</FONT></CENTER></TABLE>")
End If
%>
<TR><TD> </TD></TR>
<TR><TD><center><FONT FACE="helvetica, arial, sans serif" SIZE="-1"><A HREF="course_search.asp">New Search</A></FONT></TD></TR>
</CENTER>
<P> </P>

<CENTER><P><img src="../images/common/footer.gif" width="194" height="50" border="0" alt="TeeTimesUSA"></P></CENTER>

</BODY>
</HTML>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources