ASP Database search

[Jusenkyo]

Hello all

I have already created an ASP search page that allows me to type a surname into a text field, and view the results (taken from a SQL table) in a table on the same page.

I want to expand this to allow the user to type in their chosen search word, and then select which field they would like to search on. i.e ...

Search text box: Jones Search Type: Surname
First Name
Department

So they can now type in either a first name, Surname or department name - and the results will either show up in the same table, or on a new form, whichever is easiest!.

I have found a tutorial which does kind of what I want (

http://www.tdsf.co.uk/tdsfdemo/),

but a)I cant get it to work, and b) its not in MX - so I cant find half the things on there!

So, my question is this. Can I just add a drop down list onto my current form, and change the below SQL code to look at the selection from this box, and then search on the relevant field. Or is it more complicated than that?
SQL Code: SELECT TelephoneDetails.[Tel Number], TelephoneDetails.[Internal Ext Number], TelephoneDetails.[Member Of Hunt Group], TelephoneDetails.[Hunt Group Number], Employees.[First Name], Employees.Surname, Employees.EMail_Address, Employees.Department, Employees.[Office Location], Employees.[Responible To], Employees.Manager, Employees.PhotoLocation
FROM TelephoneDetails INNER JOIN Employees ON TelephoneDetails.[E Number] = Employees.[E Number]
WHERE SOUNDEX(Surname) = SOUNDEX('MMColParam') OR Surname like 'MMColParam' + '%'
ORDER BY [First Name];

Does anyone know of a tutorial that I can look at? Or can someone adjust the above code to suit?

Cheers in advance guys - sorry for the HUGE post!
Jusenkyo

 

[asppage]

I'm using the code below to accomplish something similar to what you want to do. It allows me to searth the entire table be each field to get the results I'm looking for. I have a filed called SearchText. This script does away with the need for them to select which field they want to search in. This is what an Access db.


