Hi Group!

I have a problem. I have a search/display page, up top is the search box
which sent the search information to the same page (this page) to display the result at the bottom. The search function work ok, but when the user enter to this page to do a search, the page pulled up all of the record in the DB and display it at the bottom, only after you do a search then will it display
that specific record you are looking for.

Is there a way to set this page so that when the user enter, it doesn't pull all of the records from the DB and display it at the bottom?

My recordset look like this:
SQL:

SELECT *
FROM Employees
WHERE emp_lname LIKE 'varLName%' AND emp_fname LIKE 'varFName%'
ORDER BY emp_lname

VARIABLES:
Name: Default Value Run-time Value
varLName % Request.Form("emp_lname"
varFName % Request.Form("emp_fname"

I have tried to replace the default value with ZZZ, but when I did that the search won't return any record. If I put back the % then the search work fine, but it will display all of the record in the DB when the user hit that page. Any idea on how to do this?

Thanks in advance!
-Dave

I assume that you display the search results in a table, yes?

You are nearly there... Change your default values to something like zzzxxx so that the record set returns nothing when the page loads.

Then, select your entire table (click the tiny little <table> icon at the very bottom of the screen) and then apply the 'Show Region If Recordset Is Not Empty' behaviour to the table.

This will stop the runtime error when the page first loads.

For a more professional result, underneath your table, add a line like this:

"Sorry - there are no results to display"

Then, select that entire line, and apply the 'Show Region If Recordset Is Empty' behaviour. Then, when there is no match from the search, a friendly message is displayed!

Hope this helps

Mark Wills

The way to do it is not to create the recordset unless the search form is sbmitted. Returning an empty recordset will still execute the search and create a recordset etc. which is inneficient.

Something like:

<% If Request("Submit" <> "" Then %>
<%
COLLECT VARIABLES
%>
<%
CREATE RECORDSET
%>

Modify the form action to incude a URL parameter, something like:

<form name="form1" method="post" action="ThisPage.asp?DisplayResults=1" >

Then in the HTML portion of the page, something like:

<% If Request.QueryString("DisplayResults"= 1 Then %>
<% If NOT RecordsetName.BOF OR NOT RecordsetName.EOF Then %>
YOUR RESULTS TABLE AND DYNAMIC TEXT
<% Else %>
Sorry No Results found.
<% End If %>
<%
RecordsetName.Cose()
End If
%>

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Hi Owen,
Thanks for your response. I'm still very new at this. Could you gave me more detail on how to write and where to put the code.

This is my form:

<form name="emp_search" method="post" action="emp_search.asp">
<table width="250" border="0" cellspacing="1" cellpadding="1">
<tr bgcolor="#FFCC99">
<td height="30" width="80">
<b> First Name:</b></td>
<td height="30">
<input type="text" name="emp_fname" size="15"> </td>
</tr>
<tr bgcolor="#FFCC99">
<td height="30" width="80">
<b> Last Name:</b></td>
<td height="30">
<input type="text" name="emp_lname" size"15"> </td>
</tr>
<tr bgcolor="#FFCC99">
<td height="50" colspan="2" bgcolor="#FFCC99">
<input type="submit" name="Submit" value="Search">
</td>
</tr>
</table>
</form>

This my display code:
<table>
<tr>
<td bgcolor="#CCCCCC"><b><font size="3">Name</font></b></td>
<td bgcolor="#CCCCCC"><b><font size="3">Site</font></b></td>
<td bgcolor="#CCCCCC"><b><font size="3">Phone # </font></b></td>
<td bgcolor="#CCCCCC"><b>Ext.</b></td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT rsDisplay.EOF)) %>
<tr>
<td><%=(rsDisplay.Fields.Item ("emp_lname".Value)%>, 
<%=(rsDisplay.Fields.Item("emp_fname".Value)%></td>
<td><%=(rsDisplay.Fields.Item("emp_site".Value)%></td>
<td><%=(rsDisplay.Fields.Item("emp_phone".Value)%></td>
<td><%=(rsDisplay.Fields.Item("emp_ext".Value)%></td>
</tr>
<%
Repeatt1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsDisplay.MoveNext()
Wend
%>
<tr>
<% If rsDisplay.EOF And rsDisplay.BOF Then %>
<td colspan="5">
<br>NO RECORD FOUND</b><br>
</td>
<% End If ' end rsDisplay.EOF And rsDisplay.BOF %>
</tr>
</table>

Thanks
-Dave

On the emp_search.asp page:

At the top of the page just below
<!--#include file="YourConnection.asp" -->

<% If Request("Submit" <> "" Then %>
varFname = Replace(Request("emp_fname", "'", "''"
varLname = Replace(Request("emp_lname", "'", "''"

' if first and last name
If varFname <> "" AND varLname <> "" Then
SQLstring = "SELECT emp_lname, emp_fname, emp_site, emp_phone, emp_ext FROM TableName WHERE emp_Lname LIKE '%" & varLname & "%' AND emp_Fname LIKE '%" & varFname & "%'"
End If

' if only first name
If varFname <> "" AND varLname = "" Then
SQLstring = "SELECT emp_lname, emp_fname, emp_site, emp_phone, emp_ext FROM TableName WHERE emp_Fname LIKE '%" & varFname & "%'"
End If

' If only last name
If varFname = "" AND varLname <> "" Then
SQLstring = "SELECT emp_lname, emp_fname, emp_site, emp_phone, emp_ext FROM TableName WHERE emp_Lname LIKE '%" & varLname & "%'"
End If

' If nothing is entered return all results
If varFname = "" AND varLname = "" Then
SQLstring = "SELECT emp_lname, emp_fname, emp_site, emp_phone, emp_ext FROM TableName"
End If
%>
<%
set rsDisplay = Server.CreateObject("ADODB.Recordset" rsDisplay.ActiveConnection = MM_YourConnection_STRING
rsDisplay.Source = SQLstring
rsDisplay.CursorType = 0
rsDisplay.CursorLocation = 2
rsDisplay.LockType = 3
rsDisplay.Open()
rsDisplay_numRows = 0
%>
<% End If ' Form Submitted %>

The Form:

<form name="emp_search" method="post" action="emp_search.asp?Display=1">
<table width="250" border="0" cellspacing="1" cellpadding="1">
<tr bgcolor="#FFCC99">
<td height="30" width="80">
<b> First Name:</b></td>
<td height="30">
<input type="text" name="emp_fname" size="15"> </td>
</tr>
<tr bgcolor="#FFCC99">
<td height="30" width="80">
<b> Last Name:</b></td>
<td height="30">
<input type="text" name="emp_lname" size"15"> </td>
</tr>
<tr bgcolor="#FFCC99">
<td height="50" colspan="2" bgcolor="#FFCC99">
<input type="submit" name="Submit" value="Search">
</td>
</tr>
</table>
</form>

The Display Code:

<% If Request.QueryString("Display"= 1 Then %>
<% If NOT rsDisplay.BOF OR NOT rsDisplay.EOF Then %>
<table>
<tr>
<td bgcolor="#CCCCCC"><b><font size="3">Name</font></b></td>
<td bgcolor="#CCCCCC"><b><font size="3">Site</font></b></td>
<td bgcolor="#CCCCCC"><b><font size="3">Phone # </font></b></td>
<td bgcolor="#CCCCCC"><b>Ext.</b></td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT rsDisplay.EOF)) %>
<tr>
<td><%=(rsDisplay.Fields.Item ("emp_lname".Value)%>,
<%=(rsDisplay.Fields.Item("emp_fname".Value)%></td>
<td><%=(rsDisplay.Fields.Item("emp_site".Value)%></td>
<td><%=(rsDisplay.Fields.Item("emp_phone".Value)%></td>
<td><%=(rsDisplay.Fields.Item("emp_ext".Value)%></td>
</tr>
<%
Repeatt1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsDisplay.MoveNext()
Wend
%>
<tr>
<% Else %>
<td colspan="5">
<br>NO RECORD FOUND</b><br>
</td>
<% End If ' end rsDisplay.EOF And rsDisplay.BOF %>
</tr>
</table>
<%
rsDisplay.Cose()
End If ' End if Display = 1
%>

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thanks!