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 Topics
Loading Advertisement...
Question
FrontPage Database Query Problem
Thanks 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.
Subscribe now for full access to Experts Exchange and get
Instant Access to this Solution
- Plus...
- 30 Day FREE access, no risk, no obligation
- Collaborate with the world's top tech experts
- Unlimited access to our exclusive solution database
- Never be left without tech help again
Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.
- "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
- "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
- "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.
See what Experts Exchange can do for you.
Got a question?
We've got the answer.
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.
Need individual assistance?
Our experts are ready to help.
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.
Want to learn from the best?
Read articles from industry experts.
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.
Working on a long term project?
Store your work and research.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
What Makes Experts Exchange Unique?
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Trusted by the world's most respected brands.
Faithfully serving IT professionals since 1996.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Free Tech Articles
-
WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
-
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
-
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
-
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
-
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
-
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...
Cloud Class Webinars
- Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
- Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
- IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
- Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
- Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
- How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...
Join the Community
Give a Little. Get a Lot.
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.
Answers
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
3 Ways to Join
Business Accounts
- Perfect for organizations
- Multiple users
- Save over 36%
- Create a Business Account
Answer for Membership
- Earn free access
- Showcase your knowledge
- No credit card required
- Sign Up to Answer
Loading Advertisement...
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