The message displays fine and correct. That's the probem - the query returns no data (records) even though there are records in the database.
Main Topics
Browse All TopicsThanks in advance. Not sure why database errors have always been so prevalent with FrontPage BUT...
I went through the steps (See link) for creating a database Query form using an Access Database imbedded within FrontPage 2002.
http://www.outfront.net/sp
When I run a query against KNOWN database content for a given field, no records are returned but the .asp page does return a "no records returned" message in the database results region. Any ideas?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The problem is in your query. Please cut and paste your SQL query (see below). I am interested in the line fp_sQry. What I suspect your problem is you let FrontPage build your query for a specific selection when you probably need to edit it. I see this problem frequently and when you modify the query you get the results you desire. The no records returned indicate the database connection is good and frontpage is actually doing as it is designed but your query cannot find any record matches which returns the no records returned value.
RCMB
<%
fp_sQry="SELECT * FROM Accessories ORDER BY Description ASC, Size ASC"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Demo"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
hhammash - It was, in fact, not verified. However, I just verified it - locked in the verification and tried it again - still no records are returned...
RCMB - I did let FrontPage build the querys for the two search form fields I specified in the wizard. Those two fields (and thier respective queries) are as follows:
Name Field: <%=Request("Name")%>
CDDVDClientJob Field: <%=Request("CDDVDClientJob
Are you suggesting I edit and paste your queries in instead?
Got it:
<%
fp_sQry="SELECT * FROM Folders WHERE (Name = '::Name::' AND CDDVDClientJob = '::CDDVDClientJob::')"
fp_sDefault="Name=&CDDVDCl
fp_sNoRecords="<tr><td colspan=4 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=50
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
Okay - your SQL statement looks fine as long as you are in fact passing text to the asp page and the database is setup as text fields. The single quotes around '::Name::' indicates the SQL statement will take whatever is passed and insert it in between the double colons. If it is a number then remove the single quotes, if it is a date replace the single quotes with #. Just a little info.
On to your problem - I assume you have a form with two boxes that when submit is clicked it goes to the asp page with the results not being displayed. Are the form boxes named Name and CDDVDClientJob respectively. The SQL statement you provided is looking for the information passed from a form or querystring with those names. The way it works is SELECT * FROM Folders WHERE (Name = '::Name::' AND CDDVDClientJob = '::CDDVDClientJob::') means to select everything from the table folders where Name equals the name you pass ('::Name::') and CDDVDClientJob equals the CDDVDClientJob you pass ('::CDDVDClientJob::'). To make sure everything is working remove everything after Folders --
to do this go to HTML view and just above the red font sql statement that you pasted is a grey line with the same information. Scroll about 3/4 of the way over and edit the grey text to remove everything in your SQL statement after Folders. The display looks like "SELECT * From Folders WHERE (Name = '::Name::' AND CDDVDClientJob = '::CDDVDClientJob::')" and I want it to look like "SELECT * From Folders".
Now run the page and you will see everything displayed. This indicates the database and asp page are functioning you just have a problem in passing the "WHERE" information. I suspect the problem is in the form and the form box names.
Long winded but I wanted to make sure you understand. Be careful in editing the HTML view and only delete the WHERE... information leave the remainder of the line in tact.
RCMB
Well first off thanks for so MUCH information. First an update. One of the fields in the Access Datatase was a Date/Time field. I changed it to TEXT so now all three fields are text fields. Also - as the tutorial in link I gave initially prescribes, this is a single page ASP form which contains the two search fields and the database results region all in the same page (didn't know if that matterd).
Question: Are you (still) suggesting I edit this Webbot line above the red SQL text? Just a note that I did that and got a page not found afterwards.
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Name,Mod
Everything below was cut off.......................
s-DefaultFields b-procedure BOTID="0" s-NoRecordsFound i-MaxRecords i-GroupSize u-dblib u-dbrgn1 preview="<tr><td colspan=64 bgcolor="#FFFF00"
I don't think you understood what I was saying. I only wanted the gray SQL portion modified. In your statement "Everything below was cut off" all needs to be put back in its original location or you will get the error you were seeing. Just edit to portion
s-sql="SELECT...."
between the double quotes.
You have it reading correctly so just put the other stuff back where it belongs.
RCMB
Hi,
Are you 100% sure that you have data in your database to match the AND in your SQL. If you are not getting any error message or any data, it means that your SQL is correct but there is not data matching.
It strange becuase your SQL is fine.
RCB would like you to test the connection between the ASP and the database, run SQL without any condition to see if it displays everything. If it does that means the connection is correct but you don't have data to match the AND.
You can go back to the properties of you DRW and don't select any criteria, just list all records.
Besides if your ASP (DRW) page is not put to Spooky Diet then when you save it all your corrections will be disregarded.
Here is the link for the steps on how to put your page on diet if you want to edit the ASP page in HTML view. http://www.outfront.net/sp
Regards
hhammash
rcmb - what I pasted in IS the GRAY SQL Portion - all of the text I pasted in (now below) is Gray in my HTLM view. Here's an idea - make the edits to the code for me and I'll paste it back in. Again, here is the GREY text above the red SQL Statement:
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Name,Mod
hhammash - I am 100% sure I have data in my database. However, you may have revealed a miscommunication on my part. I don't want these queries to find records with "Name" or "CDDVDClientJob" in them - those are column headings. I want to be able to search the database against those columns (Field Names) to return what ever data I query for. Again - this is (supposed to be) a single .asp page with two query fields and a database region intended to return rows or records based on the criteria I feed into the query fields. Apologies if I didn't communicate this clearly.
Okay - to keep from messing things up, lets double click on the top yellow bar ("this is the start of a database results region") in your normal view. This will open the database results wizard (DBRW).
Click next twice and in the 3rd step click on More Options, Criteria and remove anything in there. This will remove all of the "WHERE" statement. Finish the DBRW and then save and run your page. This should display all of the information in the database.
Send me the fields in your database and I will then tell you what to enter in the 3rd step of the DBRW to make the page display the information you desire.
RCMB
Thanks for your patience - sorry for any miscommunication. I must have tried using the wrong query tutorial.
What do you mean by Finish the DBRW? After deleting the queries you mentioned (in step 3) the page no longer loads but I suspect thats pbecause it's expecting queries.
- ID (Unique Key
- Name (Text)
- Modified (Text)
- CDDVDClientJob Text)
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Name</title>
</head>
<body>
<form BOTID="0" METHOD="POST" ACTION="archives.asp">
<table BORDER="0">
<tr>
<td><b>Name</b></td>
<td><input TYPE="TEXT" NAME="Name" VALUE="<%=Request("Name")%
</tr>
<tr>
<td><b>CDDVDClientJob</b><
<td>
<input TYPE="TEXT" NAME="CDDVDClientJob" VALUE="<%=Request("CDDVDCl
</tr>
</table>
<p><br>
<input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP" endspan --></p>
<p> </p>
</form>
<table width="100%" border="1">
<thead>
<tr>
<td><b>ID</b></td>
<td><b>Name</b></td>
<td><b>Modified</b></td>
<td><b>CDDVDClientJob</b><
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Name,Mod
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Folders"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=4 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.in
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="44884"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Folders WHERE (Name = '::Name::' AND CDDVDClientJob = '::CDDVDClientJob::')"
fp_sDefault="Name=&CDDVDCl
fp_sNoRecords="<tr><td colspan=4 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=50
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.in
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="59034" --><tr>
<td>
<!--webbot bot="DatabaseResultColumn"
<td>
<!--webbot bot="DatabaseResultColumn"
<td>
<!--webbot bot="DatabaseResultColumn"
<td>
<!--webbot bot="DatabaseResultColumn"
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00"
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>
</body>
</html>
mmurray46 - here is the code all working correctly. I created a ms access database with the same fields as yours and with the database connection named "database1". I ran the file named "archives.asp" and it worked per design. When you first open the file it will not show any data until you enter a name and CDDVDClientJob. The results must be an exact match of both fields. For example I had test1 as a name and test11 as CDDVDClientJob. If you desire more generic results then I recommend using LIKE instead of EQUALS.
For instance SELECT * FROM Folders WHERE (Name LIKE '::Name::%' AND CDDVDClientJob LIKE '::CDDVDClientJob::%')
If I entered test in the name box and test in the CDDVDClientJob will return
test1
test2
test3
In other words the % at the end indicates "BEGINS WITH"
% at the beginning indicates "ENDS WITH"
and a % at both ends indicated "CONTAINS"
Play with until you find what you desire.
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Name</title>
</head>
<body>
<form BOTID="0" METHOD="POST" ACTION="archives.asp">
<table BORDER="0">
<tr>
<td><b>Name</b></td>
<td><input TYPE="TEXT" NAME="Name" VALUE="<%=Request("Name")%
</tr>
<tr>
<td><b>CDDVDClientJob</b><
<td>
<input TYPE="TEXT" NAME="CDDVDClientJob" VALUE="<%=Request("CDDVDCl
</tr>
</table>
<p><br>
<input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " --></p>
<p> </p>
</form>
<table width="100%" border="1">
<thead>
<tr>
<td><b>ID</b></td>
<td><b>Name</b></td>
<td><b>Modified</b></td>
<td><b>CDDVDClientJob</b><
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Name,Mod
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Folders WHERE (Name = '::Name::' AND CDDVDClientJob = '::CDDVDClientJob::')"
fp_sDefault="Name=&CDDVDCl
fp_sNoRecords="<tr><td colspan=4 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.in
<!--webbot bot="DatabaseRegionStart" endspan i-CheckSum="41917" --><tr>
<td>
<!--webbot bot="DatabaseResultColumn"
<td>
<!--webbot bot="DatabaseResultColumn"
<td>
<!--webbot bot="DatabaseResultColumn"
<td>
<!--webbot bot="DatabaseResultColumn"
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn
<!--webbot bot="DatabaseRegionEnd" endspan i-CheckSum="62730" --></tbody>
</table>
</body>
</html>
OK - We're making progress. I was able to get a query to work using criteria in each field for specific data. However, now when I replace:
"SELECT * FROM Folders WHERE (Name = '::Name::' AND CDDVDClientJob = '::CDDVDClientJob::')"
with
"SELECT * FROM Folders WHERE (Name LIKE '::Name::%' AND CDDVDClientJob LIKE '::CDDVDClientJob::%')
and save it - it automatically reverts back to the original - like it doesn't want to accept the "LIKE" condition...
Also what if I only want to search using one of the fields or the other ot both at the same time? Are we looking at a complete query rewrite?
OK Well we're not quite there yet. The first field, "Name" seems to be working perfectly! It returns results on all or just portions of the data you query.
BUT, the 2d field, "CDDVDClientJob", WILL return results ONLY if you search on a number. For Example: If I search this field (which contains an entry for "1020|Bloomingdales3") for 1020 it brings up all "1020" entries just fine.
However, if I search for "Bloomingdales", "Bloomingdales3", or "|Bloomingdales' I get no results. Is the "|" an illegal character or something?
The problem is where the % sign is. The % at the end ('::CDDVDClientJob::%') means "begins with" so the query searches any thing that begins with "Bloomingdales", "Bloomingdales3", or "|Bloomingdales". As you can see in your database you have nothing that begins with that. It in fact begins with 1020 which is why that works. If you want to perform a "contains" query put the % at both ends ('%::CDDVDClientJob::%') or if you want to do an "ends with" put the % at the beginning ('%::CDDVDClientJob::' - in this case the opposite would occur - 1020 would return nothing).
RCMB
Business Accounts
Answer for Membership
by: hhammashPosted on 2004-04-01 at 12:27:32ID: 10735507
Hi,
Did you remove the message of "Nor records returned" from the box in the options area?
This is strange, it should display that message if it is available in the box.
Regards
hhammash