Solved

FrontPage Database Query Problem

Posted on 2004-04-01
28
464 Views
Last Modified: 2013-12-24
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/spooky/search_form.htm

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?
0
Comment
Question by:mmurray46
  • 12
  • 11
  • 5
28 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 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
0
 

Author Comment

by:mmurray46
ID: 10735609
The message displays fine and correct.  That's the probem  - the query returns no data (records) even though there are records in the database.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10736114
Hi,

Are you sure that the database is verified in the Tools>WebSettings>Databases?

Regards
hhammash
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10739966
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
%>
0
 

Author Comment

by:mmurray46
ID: 10742702
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?


0
 
LVL 14

Expert Comment

by:hhammash
ID: 10744677
Hi mmurray46,

We need you to post the SQL statement that is in your frontpage.

Either go to HTML view and copy the line
fp_sQry="SELECT * FROM ......etc"

Or open the DRW and clic Custom button and copy the SQL from there?

Maybe your SQL is OK syntatically but will not retrieve anything.

Thanks
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10744680
Sorry for the typo,  I mean syntactically

0
 

Author Comment

by:mmurray46
ID: 10744854
Got it:

<%
fp_sQry="SELECT * FROM Folders WHERE (Name =  '::Name::' AND CDDVDClientJob =  '::CDDVDClientJob::')"
fp_sDefault="Name=&CDDVDClientJob="
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
%>
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10745455
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
0
 

Author Comment

by:mmurray46
ID: 10747906
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,Modified,CDDVDClientJob" s-columntypes="3,202,135,202" s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="ID" s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource="Folders" s-displaycolumns="ID,Name,Modified,CDDVDClientJob" s-criteria="[Name] EQ {Name} + [CDDVDClientJob] EQ {CDDVDClientJob} +" s-order s-sql="SELECT * FROM Folders"

Everything below was cut off.............................

 s-DefaultFields b-procedure BOTID="0" s-NoRecordsFound i-MaxRecords i-GroupSize u-dblib u-dbrgn1 preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan--><!--#include file="_fpclass/fpdblib.inc"-->



0
 
LVL 12

Expert Comment

by:rcmb
ID: 10748006
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
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10748786
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/spooky/adv_drw.htm


Regards
hhammash
0
 

Author Comment

by:mmurray46
ID: 10749899
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,Modified,CDDVDClientJob" s-columntypes="3,202,135,202" s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="ID" s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource="Folders" s-displaycolumns="ID,Name,Modified,CDDVDClientJob" s-criteria="[Name] EQ {Name} + [CDDVDClientJob] EQ {CDDVDClientJob} +" s-order s-sql="SELECT * FROM Folders" s-DefaultFields b-procedure BOTID="0" s-NoRecordsFound i-MaxRecords i-GroupSize u-dblib u-dbrgn1 preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan--><!--#include file="_fpclass/fpdblib.inc"-->

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.



0
 
LVL 12

Expert Comment

by:rcmb
ID: 10750264
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:mmurray46
ID: 10750445
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)
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10750542
To finish the DBRW just select the defaults in steps 4 and 5 to finish the wizard.

Do me a favor and switch to HTML view and copy and paste the entire page here. I know your database now and I want to see your code. I will update and let you know what I find wrong (or where the problem is).

RCMB
0
 

Author Comment

by:mmurray46
ID: 10754326
<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<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")%>" size="20"></td>
    </tr>
    <tr>
      <td><b>CDDVDClientJob</b></td>
      <td>
      <input TYPE="TEXT" NAME="CDDVDClientJob" VALUE="<%=Request("CDDVDClientJob")%>" size="20"></td>
    </tr>
  </table>
  <p><br>
  <input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP" endspan --></p>
  <p>&nbsp;</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></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-columntypes="3,202,202,202" s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="ID" s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource="Folders" s-displaycolumns="ID,Name,Modified,CDDVDClientJob" s-criteria="" s-order s-sql="SELECT * FROM Folders" s-DefaultFields b-procedure="FALSE" BOTID="0" s-NoRecordsFound="No records returned." i-MaxRecords="256" i-GroupSize="0" u-dblib u-dbrgn1 preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan b-WasTableFormat="TRUE"--><!--#include file="_fpclass/fpdblib.inc"-->
