Databases and Dreamweaver MX 2004, concluded - Using Dreamweaver MX 2004 to Create Queries
(Page 2 of 5 )
Let’s start by opening Dreamweaver and trying out some simple queries to pull data from our sample database. Open the site you defined in Chapter 1 and you can begin to create some Recordsets. Before proceeding, make sure you have a data source set up, as detailed previously.
Open the Application panel, and then select Bindings->+->Recordset (Query). The dialog box shown in Figure 2-8 will open:
Figure 2-8. The Recordset dialog box
- Now select our CompanyInfo Data Source from the Data Source drop-down menu. Leave the User Name and Password fields blank, and select Departmt from the Table drop-down menu. For this example, the name of the Recordset is not relevant, so use the default Name, Recordset1. Later, this will be used as a reference name for the code that Dreamweaver will create.
- Leave the other fields at their default values and click the Test button. The Test SQL Statement pops up, displaying the search results, as shown in Figure 2-9.
Our query simply selects all the columns from the Departmt table. If you want to select only certain columns from a table, simply click the Selected radio button next to Columns in the Recordset dialog box, then click on the columns that you wish to select. To select multiple columns, hold Ctrl while clicking on them.
Figure 2-9. The results of testing our Recordset
- Return to the Recordset dialog box by clicking OK. This time, click on the Selected radio button, and then select the Dept_ID and Dept_Name columns. Click on the Test button. You should see the same results as last time, but without the Location column.
You can use the Recordset dialog box to filter out rows as well. Change the Filter drop-down menu selection from None to Location, and then change the URL Parameter drop-down menu selection to Entered Value. Finally, in the text field containing the text Location, change the text to Cambridge as shown in Figure 2-10.
Figure 2-10. The Recordset dialog box with the filter applied
You will see only three records instead of four. This is because the fourth record was for the location San Francisco, so it wasn’t selected by the filter that we set up.
If you want to sort the records by a particular criterion, such as department name, you simply need to change the value in the Sort drop-down menu from None to Dept_Name. Click on Test again, and you will see the three departments in Cambridge sorted by department name in ascending (A to Z) order, as shown in Figure 2-11.
Figure 2-11. The three departments in Cambridge, sorted in ascending order
To sort the results in reverse order (Z-A), simply change the value in the Sort drop-down menu from Ascending to Descending.
By now, you must have noticed that Dreamweaver allows you to create simple queries without having to write any SQL. Unfortunately, you are limited to simple queries. If you want to write anything more complicated, for example, to order the search results by more than one column, you must switch to the Advanced interface and write some SQL, or manually tweak our SQL code in Code View.
You may have also noticed that you can do onlySELECTqueries in the basic Recordset dialog box, notINSERT,UPDATE, orDELETE statements. If you want to create any of these statements, you must write your own SQL.
Now let’s look at creating advanced queries by switching to the Advanced Recordset interface.
NOTE We will look at INSERT, UPDATE, and DELETE queries later in Chapter 6. |
Next: Using Dreamweaver MX 2004’s Advanced Recordset Interface >>
More ColdFusion Articles
More By Apress Publishing
|
 This article is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress, 2004; ISBN: 1590592379). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 3 
