Access db search result behaviour

Hi all.

The following search was built with frontpage 2002 to query an
Access table. At the moment it insists on a value in both
fields or returns an error. How do I write this so an answer
in either one or none or all fields produces an answer.
i.e. if no fields of the search form are completed the entire
table is the result, and if one or more fields are completed
it simply 'narrows' the search?

<td><b>AGE</b></td>
<td><input TYPE="TEXT" NAME="AGE" VALUE="<%=Request("AGE")%>"
size="20"></td>
</tr>
<td><b>CITY</b></td>
<td><input TYPE="TEXT" NAME="CITY" VALUE="<%=Request("CITY")%>"
size="20"></td>

These are only two of the fields here for illustration but there
are more in the search

Many thanks for any pointers

"Richard Rosser" <setonendell [at] ntlworld.com> wrote in message
news:5cBHg.13390$_06.2806 [at] newsfe4-gui.ntli.net...
> Hi all.
>
> The following search was built with frontpage 2002 to query an
> Access table. At the moment it insists on a value in both
> fields or returns an error. How do I write this so an answer
> in either one or none or all fields produces an answer.
> i.e. if no fields of the search form are completed the entire
> table is the result, and if one or more fields are completed
> it simply 'narrows' the search?
>
> <td><b>AGE</b></td>
> <td><input TYPE="TEXT" NAME="AGE" VALUE="<%=Request("AGE")%>"
> size="20"></td>
> </tr>
> <td><b>CITY</b></td>
> <td><input TYPE="TEXT" NAME="CITY" VALUE="<%=Request("CITY")%>"
> size="20"></td>
>
> These are only two of the fields here for illustration but there
> are more in the search

How does "it insist"? Show us that code.

I'm not familiar with "Request()"; I have heard of "Request.Cookies()"
"Request.Form()", "Request.QueryString()", Request.ServerVariables()".

URL:http://www.w3schools.com/asp/asp_ref_request.asp

URL:http://www.netzone.ch/caspdoc/Ch9_ASPBuiltIn30.html

"McKirahan" <News [at] McKirahan.com> wrote in message
news:vcmdnd5Wyft-e3PZnZ2dnUVZ_oqdnZ2d [at] comcast.com...
> "Richard Rosser" <setonendell [at] ntlworld.com> wrote in message
> news:5cBHg.13390$_06.2806 [at] newsfe4-gui.ntli.net...
> > Hi all.
> >
> > The following search was built with frontpage 2002 to query an
> > Access table. At the moment it insists on a value in both
> > fields or returns an error. How do I write this so an answer
> > in either one or none or all fields produces an answer.
> > i.e. if no fields of the search form are completed the entire
> > table is the result, and if one or more fields are completed
> > it simply 'narrows' the search?
> >
> > <td><b>AGE</b></td>
> > <td><input TYPE="TEXT" NAME="AGE" VALUE="<%=Request("AGE")%>"
> > size="20"></td>
> > </tr>
> > <td><b>CITY</b></td>
> > <td><input TYPE="TEXT" NAME="CITY" VALUE="<%=Request("CITY")%>"
> > size="20"></td>
> >
> > These are only two of the fields here for illustration but there
> > are more in the search
>
> How does "it insist"? Show us that code.
>
> I'm not familiar with "Request()"; I have heard of "Request.Cookies()"
> "Request.Form()", "Request.QueryString()", Request.ServerVariables()".

It is the standard search script code written by MS FrontPage when you
generate a page containing DB Table results from an Access Table.
The whole scrit is very long - do you want to see the entire thing?

<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>New Page 1</title>
</head>

<body>

<form BOTID="0" METHOD="POST" ACTION="new_page_1.htm">
<table BORDER="0">
<tr>
<td><b>CITY</b></td>
<td><input TYPE="TEXT" NAME="CITY" VALUE="<%=Request("CITY")%>"
size="20"></td>
</tr>
<tr>
<td><b>AGE</b></td>
<td><input TYPE="TEXT" NAME="AGE" VALUE="<%=Request("AGE")%>"
size="20"></td>
</tr>
</table>
<p><br>
<input TYPE="Submit"><input TYPE="Reset"></p>
<p> </p>
</form>
<table width="100%">
<tbody>

<% 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 Females WHERE (CITY = '::CITY::' AND AGE >
::AGE::)"
fp_sDefault="CITY=&AGE="
fp_sNoRecords="<tr><td colspan=14 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=14
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"USER")%></td>
<td>
<%=FP_FieldVal(fp_rs,"AGE")%></td>
<td>
<%=FP_FieldVal(fp_rs,"CITY")%></td>
<td>
<%=FP_FieldVal(fp_rs,"DOMICILE")%></td>
<td>
<%=FP_FieldVal(fp_rs,"FIRST NAME")%></td>
<td>
<%=FP_FieldVal(fp_rs,"HEIGHT")%></td>
<td>
<%=FP_FieldVal(fp_rs,"WEIGHT")%></td>
<td>
<%=FP_FieldVal(fp_rs,"CHILDREN")%></td>
<td>
<%=FP_FieldVal(fp_rs,"PREVIOUS M")%></td>
<td>
<%=FP_FieldVal(fp_rs,"AGE MIN")%></td>
<td>
<%=FP_FieldVal(fp_rs,"AGE MAX")%></td>
<td>
<%=FP_FieldVal(fp_rs,"SMOKES")%></td>
<td>
<%=FP_FieldVal(fp_rs,"DRINKS")%></td>
<td>
<%=FP_FieldVal(fp_rs,"URL")%></td>
</tr>

</tbody>
</table>

</body>

</html>

"Richard Rosser" <setonendell [at] ntlworld.com> wrote in message
news:7eCHg.13395$_06.3625 [at] newsfe4-gui.ntli.net...

[snip]

> It is the standard search script code written by MS FrontPage when you
> generate a page containing DB Table results from an Access Table.
> The whole scrit is very long - do you want to see the entire thing?

I'd recommend a Frontpage newsgroup such as microsoft.public.frontpage.

Richard wrote on Fri, 25 Aug 2006 12:45:22 GMT:

> <!--webbot bot="DatabaseRegionStart"
> s-columnnames="ID,USER,AGE,CITY,DOMICILE,FIRST
> NAME,HEIGHT,WEIGHT,CHILDREN,PREVIOUS M,AGE MIN,AGE MAX,SMOKES,DRINKS,URL"

This is bad, FrontPage reveals your table definitions for all the world to
see. While in itself not a security issue, if you had some code that allowed
the SQL to be built on the fly (see further down) then this information can
be used to get more data out of your system without trial and error.

> s-recordsource="Females"

and here's the table name visible to the public.

> fp_sQry="SELECT * FROM Females WHERE (CITY = '::CITY::' AND AGE >
> ::AGE::)"

Well here's your problem - FP is forcing both the CITY and AGE values to be
required.

Hopefully the FP database bot has some kind of data validation, if it just
passes values straight in then this sort of thing would be bad:

CITY=anywhere&AGE=10%29;DELETE+FROM+Females+WHERE+%281%3D1

which could result in the following query running:

SELECT * FROM Females WHERE (CITY = 'anywhere' AND AGE > 10);DELETE FROM
Females WHERE 1=1)

As you can see, that's potentially devastating.

This entire thing should be written from scratch using a stored procedure
and passing values in by parameter. FrontPage does not create easily
maintainable code, and reveals way too much information to the public.

Dan

"McKirahan" <News [at] McKirahan.com> wrote in message
news:vcmdnd5Wyft-e3PZnZ2dnUVZ_oqdnZ2d [at] comcast.com...
> "Richard Rosser" <setonendell [at] ntlworld.com> wrote in message
> news:5cBHg.13390$_06.2806 [at] newsfe4-gui.ntli.net...
>> Hi all.
>>
>> The following search was built with frontpage 2002 to query an
>> Access table. At the moment it insists on a value in both
>> fields or returns an error. How do I write this so an answer
>> in either one or none or all fields produces an answer.
>> i.e. if no fields of the search form are completed the entire
>> table is the result, and if one or more fields are completed
>> it simply 'narrows' the search?
>>
>> <td><b>AGE</b></td>
>> <td><input TYPE="TEXT" NAME="AGE" VALUE="<%=Request("AGE")%>"
>> size="20"></td>
>> </tr>
>> <td><b>CITY</b></td>
>> <td><input TYPE="TEXT" NAME="CITY" VALUE="<%=Request("CITY")%>"
>> size="20"></td>
>>
>> These are only two of the fields here for illustration but there
>> are more in the search
>
> How does "it insist"? Show us that code.
>
> I'm not familiar with "Request()"; I have heard of "Request.Cookies()"
> "Request.Form()", "Request.QueryString()", Request.ServerVariables()".
>
> URL:http://www.w3schools.com/asp/asp_ref_request.asp
>
> URL:http://www.netzone.ch/caspdoc/Ch9_ASPBuiltIn30.html
>
>

Request.Form is the default. so Request() does the same thing

"Jimmy" <j [at] j.j> wrote in message
news:eKuMOZo1GHA.4388 [at] TK2MSFTNGP03.phx.gbl...
>

[snip]

> > I'm not familiar with "Request()"
>
> Request.Form is the default. so Request() does the same thing

That may be but it's bad practice -- IMO.

Also:

"If a variable with the same name exists in more than one collection,
the Request object returns the first instance that the object encounters."

Jimmy wrote:
> "McKirahan" <News [at] McKirahan.com> wrote in message
> news:vcmdnd5Wyft-e3PZnZ2dnUVZ_oqdnZ2d [at] comcast.com...
> > "Richard Rosser" <setonendell [at] ntlworld.com> wrote in message
> > news:5cBHg.13390$_06.2806 [at] newsfe4-gui.ntli.net...
> >> Hi all.
> >>
> >> The following search was built with frontpage 2002 to query an
> >> Access table. At the moment it insists on a value in both
> >> fields or returns an error. How do I write this so an answer
> >> in either one or none or all fields produces an answer.
> >> i.e. if no fields of the search form are completed the entire
> >> table is the result, and if one or more fields are completed
> >> it simply 'narrows' the search?
> >>
> >> <td><b>AGE</b></td>
> >> <td><input TYPE="TEXT" NAME="AGE" VALUE="<%=Request("AGE")%>"
> >> size="20"></td>
> >> </tr>
> >> <td><b>CITY</b></td>
> >> <td><input TYPE="TEXT" NAME="CITY" VALUE="<%=Request("CITY")%>"
> >> size="20"></td>
> >>
> >> These are only two of the fields here for illustration but there
> >> are more in the search
> >
> > How does "it insist"? Show us that code.
> >
> > I'm not familiar with "Request()"; I have heard of "Request.Cookies()"
> > "Request.Form()", "Request.QueryString()", Request.ServerVariables()".
> >
> > URL:http://www.w3schools.com/asp/asp_ref_request.asp
> >
> > URL:http://www.netzone.ch/caspdoc/Ch9_ASPBuiltIn30.html
> >
> >
>
> Request.Form is the default. so Request() does the same thing

You miss the point. You shouldn't use Request() without qualifying the
collection you are referring to. It's sloppy, and potentially very
inefficient.

--
Mike Brind

Access db search result behaviour

Re: Access db search result behaviour

Re: Access db search result behaviour

Here is the entire code

Re: Access db search result behaviour

Re: Here is the entire code

Re: Access db search result behaviour

Re: Access db search result behaviour

Re: Access db search result behaviour