Tutorials >
Spooky's Databases > DRW
Steps
Step 1
On the surface, the DRW seems quite a
simple wizard to use. If you delve deeper into its
murky Microsoft depths however, you will find quite a
powerful and customizable tool. (Other than
learning asp...oh
I've mentioned that already?)
Read on........
Use sample database connection
Enables you to access the Northwind database for
testing. The sample database is a cut down version with
the same functionality. If you want to test stored
procedures, replace it with the full Northwind database
from the \Program Files\Microsoft Office\Office\Samples
directory.
Use existing database connection
Allows you to select from one of the connections you
have already set up on your PC / web (Tools >
Web Settings > Database)
Connections are made by importing a database file,
using the 'Save to database' option or adding a
connection manually
Remember that the term 'Database' can
refer to any ODBC compliant data source, mdb, xls, txt,
csv etc.
Use a new database connection
Allows you to make a new connection.
It follows the same steps as Tools > Web
Settings > Database > Add
Step 2
Record source
Choose the record source (Table) from your database
connection.
The selection could also be a stored procedure (it will
have VIEW in brackets showing it is view only)
Custom Query
The custom query selection allows you to enter any
query you wish to perform. Normally cut and pasted from
a SQL source.
Custom queries prevent you from using
Criteria in later steps.
Insert Parameter
This button allows you insert a parameter passed from a
form, either on this page or from a form on another
page.
You must know the name (parameter) of the text box that
passes the value.
Type the name of your form field into
this box.
All this step basically does, is
places your field name into the query with ::
surrounding it.
Its easier to type the information
into the Custom query box yourself. :)
Paste from Clipboard
Simply used to paste any queries you have stored on the
clipboard (CTRL + V)
Verify Query
Press this button to verify your query is correct.
Unfortunately, this only checks the record source of
the query and not the values being passed. So it is
possible to send the wrong syntax to the SQL query.
i.e. a correct query like this :
SELECT * FROM Products WHERE (ProductID = ::ProductID::)
will be verified correctly as will
the following query:
SELECT * FROM Products WHERE (PrdctID = '::ProductID::')
Syntax for query fields
The correct syntax for (Access) queries are:
Text fields = '::Fieldname::'
Numeric fields = ::Fieldname::
Date fields = #::Fieldname::#
::Fieldname:: will be replaced with
the Fieldname value passed from the form when the query
is run.
(This is the same as the %%Fieldname%% syntax that was
used with FP98)
Step 3
An overview of Step 3, perhaps one of
the more important steps to understand.
Edit list
Allows you to add, order and remove fields that will be
displayed on your web page as part of a query.
More Options
This allows, as the option says, to filter, limit or
sort the database results, set up a search form or
provide defaults for the search
Limit number of records can be
preset to prevent a search of a large database to
overload the server
Message to Display can be
customized to what you want to be shown to the user
Criteria
Allows you to display certain records from the
database, either with a preset criteria or a criteria
based on form / hyperlink input (A search form)
Note : You do not get this option
when using a custom query from step 2
Add / Modify
Add or modify allow you to set the criteria.
The Field name
is the column in the database you wish to use for your
comparison.
Comparison
gives you access to valid SQL operators depending on
the field type
Value is
where you can enter the criteria you want to use in the
comparison
eg.
Field Name = ProductID (A numeric field)
Comparison = Equals
Value =12
SQL = Select * FROM Table WHERE ProductID=12
The Value
field can also be used where the comparison value is
being passed from a
form or
hyperlink.
In this case, you would enter the name from the text
box on your form page, in this case T1
Normally you would name your text box
the same as the database field so as to avoid
confusion.
Or you would used the Field name from
the hyperlink:
Querypage.asp?T1=Value
The "Use this search form field"
box must be checked.
AND / OR
If you are using more than one criteria / search field
then you can use the AND / OR operator to further
define your query.
Note: Advanced
nesting cannot be achieved here with the DRW, although
you can modify the gray code in HTML view to achieve
the same output.
Ordering
The order in which returned records are displayed can
be set using the Order button.
In this example, the query will be
sorted first by the ProductID ascending and then
secondarily by the CategoryID descending.
The SQL would look like this :
"SELECT * FROM
Products ORDER BY ProductID ASC,CategoryID DESC"
Defaults
The Defaults button becomes available after setting a
criteria
Note: If you set no
defaults, and you are using form contents to set the
criteria you will receive this message:
Database
Results Error
Description: [Microsoft][ODBC Microsoft Access
Driver] Extra ) in query expression '(ProductID = )'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC
Drivers
One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.
Adding a default value that you want
to show your users can be done here, or if you wish to
show no records, use a value of NULL
For text fields, while using the LIKE
operator, you can use the wildcard "%" to show all
fields
Step 4
Step 4 in the DRW controls the
formatting output options.
There are three you can choose from:
Table
A table is constructed containing the fields that you
specified in
step
3 of the DRW
- Use table border
Applies a table border width of "1"
- Expand table to page width
Applies a "100%" width to the table
- Include header row
Adds a header <TH> row to the table. (Bold text of
column names)
All of these settings can be modified
by right clicking on the table once produced and
changing its properties
Back to top
List
A list is allows formatting on a field by field basis.
You can place the value into the following formats:
- Paragraphs
A new paragraph <P> is
started for each field
- Line breaks
A new line break <BR> is
entered after each field
- Bullet lists
A bulleted list is produced
- Numbered list
A numbered list is produced
- Definition list
- Table
A table is constructed for
each group of records
- Formatted
Any formatting can be
applied to the output, as long as the fields
remain within the yellow DRW borders.
- Text fields
Database output is placed
into a text box, and a submit form is produced.
This is handy for editing database entries as you
can submit the form to a page that processes the
input
- Scrolling text fields
Same as with text fields
above, but a scrolling text box is used. Better
for memo or large text fields.
Back to top
Drop down list
A drop down list can be constructed using the DRW.
It may be handy it you are constructing a search form
within your web, and you want to allow a standard
selection.
You could just enter the items manually into a drop
down list box, but if the search contents change, you
will have to re edit the web page.
This way you may use a query like DISTINCT to select
unique column values.
One value can be displayed (e.g. ProductName) and
another value can be submitted (e.g. ProductID)
Back to top
Step 5
Step 5 of the DRW finishes off how
the records will be displayed.
You can choose to display all of the
records together (Up to a maximum number of records you
set in step 3
Or you can choose to display records
split into groups of "x"
This will display your records with forward and back
form buttons to navigate through your data.
Ticking "Add search form" here will
do just that. But only if you have specified form
results to be passed to your query previously in step 3
OK, that's all for the DRW for now. I
hope you know more than when you started?. Good :)
Now we can move on to our next step!
Modifying the DRW code so it does what YOU want!
Click here to learn more
