T-Cubed advanced search - 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)

T-Cubed advanced search

Author

Topic

Ritu
Starting Member

4 Posts

Posted - 2001-07-04 : 11:44:50

Hi guys! I have made a search page in asp which only searches one cloumn of a table in the database. Now i want it to be able to search all the columns of the table at the same time...here is the Code ......
<%@LANGUAGE="VBSCRIPT"%>

<%
Dim CompType,t3_advSearch_String
'Store type of query in CompType ie All Words/OR, Any Word/AND or Exact Phrase/EXACT
CompType = Request("CompType")

SearchColumn = "productType "

SearchField = "zzz"
if(Request("varsearch") <> "") then SearchField = Request("varsearch")
'Remove any single quotes from search field to eliminate potential errors.
SearchField = Replace(SearchField, "'", "")

'Checks the CompType, Executes this code if the option All words or Any Word is chosen
if(CompType <> "EXACT") then
t3_advSearch_String = "WHERE " & SearchColumn & " LIKE '%"
'Splits the search criteria into seperate words and stores them in an Array
SearchArray=Split(SearchField," ")
for i = 0 to Ubound(SearchArray)
if i > 0 then
'Builds the sql statement using the CompType to substitute AND/OR
t3_advSearch_String = t3_advSearch_String & " " & CompType & " " & SearchColumn & " LIKE '%"& SearchArray(i) & "%'"
else
'Ends the sql statement if there is only one word
t3_advSearch_String = t3_advSearch_String & SearchArray(i) & "%'"
end if
next

else
t3_advSearch_String = "WHERE " & SearchColumn & " LIKE '%" & SearchField & "%'"
end if
%>
<%
Dim Recordset1__varsearch
Recordset1__varsearch = "%"
if (t3_advSearch_String <> "") then Recordset1__varsearch = t3_advSearch_String
%>
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "y"
if (Request("MM_EmptyValue") <> "") then Recordset1__MMColParam = Request("MM_EmptyValue")
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_best_STRING
Recordset1.Source = "SELECT * FROM dbo.intranet " + Replace(Recordset1__varsearch, "'", "'") + ""

Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = 10
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table width="75%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Product Type</td>
<td>Product Title</td>
<td>Short description</td>
</tr>
</table>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<table width="75%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td> <%=(Recordset1.Fields.Item("productType").Value)%> </td>
<td> <%=(Recordset1.Fields.Item("productTitle").Value)%> </td>
<td> <%=(Recordset1.Fields.Item("shortDescription").Value)%> </td>
</tr>
</table>
<p> </p>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</body>
</html>
<%
Recordset1.Close()
%>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources