| In
order for your data to be
selected in the proper order
from your database you need to
use a "query".
Alot of people get pretty hung
up about "querying their
recordsets" and all; but it
can be pretty straightforward if
you follow the protocol
FrontPage is looking for.
The
default SQL query generated by FrontPage when creating a
database results areas on a
webpage is:
SELECT * FROM
YourTableName
The
star means
"everything".
This can work OK but is a
general query and can also
return many unwanted results.
In
many cases what you are trying
to do is select only 1 result
from the database based on your
"query", or
question. An example of
this would be for our www.mydomain.com/display.asp?name=John
pal John.
You
know he only has one record and
you want to query the database
and see only HIS information;
not everyone who has a record in
the name column.
Try
this:
Use
the following SQL statement in
your "custom query"
box.
SELECT * FROM Results WHERE
(FieldName LIKE '%::FieldName::%')
And
in the database
"options" tab make
sure you select the number of
records to be displayed to
"limit to 1" (the
default is 256).
This
page will then display only the
records associated with that
exact record.
If
you want to show more than 1
record, like in our example
http://www.meetnewplayers.com/bend/Listings/US/musiciansearch.asp?MusUSState=California
where
you are showing all records with
the value "California"
in the MusUSState column; leave
the maximum number of records to
be displayed to 256 and then on
the last panel of the database
wizard divide the records into
sets of "....5, 25,
etc. This way the first 5
records are shown, you click
"next" and see the
next 5. Very cool.
If
you get proficient with using
the "*" results you
may want to sort by a certain
column; example Timestamp.
Tip:
Simply generate a
Frontpage results area below the
one you are working on, go
through the wizard choosing your
sort method, ascending, etc and
once completed go back and view
the SQL query FrontPage
generated. Incorporate
this into your code and it will
sort as requested since is
generated in a format recognized
by the editor. Great
time-saver when generating more
complex queries.
Another
important character to know
about is the
"ampersand"
&
This
character joins 2 or more
columns when adding search
criteria. This
"refines" your
results; adding more data to
search within your original
query. Here's an example:
http://www.meetnewplayers.com/bend/Listings/US/musiciansearch.asp?MusUSState=California&name=John
You
would then get all records from
California with the John in the
name column.
By
using the Database results
wizard you are letting FrontPage
do all of the heavy coding
behind the scenes. Things
like
- Number
of records returned
- Which
fields to return
- If
there are any tables, labels
or fields for each result
are all handled for
you. Once again very
cool and better than Linux
land where you can spend the
whole weekend looking at a
notepad window trying to
make your tables line up.
The
other time you would use SQL
statements is to Update or
Delete records.
Here's our page page
explaining how to update and delete records in asp and FrontPage/ExpressionWeb
|
