| 8.0 |
The
Results Page.
|
| 8.1 |
Open the results.asp
page.
|
| 8.2 |
Now we are ready to attach
a recordset, however this recordset is not going to use a Table
but a Query, so we first need to create our Query in Access.
|
| 8.3 |
Open the TDSFdemo.mdb
file in Access, in the Database window, click on Queries in
the Objects panel, then double click on the Create query in
Design view. You will see the some renamed copies of the Queries
you are about to create, which enable the original copy of the
demo site to function. You can refer to these if necessary.
|
| 8.4 |
Now you will see the Query
designer panel.
|
| 8.5 |
Now we need to add the database
Tables from which we wish to select our query fields to the
query designer panel. Double click on Albums
and TrackListings then
close the Show Table dialogue box. Once you've done this, the
Query designer window should look something like this.
A Note about the Query
Designer window and Relationships
You will notice that sometimes when you add tables to
the query design panel that the tables are not set out as they
appear in the illustrations. Try clicking on a table in the
Query design panel, hold down the mouse button and drag it around
the screen. You can rearrange the layout in the panel any way
you wish. The Layout of the tables does not affect the query
you build. For the illustrations I have set them out to show
the relationships between the tables as clearly as possible,
when Access places the tables in the window, it is not always
quite so tidy.
It is worth noting that in the TDSFdemo.mdb database the relationships
between the tables have already been established. If the relationships
had not been predefined the lines between the tables indicating
a relationship would not appear. You can however manually create
relationships between tables in the Query dsign panel.
Right click on one the line between AlbumID in the Albums table
and AlbumID in the TrackListings table, select delete, the relationship
is now removed. Note however that this change applies to this
query alone and will not affect the predefined relationship
already established. To re-establish the relationship between
the two tables click on AlbumID in the TrackListings table hold
down the mouse button and drag to AlbumID in the Albums table,
the line is replaced indicating the existence of a relationship.
The process of adding tables and dragging fields from one table
to a corresponding field in another table is essentially how
relationships between tables is established in the relationships
window. The difference is that relationships established in
the relationships window are permanent and have a global effect
on the database. This is why when the tables were added to the
Query designer the relationships were already established.
If you wish to take a look at the relationships window click
on Tools on main access toolbar and select Relationships from
the dropdown menu you should now see the relationships window
as illustrated in Section 4.0 of this document.
|
| 8.6 |
Now we have our tables available
in the Query design panel we can select the fields we wish to
include in our Query from the tables. There are three ways you
can do this you can either double click on the field name in
the table and it will be placed in the next clear column in
the bottom panel of the query designer panel You can drag the
field from the table to the top row of a column or you can click
in the top three rows of a column and select the relevant item
from the drop down boxes.
For now, double click on AlbumID
in the Albums table, you should see it appear in the first column
in the lower half of the Query design panel.
The bottom portion of the Query design window should look like
this.
|
| 8.7 |
For the second column we
are going to use the Expression Builder to create the values,
rather than taking them directly from a table.
Right click in the top row of the second column
|
| 8.8 |
Select Build from the pop
up menu.
This will open the Expression Builder panel.
In the lower left pane you will see an Explorer style collection
of folders, double click on Tables,
then on Albums from the
list that drops down, you will now see the Fields in the Albums
table appear in the centre pane.
|
| 8.9 |
Double click on ArtistName
in the centre pane, you will see [Albums]![ArtistName]
appears in the top pane. Click next to the text in the top pane
and type &' '&
(ampersand - single quote - space - single quote - ampersand)
so that we now have [Albums]![ArtistName]
&' '& now double click on AlbumTitle.
Type &' '& again
in the top pane, then double click on the TrackListings
folder in the lower left pane. The Fields in the TrackListings
table will now appear in the centre pane, double click on Track,
this will now be added to the top pane. If everything has gone
to plan the Expression builder panel should now look like this.
Click OK to close the Expression Builder window.
|
| 8.10 |
In the Query Desinger panel
widen the column to see the expression you have just created.
It will be named Expr1
Highlight Expr1 and rename it CombinedFields.
|
| 8.11 |
Click run on the Main toolbar
in the Access windows, it looks like a red exclamation mark.
You should now see the results of the Query displayed as a table,
this is the Datasheet View of the Query you have just created
in Design View.
Essentially, what we have done is given Access the instructions
that enable it to dynamically produce an output table from,
in this case, two input tables. The CombinedFields column contains
the ArtistName field from the Albums table then a space which
we created with the &' '& then the AlbumTitle field
from the Albums table another space and then the Track field
from the Tracks table. The two parent tables both had an AlbumID
field, the query matched these together according to the relationship.
There was only one entry in the Albums Table but many entries
in the Tracks table because each album in the database has several
tracks. So the query produces one table row for every album
track in the Tracks table so there are as many rows with AlbumID
1 as there are tracks on the album for which the ID is 1.
|
| 8.12 |
Now click on the drop down
arrow at the very left of the Main toolbar in the Access Window
and select SQL View from the drop down list.
The Datasheet View will now change to the SQL View
This shows the SQL SELECT statement that Access uses to find
the tables and fields you have specified and build the output
table.
If you are proficient with SQL or Structured Query Language
you can build Queries by typing SQL directly into the SQL
View window. The Design View is simply a visual, drag and
drop interface you can use, Access writes the SQL (Structured
Query Language) code for you.
|
| 8.13 |
Close the Query, an alert
message will appear asking Do you want to save the changes to
the design of query 'Query1'? Click Yes and save the Query as
QryCombinedFields.
SQL Queries can be created and saved within the Database as
we have just done. Queries Stored within the Database are referred
to as Views these can then be utilised by other Queries or external
applications in the same manner as database tables. |
| 8.14 |
Now we are going to create
another Query, double click Create Query in design view as we
did in section 7.3. Add the following tables to the Query Design
window by double clicking on them in the Show Table dialogue
box:
Albums,
AlbumFormats and Genres.
The top of the Query Designer window should now look like this:
|
| 8.15 |
Now double click the following
Fields in the tables to add them to the columns below.
AlbumID,
ArtistName, AlbumTitle, and GenreID
in the Albums Table.
Genre in the Genres
Table
FormatID in
the AlbumFormats Table.
The lower half of the Query Designer window should now look
like this:
|
| 8.16 |
Close the Query Designer
window and save the Query as QryDisplayFields.
We are now ready to the use Queries on the results page.
|
| 8.17 |
If it is not already running
start Ultrdev and as in section 6.2 use the Data Bindings palette
to Add a new recordset, once the Recordset options palette opens
click the advanced button.
|
| 8.18 |
Name the Recordset rsQryCombinedFields
and select TDSFdemoOLE
from the Connection drop down list. At the bottom of the Recordset
options palette you can see a window labelled Database Items,
click on the plus symbol next to views, You should see the two
Queries or Views created in Access earlier. Click the plus symbol
next to QryCombinedFields,
you should now see the two column names AlbumID
and CombinedFields. Click
on AlbumID to highlight
it, then click on the SELECT button to the right of the window.
You should now see the SQL select statement in the SQL window
above.
|
| 8.19 |
Click the Test button, you
should see the a list of AlbumID's, however there are several
records for each ID, because there are as many records with
ID 1 as there are tracks on album ID1. If you click the Next
25 button repeatedly until you get to the end of the recordset
you should find that there are in fact 307 records. The CombinedFields
column is where we are going to look for matches with the text
the user enters in the text field of the search page. However
we only want to find the relevant AlbumID from this recordset,
therefore we only require a single ID regardless of how many
matches were found within the text relating to a particular
AlbumID.
|
| 8.20 |
Close the Test SQL Statement
window, in the SQL window of the Recordset options palette type
the word DISTINCT between SELECT and AbumID to modify the SQL
select statement as follows:
SELECT DISTINCT AlbumID
FROM QryCombinedFields
Click the Test button again, there should now be just 25 records.
The DISTINCT clause returns only those rows that are unique.
If we had included the CombinedFields column as well as the
AlbumID column in the SELECT statement then we would once again
have 307 records as all the rows would be unique.
Click OK in the Recordset options palette to close it.
|
| 8.21 |
Now Click the plus button
on the Data Bindings palette to add another recordset.
|
| 8.22 |
As before use the Advanced
Recordset options palette. In the Database Items window expand
the Views to see your Queries then expand QryDisplayFields.
highlight AlbumID then click the SELECT button to the right.
Then highlight ArtistName,
AlbumTitle and Genre
and click the SELECT button for each in turn, you should see
the Select statement as follows in the SQL window.
SELECT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
|
| 8.23 |
Click the Test button, 70
records are returned, some of the rows are repeated. This is
because, although not displayed, a row is returned for each
different Format that any given album is available on. Once
again use the DISTINCT clause to prevent the SELECT statement
from returning duplicate rows.
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
Click Test again, there should now be just 25 records, one for
each album in the database.
|
| 8.24 |
This Recordset requires
a WHERE clause in order to display only the results we require
according to the search paramerters.
The WHERE clause tells the SQL SELECT to return results according
to the criteria set out within the WHERE clause.
In the SQL window amend the SQL statement to read as follows.
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE ArtistName LIKE "%beck%"
Click the Test Button, 4 rows are returned, the ones where the
ArtistName is Beck. Close the Test SQL Statement window, now
modify "%beck%" to "%be%". Click Test again,
5 rows are returned, because Beethoven contains the character
string be.
The % symbols at either end of the character string tell the
Select statement that it doesn't matter what is either side
of the character string eg.
Like "%den%" will return a match with deny,
denied, sedentary,
golden, dentist
and den
Like "den"
will only match with den
The quotes are used to indicate that it is a character
string that we wish to look for a match with.
Numeric values are entered without quotes, you can also use
logical operators to return results, remember the data types
must match, for instance AlbumID is a numeric value, ArtistName
is a character string.
Try the following:
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID = 4
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID <> 4
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID > 4
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID < 4
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID = 4 OR AlbumID = 5
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID > 4 OR AlbumID < 7
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID > 4 AND AlbumID <7
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID >= 4 AND AlbumID <=7
|
| 8.25 |
Now try this:
Modify the SQL Select statement as follows:
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE ArtistName LIKE testVariable
Now in the Recordset options palette click on the plus button
at the bottom of the SQL window and type testVariable
in the Name field "%beck%"
in the Default Value field and testVariable
again in the Run-Time-Value field.
Click Test, we see the four rows returned as if we had entered
the criteria directly in the WHERE statement.
|
| 8.26 |
This is the basis of how
we are going to return the results required according to the
criteria specified by the user with the search parameters. However
because we have multiple criteria and options, we need to generate
the entire WHERE clause dynamically.
Change the text in the SQL window as follows:
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields sqlString
Change the Variable Name and the Run-time Value to sqlString
and the Default value to WHERE
AlbumID <> -1
Click Test, the default
WHERE clause contained within the variable will return all rows,
because no Album has an ID of -1.
Close the Recordset options palette.
|
| 8.27 |
In the Data Bindings window
there should now be the two recordsets we have created click
on the plus symbol next to rsQryDisplayFields
to see the available records. Click on ArtistName
to highlight it.
Hold down the mouse button and drag it onto the Web page. A
box with a plus sign next to it will appear next to the mouse
pointer. Position the pointer in the empty table cell under
the heading Artist or Group Name
and let go of the mouse button. Repeat the process placing AlbumTitle
and Genre in the empty
cells beneath the relevant headings.
The page should now appear as follows.
|
| 8.28 |
Now we are going to use
some of Ultradev's built in server behaviours and Live data
objects to improve the appearance and add functionality to our
page.
If it is not open already, open the objects palette (Windows
- Objects or Ctrl+F2).
|
| 8.29 |
Click on the small down
arrow 
at the top right to open the drop down menu.
Select Live from the drop down menu to show the Live Data objects.
|
8.30 |
Click on the Insert Recordset
Navigation Bar button  ,
in the options palette that opens, select rsQryDisplayFields
from the drop down list and the Text radio button.
Click OK, a small table with links for First, Previous, Next
and Last records is placed within the cell.
|
| 8.31 |
In the Data Bindings window
click on the Server Behaviours tab, here you can see the two
recordsets we have added, the dynamic text we placed in the
table and a whole list of Move and Show behaviours that our
Live Data object has added for us.
Click on the plus 
button at the top left of the Server Behaviours window, you
can see a list of additional behaviours. Notice the Show Region
and Move To Record behaviours. Ultradev gives us the option
to use its built in Live Data object to provide us with a recordset
navigation bar using either text or it's built in images as
we have just done. However if we wanted to set up a navigation
bar using our own custom graphic buttons we could use these
behaviours to set it up manually.
|
| 8.32 |
Click in the empty cell
below the one in which we just placed the navigation bar. Click
on the Centre Text button 
in the properties palette, so the cursor is now blinking in
the centre of the table cell. Now click on the Insert Recordset
Navigation Status button 
in the objects palette. Make sure that rsQryDisplayFields is
selected in the options palette and click OK.
At this point your page should be looking something like
this:
Dynamic Text can be formatted in the same manner as regular
text so if you wish change all the dynamic text we have just
added to Arial, Helvetica, sans-serif, Size -1.
|
| 8.33 |
At the moment our results
page would only show us one record at a time. On the page select
the three table cells with the dynamic text in them.
With them still selected click the plus button at the top left
of the Server Behaviours window and Select Repeat Region from
the drop down menu.
In the Options palette select rsQryDisplayFields and on this
occasion leave the Show option as 10 records.
Now our page will show ten records at a time.
|
| 8.34 |
On the page highlight the
whole table with the border, now hold down the shift key and
highlight the table below it with the navigation bar and the
navigation status in it, the page should look like this.
Leaving the tables selected click on the plus button on the
Server Behaviours window and select Show
Region, then Show Region
If Recordset Is Not Empty from the pop out list.
Select rsQryDisplayFields from the drop down list in the options
palette and click OK.
|
| 8.35 |
Now on the page Highlight
the table containing the text Sorry, no albums found. Select
Show Region, then Show
Region If Recordset Is Empty from the pop out list.
Once again select rsQryDisplayFields
from the drop down list and click OK.
Finally we are going to add a link to our Details page.
|
| 8.36 |
Click on the text {rsQryDisplayFields.AlbumTitle},
to select it.
Click on the plus button in the Server Behaviours window and
select Go To Detail Page
from the drop down menu.
This will open the Go To Detail Page options. Click the Browse
button next to the Detail page text box and browse to details.asp
and select it. Check that Pass URL Parameter and Column are
AlbumID and select rsQryDisplayFields
from the drop down list.
Click OK
Click OK
Your page should now appear like this.
To recap what we have done we have created a page and attached
two recordsets. We have then placed some of the values from
one of our recordsets on the web page itself and we have set
up a server behaviour to repeat this text for ten rows.
We have added a link from the Album Name to a details page so
that the user can see additional information about any particular
album.
We have added a means for the user to navigate through the records
on the page and some dynamically generated text, which informs
the user where within the recordset they are.
As our page displays the results of a search it is conceivable
that the particular criteria supplied by the user does not have
any matches and there are no results to display. Therefore,
we have set the page up so that if no results are found, the
recordset status, navigation and results table are not shown,
but instead a message informing the user that there were no
results found is displayed. If results are found then the message
is not displayed and the results and navigation tools are. |