If Request.Form("btnSearch&quot = "Search" Then

SQL = "SELECT CommID, ANumber, FirstName, LastName, Department, TeamLeader, DateEntered, DateOfService, NumberOfHours, TimeTakenWhen, CorporateActivityType, OrgSchoolReceivingService, DescriptionOfActivity, City "
SQL = SQL & "FROM CommunityService "
SQL = SQL & "WHERE (((CommunityService.ANumber) Like '%" & SearchText & "%')) OR (((CommunityService.FirstName) Like '%" & SearchText & "%')) OR (((CommunityService.LastName) Like '%" & SearchText & "%')) OR (((CommunityService.TeamLeader) Like '%" & SearchText & "%')) OR (((CommunityService.Department) Like '%" & SearchText & "%')) OR (((CommunityService.CorporateActivityType) Like '%" & SearchText & "%')) OR (((CommunityService.DateOfService) Like '%" & SearchText & "%'))"
SQL = SQL & "ORDER By DateOfService"

End If

 

[Jusenkyo]

Hiya asppage

I am not too good with ASP, in fact, this whole web development thing is new to me!

Where would I put this code? The code I gave you was in the recordset in a Dreamweaver MX page...

Sorry, I aint to good at this!

 

[asppage]

I need to see the code to your page. Can you post it?

Don't sweat the small stuff.

 

[Jusenkyo]

The code is kinda big!

But here you go anyways -

<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!--#include file=&quot;Connections/TelConn.asp&quot; -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = &quot;1&quot;
If (Request.Form(&quot;SearchTXT&quot <> &quot;&quot Then
Recordset1__MMColParam = Request.Form(&quot;SearchTXT&quot
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject(&quot;ADODB.Recordset&quot
Recordset1.ActiveConnection = MM_TelConn_STRING
Recordset1.Source = &quot;SELECT TelephoneDetails.[Tel Number], TelephoneDetails.[Internal Ext Number], TelephoneDetails.[Member Of Hunt Group], TelephoneDetails.[Hunt Group Number], Employees.[First Name], Employees.Surname, Employees.EMail_Address, Employees.Department, Employees.[Office Location], Employees.[Responible To], Employees.Manager, Employees.PhotoLocation FROM TelephoneDetails INNER JOIN Employees ON TelephoneDetails.[E Number] = Employees.[E Number] WHERE SOUNDEX(Surname) = SOUNDEX('&quot; + Replace(Recordset1__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;') OR Surname like '&quot; + Replace(Recordset1__MMColParam, &quot;'&quot;, &quot;''&quot + &quot;' + '%'ORDER BY [First Name];&quot;
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim Recordset1_total
Dim Recordset1_first
Dim Recordset1_last

' set the record count
Recordset1_total = Recordset1.RecordCount

' set the number of rows displayed on this page
If (Recordset1_numRows < 0) Then
Recordset1_numRows = Recordset1_total
Elseif (Recordset1_numRows = 0) Then
Recordset1_numRows = 1
End If

' set the first and last displayed record
Recordset1_first = 1
Recordset1_last = Recordset1_first + Recordset1_numRows - 1

' if we have the correct record count, check the other stats
If (Recordset1_total <> -1) Then
If (Recordset1_first > Recordset1_total) Then
Recordset1_first = Recordset1_total
End If
If (Recordset1_last > Recordset1_total) Then
Recordset1_last = Recordset1_total
End If
If (Recordset1_numRows > Recordset1_total) Then
Recordset1_numRows = Recordset1_total
End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (Recordset1_total = -1) Then

' count the total records by iterating through the recordset
Recordset1_total=0
While (Not Recordset1.EOF)
Recordset1_total = Recordset1_total + 1
Recordset1.MoveNext
Wend

' reset the cursor to the beginning
If (Recordset1.CursorType > 0) Then
Recordset1.MoveFirst
Else
Recordset1.Requery
End If

' set the number of rows displayed on this page
If (Recordset1_numRows < 0 Or Recordset1_numRows > Recordset1_total) Then
Recordset1_numRows = Recordset1_total
End If

' set the first and last displayed record
Recordset1_first = 1
Recordset1_last = Recordset1_first + Recordset1_numRows - 1

If (Recordset1_first > Recordset1_total) Then
Recordset1_first = Recordset1_total
End If
If (Recordset1_last > Recordset1_total) Then
Recordset1_last = Recordset1_total
End If

End If
%>
<html>
<head>
<title>Telephone Number Search</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
<style type=&quot;text/css&quot;>
<!--
.Box1 {
height: 100px;
width: 85px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
text-align: center;
text-indent: 1pt;
vertical-align: middle;
}
.BoxSmall {
height: 102px;
width: 10px;
font-family: Arial, Helvetica, sans-serif;
font-size: 9px;
text-align: center;
text-indent: 1pt;
vertical-align: middle;
background-image: url(images/DBTable/LeftDBtable.jpg);
}
.Photot {
height: 100px;
width: 100px;
font-family: Arial, Helvetica, sans-serif;
font-size: 9px;
text-align: center;
vertical-align: middle;
}
.Titles {
height: 38px;
width: 85px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
text-align: center;
vertical-align: middle;
color: #FF6600;
background-color: #FFFFFF;
background-image: url(images/TableTitle1.jpg);
}
.Titles2 {
height: 38px;
width: 100px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
text-align: center;
vertical-align: middle;
color: #FF6600;
background-color: #FFFFFF;
background-image: url(images/TableTitle3.jpg);
}
-->
</style>
<style type=&quot;text/css&quot;>
<!--
.NewBoxSmaller {
text-align: center;
text-indent: 1pt;
vertical-align: middle;
height: 100px;
width: 80px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
color: #3300CC;
}
.Titles3 {
height: 38px;
width: 80px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
text-align: center;
vertical-align: middle;
color: #FF6600;
background-color: #FFFFFF;
background-image: url(images/TableTitle1.jpg);
}
.BoxLarger {
height: 100px;
width: 90px;
font-family: Arial, Helvetica, sans-serif;
font-size: 9px;
text-align: center;
text-indent: 1pt;
vertical-align: middle;
color: #3333FF;
text-decoration: underline;
}
.Titles4 {
height: 38px;
width: 90px;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
text-align: center;
vertical-align: middle;
color: #FF6600;
background-color: #FFFFFF;
background-image: url(images/TableTitle2.jpg);
}
.TopSmall {
height: 38px;
width: 12px;
background-color: #000000;
background-image: url(images/DBTable/TitleRight.jpg);
}
.TopSmall2 {
background-color: #000000;
height: 38px;
width: 11px;
background-image: url(images/DBTable/TitleLeft.jpg);
}
.BoxRightDB {
height: 102px;
width: 10px;
background-image: url(images/DBTable/RightDBtable.jpg);
}
.BOTTOM {
background-image: url(images/DBTable/BOTTOM.jpg);
width: 924px;
}
.TopBar {
background-image: url(images/DBTable/TopBar.jpg);
width: 800px;
height: 43px;
}
.BottomBar {
width: 800px;
height: 43px;
background-image: url(images/DBTable/BottomBarDB.jpg);
}
.PleaseEnter {
background-image: url(images/DBTable/PleaseEnter.jpg);
height: 20px;
width: 800px;
}
.TextBox {
background-image: url(images/DBTable/testBox.jpg);
width: 800px;
background-repeat: no-repeat;
height: 20px;
}
.textbox2 {
background-image: url(images/DBTable/testBox2.jpg);
}
.Form {
background-image: url(images/DBTable/Form.jpg);
}
-->
</style>
</head>

<body bgcolor=&quot;#F0F0F0&quot;>

<div align=&quot;center&quot;>
<table width=&quot;800&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; bgcolor=&quot;#FFFFFF&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;924&quot; height=&quot;30&quot; valign=&quot;top&quot;><table width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; bgcolor=&quot;#FF6600&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;924&quot; height=&quot;30&quot; valign=&quot;top&quot; class=&quot;TopBar&quot;><object classid=&quot;clsid27CDB6E-AE6D-11cf-96B8-444553540000&quot; codebase=&quot;

http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0&quot;

width=&quot;120&quot; height=&quot;39&quot; hspace=&quot;2&quot; vspace=&quot;2&quot;>
<param name=&quot;movie&quot; value=&quot;FLA/HomeLinkFLASH.swf&quot;>
<param name=&quot;quality&quot; value=&quot;high&quot;>
<embed src=&quot;FLA/HomeLinkFLASH.swf&quot; width=&quot;120&quot; height=&quot;39&quot; hspace=&quot;2&quot; vspace=&quot;2&quot; quality=&quot;high&quot; pluginspage=&quot;

http://www.macromedia.com/go/getflashplayer&quot;

type=&quot;application/x-shockwave-flash&quot;></embed>
</object>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td height=&quot;20&quot; valign=&quot;top&quot; bgcolor=&quot;#FFFFFF&quot;><div align=&quot;center&quot;>
<p class=&quot;PleaseEnter&quot;><font color=&quot;#000066&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>Please enter a <font color=&quot;#FF0000&quot;><strong>Surname</strong></font> below and click <font color=&quot;#FF0000&quot;><strong>Search</strong></font></font></p>
</div></td>
</tr>
<tr>
<td height=&quot;24&quot; valign=&quot;top&quot;><table width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;827&quot; height=&quot;24&quot; valign=&quot;top&quot; bgcolor=&quot;#FFFFFF&quot; class=&quot;Form&quot;><form name=&quot;form1&quot; method=&quot;post&quot; action=&quot;&quot;>
<div align=&quot;center&quot;>
<p class=&quot;TextBox&quot;>
<input name=&quot;SearchTXT&quot; type=&quot;text&quot; id=&quot;SearchTXT&quot;>
<input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Search&quot;>
</p>
</div>
</form>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td height=&quot;20&quot; valign=&quot;top&quot; class=&quot;textbox2&quot;><div align=&quot;center&quot;>
<% If Recordset1.EOF And Recordset1.BOF Then %>
<font color=&quot;#FF0000&quot; face=&quot;Arial, Helvetica, sans-serif&quot;></font>
<%Else%>
<font color=&quot;#000066&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>Total Records Found: <%=(Recordset1_total)%></font>
<% End If ' end Recordset1.EOF And Recordset1.BOF %>
</div></td>
</tr>
<tr>
<td height=&quot;38&quot; valign=&quot;top&quot;>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<table width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;5&quot; height=&quot;38&quot; valign=&quot;top&quot; class=&quot;TopSmall2&quot;><!--DWLayoutEmptyCell-->&nbsp;</td>
<td width=&quot;80&quot; align=&quot;center&quot; valign=&quot;top&quot; bgcolor=&quot;#FFFFFF&quot; class=&quot;Titles3&quot;> First
Name</td>
<td width=&quot;90&quot; align=&quot;center&quot; valign=&quot;top&quot; bgcolor=&quot;#FFFFFF&quot; class=&quot;Titles4&quot;>Surname</td>
<td width=&quot;90&quot; align=&quot;center&quot; valign=&quot;top&quot; bgcolor=&quot;#0066FF&quot; class=&quot;Titles&quot;>External Tel. No.</td>
<td width=&quot;87&quot; align=&quot;center&quot; valign=&quot;top&quot; bgcolor=&quot;#FFFFFF&quot; class=&quot;Titles3&quot;>Internal Ext.</td>
<td width=&quot;87&quot; align=&quot;center&quot; valign=&quot;top&quot; bgcolor=&quot;#FFFFFF&quot; class=&quot;Titles&quot;>Department</td>
<td width=&quot;87&quot; align=&quot;center&quot; valign=&quot;top&quot; class=&quot;Titles3&quot;>Department Tel. No.</td>
<td width=&quot;87&quot; align=&quot;center&quot; valign=&quot;top&quot; class=&quot;Titles&quot;>Manager</td>
<td width=&quot;87&quot; align=&quot;center&quot; valign=&quot;top&quot; class=&quot;Titles4&quot;>Email Address</td>
<td width=&quot;100&quot; align=&quot;center&quot; valign=&quot;top&quot; class=&quot;Titles2&quot;>Photo</td>
<td width=&quot;14&quot; valign=&quot;top&quot; class=&quot;TopSmall&quot;><!--DWLayoutEmptyCell-->&nbsp;</td>
</tr>
</table>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
</td>
</tr>
<tr>
<td height=&quot;138&quot; valign=&quot;top&quot;>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<table width=&quot;800&quot; height=&quot;100&quot; border=&quot;0&quot; align=&quot;center&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; cols=&quot;9&quot; bgcolor=&quot;#FFFFFF&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;10&quot; rowspan=&quot;2&quot; valign=&quot;top&quot; bgcolor=&quot;#000000&quot; class=&quot;BoxSmall&quot;><p>&nbsp;</p> <p>&nbsp;</p>
</td>
<td width=&quot;85&quot; height=&quot;102&quot; valign=&quot;top&quot; class=&quot;NewBoxSmaller&quot;><%=(Recordset1.Fields.Item(&quot;First Name&quot.Value)%></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;Box1&quot;><font color=&quot;#0000FF&quot;><%=(Recordset1.Fields.Item(&quot;Surname&quot.Value)%></font></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;Box1&quot;><%=(Recordset1.Fields.Item(&quot;Tel Number&quot.Value)%></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;NewBoxSmaller&quot;><%=(Recordset1.Fields.Item(&quot;Internal Ext Number&quot.Value)%></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;Box1&quot;><%=(Recordset1.Fields.Item(&quot;Department&quot.Value)%></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;NewBoxSmaller&quot;><%=(Recordset1.Fields.Item(&quot;Hunt Group Number&quot.Value)%></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;Box1&quot;><%=(Recordset1.Fields.Item(&quot;Manager&quot.Value)%></td>
<td width=&quot;85&quot; valign=&quot;top&quot; class=&quot;BoxLarger&quot;><a href=&quot;mailto:<%=(Recordset1.Fields.Item(&quot;EMail_Address&quot.Value)%>&quot;>Send Email</a>
</td>
<td width=&quot;100&quot; valign=&quot;top&quot; class=&quot;Photot&quot;><img src=&quot;<%=(Recordset1.Fields.Item(&quot;PhotoLocation&quot.Value)%>&quot; alt=&quot;&quot; name=&quot;Photo&quot; width=&quot;100&quot; height=&quot;100&quot; vspace=&quot;1&quot;></td>
<td width=&quot;10&quot; rowspan=&quot;2&quot; valign=&quot;top&quot; bgcolor=&quot;#000000&quot; class=&quot;BoxRightDB&quot;><p>&nbsp;</p> <p>&nbsp;</p> <p>&nbsp;</p>
</td>
</tr>
<tr>
<td height=&quot;2&quot;></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
</td>
</tr>
<tr>
<td height=&quot;35&quot; valign=&quot;top&quot;>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<table width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;924&quot; height=&quot;35&quot; valign=&quot;top&quot; class=&quot;BOTTOM&quot;><blockquote>
<p align=&quot;center&quot;><a href=&quot;javascript:scroll(0,0)&quot;><font color=&quot;#FF6600&quot; size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;>Back to Top</font></a></p>
</blockquote>
</td>
</tr>
</table>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
</td>
</tr>
<tr>
<td height=&quot;30&quot; valign=&quot;top&quot;><table width=&quot;100%&quot; border=&quot;0&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; bgcolor=&quot;#FF6600&quot; class=&quot;BottomBar&quot;>
<!--DWLayoutTable-->
<tr>
<td width=&quot;924&quot; height=&quot;30&quot; valign=&quot;middle&quot;><div align=&quot;center&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;> <font color=&quot;#FFFFFF&quot;>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<object classid=&quot;clsid27CDB6E-AE6D-11cf-96B8-444553540000&quot; codebase=&quot;

http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0&quot;

width=&quot;40&quot; height=&quot;39&quot; hspace=&quot;2&quot; vspace=&quot;1&quot; align=&quot;left&quot;>
<param name=&quot;movie&quot; value=&quot;FLA/HomeLinkSmall.swf&quot;>
<param name=&quot;quality&quot; value=&quot;high&quot;>
<embed src=&quot;FLA/HomeLinkSmall.swf&quot; width=&quot;40&quot; height=&quot;39&quot; hspace=&quot;2&quot; vspace=&quot;1&quot; align=&quot;left&quot; quality=&quot;high&quot; pluginspage=&quot;

http://www.macromedia.com/go/getflashplayer&quot;

type=&quot;application/x-shockwave-flash&quot;></embed>
</object>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
<br>
If
you have any questions, please</font> <a href=&quot;mailto:%20Brett@economy-power.co.uk&quot;><font color=&quot;#0000FF&quot;>click here</font></a></font></div></td>
</tr>
</table>
</td>
</tr>
</table>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

[color]Damn thats alot of code!

 

[Jusenkyo]

Oops - its ok...

I used your code and adapted it to my SQL statement to look like this:

SELECT TelephoneDetails.[Tel Number], TelephoneDetails.[Internal Ext Number], TelephoneDetails.[Member Of Hunt Group], TelephoneDetails.[Hunt Group Number], Employees.[First Name], Employees.Surname, Employees.EMail_Address, Employees.Department, Employees.[Office Location], Employees.[Responible To], Employees.Manager, Employees.PhotoLocation
FROM TelephoneDetails INNER JOIN Employees ON TelephoneDetails.[E Number] = Employees.[E Number]
WHERE SOUNDEX (Surname) = SOUNDEX('MMColParam') OR Surname like 'MMColParam' + '%' OR SOUNDEX ([First name]) = SOUNDEX('MMColParam') OR [First name] like 'MMColParam' + '%' OR SOUNDEX (Department) = SOUNDEX('MMColParam') OR Department like 'MMColParam' + '%'


And it works fine!!!
Cheers for that anyways!

 

[asppage]

Good!!! May the rest of your day prove to be just as successful!

Don't sweat the small stuff.