<% 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.inc"-->
<!--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=&CDDVDClientJob="
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.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="59034" --><tr>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="ID" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"ID")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="62813" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="Name" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Name")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="3900" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="Modified" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Modified&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Modified")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="16236" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="CDDVDClientJob" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CDDVDClientJob&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CDDVDClientJob")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="36465" --></td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="" i-groupsize="0" clientside tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>

</body>

</html>
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10755610
mmurray46,

Is the file an .asp page - based on the information you gave me it is not and it must be

"...The page must have a filename ending in '.asp'"

rename the file to .asp vice .htm

Are you running the file on a webserver?

RCMB
0
 
LVL 12

Accepted Solution

by:
rcmb earned 250 total points
ID: 10757273
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.Document">
<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")%>" size="20"></td>
    </tr>
    <tr>
      <td><b>CDDVDClientJob</b></td>
      <td>
      <input TYPE="TEXT" NAME="CDDVDClientJob" VALUE="<%=Request("CDDVDClientJob")%>" size="20"></td>
    </tr>
  </table>
  <p><br>
  <input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " --></p>
  <p>&nbsp;</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></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-columntypes="3,202,135,202" s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource="Folders" s-displaycolumns="ID,Name,Modified,CDDVDClientJob" s-criteria="[Name] EQ {Name} + [CDDVDClientJob] EQ {CDDVDClientJob} +" s-order s-sql="SELECT * FROM Folders WHERE (Name =  '::Name::' AND CDDVDClientJob =  '::CDDVDClientJob::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="Name=&amp;CDDVDClientJob=" s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% 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=&CDDVDClientJob="
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.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-CheckSum="41917" --><tr>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="ID" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"ID")%><!--webbot bot="DatabaseResultColumn" endspan i-CheckSum="62813" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="Name" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Name")%><!--webbot bot="DatabaseResultColumn" endspan i-CheckSum="3900" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="Modified" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Modified&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"Modified")%><!--webbot bot="DatabaseResultColumn" endspan i-CheckSum="16236" --></td>
      <td>
      <!--webbot bot="DatabaseResultColumn" s-columnnames="ID,Name,Modified,CDDVDClientJob" s-column="CDDVDClientJob" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;CDDVDClientJob&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" startspan --><%=FP_FieldVal(fp_rs,"CDDVDClientJob")%><!--webbot bot="DatabaseResultColumn" endspan i-CheckSum="36465" --></td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-CheckSum="62730" --></tbody>
</table>

</body>

</html>
0
 

Author Comment

by:mmurray46
ID: 10757875
OK: is this code edited or changed or is it the same as what I sent earlier?
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10758779
It was edited to put the WHERE statements back in and ensure all was working per design. Just copy the code from <html> to </html> to replace all of your code.

RCMB
0
 

Author Comment

by:mmurray46
ID: 10759807
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?

0
 

Author Comment

by:mmurray46
ID: 10759917
GOT IT!!!  

I entered the statement into the Red SQL statement area instead of the gray.  Tried the gray and whala!  THANK YOU VERY MUCH!  So in hindsight, it was probably working all the long - it was just that I wasn't playing by the rules for criteria I had set up through the wizard.  Duh!  
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10760739
The nice thing about the like statement is that if you enter data in one of the fields the other does not require an entry to get a return. If you have LIKE on both fields and just hit submit then it will return all records in the database.

RCMB
0
 

Author Comment

by:mmurray46
ID: 10761127
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?

0
 
LVL 12

Expert Comment

by:rcmb
ID: 10761215
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
0
 

Author Comment

by:mmurray46
ID: 10761282
That did it!  I guess I can stop bothering you now.  Can't thank you enough.
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10761433
My pleasure - glad to get this done and hopefully you learned a little bit and can enjoy FrontPage even more once you start getting into the depths of ASP.

Curtis

RCMB
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now