In this article you will be able to use the new features of dreamweaver 8 to connect to a SQL database and return the results to a datagrid.
We will show you how to create full-featured, customized DataGrids using Macromedia Dreamweaver 8 on the ASP.NET application server platform.
You will set up your Dreamweaver MX site, connect to the database, create the DataSet for the DataGrid, and create a default DataGrid. In the following three articles, you will build on what you learn in this article and learn how to customize the DataGrid appearance, make the DataGrid editable, and sort columns in the DataGrid. By the end of the article, you will be comfortable working with DataGrids and will be familiar with their various features and attributes. This article uses the DataSet tag to obtain the data from the sample Northwind database included with SQL Server 2000 and display it in a DataGrid.
Firstly you will need to set up your site definition.
Select Site > New Site... from the Menu bar to create a new site.
On the Basic tab, enter the following information, clicking Next at the bottom of each screen:
Enter a title in the Name field.
Yes, I want to use a server technology. Select ASP.NET C# as the server technology.
Edit and test locally, storing your files at C:Inetpubwwwroot.
URL to browse to the root of your site http://localhost/.
No, do not copy files when I am done editing.
Click Done to create an initial cache of your site.
Open the the Site Definition dialog box by choosing Site > Edit Sites. Select your site's name from the list, and click the Edit button.
Set up the testing server to use ASP.NET C# as the Server Model. Set the Access and other values as appropriate for your site
Now we need to create a connection to the SQL database.
Within Dreamweaver, select File > New... from the Menu bar to create a new file. On the General tab, select Dynamic Page for the Category and ASP.NET C# for the Dynamic Page type. Click the Create button to create the new page. This step is required because Dreamweaver needs to know what kind of server technology your pages will use before it can help you in the create a database connection Open the Databases tab of the Application panel group. Click the plus button, and select the SQL Server Connection option.
When the SQL Server Connection dialog box appears, type in the information presented below, and ensure that it matches your database's User ID, password, and name.
Connection Name: Set this to Northwind.
Data Source: Set this to the name of your database server.
Initial Catalog: You will be using the Northwind sample database that comes with Microsoft SQL Server, so set this to Northwind.
User ID: The default user name for Microsoft SQL Server that is created at the time of installation is SA, so set this to SA.
Password: The default password for the SA account is blank, so leave this blank.
Click the Test button in the SQL Server Connection dialog to ensure that you set up your connection correctly. If an error occurs, check your Connection string settings. Next, click OK to accept the connection settings. You will now see the Northwind connection appear in the Databases tab of the Application panel group.
Look at web.config in your site's root directory. Notice that Dreamweaver has added several new lines under the element:
You will now need to create the DataSet that you will use for the DataGrid data source.
Using the new page you created prior to defining the database connection, save the page as employees.aspx
In the Application panel, click the Server Behaviors tab.
The file DreamweaverCtrls.dll must reside in the /bin directory under the root of your website. This file is required by the ASP custom tags provided by Dreamweaver MX. If you haven't already done so, click the deploy link within the Server Behaviors tab. This will copy the DreamweaverCtrls.dll file into the appropriate directory. Next, click the plus button and select DataSet.
The DataSet dialog appears. Name the DataSet ds_list.
Select Northwind from the connection pop-up menu.
Select dbo.Employees from the Table pop-up menu.
Select the Selected: radio button from the Columns: radio button group.
The Employees table in the Northwind database contains 18 columns. For this example, select the following 10 columns only (hold the Ctrl key while you select each column): EmployeeID, LastName, FirstName, HireDate, Address, City, Region, PostalCode, Country, HomePhone
Since you don't need to filter the query, set Filter to None.
Since you want to sort alphabetically according to last name, so set Sort to LastName Ascending.
Click OK to create the DataSet.
Save your work.
Now you have created the connection to the database and specified the data that you need to be returned you will need to create a datagrid to display the information in a tabular form.
Open the Server Behaviors tab of the Application panel.
Select the plus button again, and select DataGrid.
Name your DataGrid dg_list.
Select the ds_list DataSet from the popup list.
Show just four Records instead of the default 10 records.
Leave the rest of the attributes at their default values for now.
Click OK to create the DataGrid.
Save your work.
To see your page in action, view the page in Live Data preview (View > Live Data), or press the F12 key to preview the page in your browser as shown in the illustration below.
The column headers are taken from the names of the database columns. Now that you know how to create DataSets and how to create the default display for the DataGrid using the data in those datasets.