Recordsets and Data - Page 3

Recordsets are where you define how much of the data in the database you want to pull into your ASP page. The simplest choice is a selection of columns from a single table or query. In UltraDev you define the recordset for a page in the Data Bindings window. Here you give the recordset a name, identify the database connection it should use, and which table and fields you wish to select for publication on your page.

Assuming you've asked Ultradev to write Visual Basic — the most common choice for ASP pages — the recordset code you finish up with on your page will look something like this:

<%
  set rsReleaseSummaries = Server.CreateObject("ADODB.Recordset")
  rsReleaseSummaries.ActiveConnection = "dsn=scaalcoffee;"
  rsReleaseSummaries.Source = "SELECT PRID, PRTitle, FROM
  PressReleases"
  rsReleaseSummaries.CursorType = 0
  rsReleaseSummaries.CursorLocation = 2
  rsReleaseSummaries.LockType = 3
  rsReleaseSummaries.Open
  rsReleaseSummaries_numRows = 0
  %>

This sits very close to the beginning of the page, before any HTML. If you're new to ASP it's very interesting to look at the mix of VB and HTML produced on your first Ultradev pages. The two languages alternate through the page. The HTML elements are passed through the server untouched and sent to the viewer's browser. The VB elements are processed by the server and usually result in more lines of dynamic HTML code, which are also sent to the browser as they are created.

Defining a recordset is a preparatory element and doesn't produce HTML itself, it just paves the way for later VB code to do so. The first line of the code shown above names the recordset "rsReleaseSummaries." The second line shows the database connection that should be used, called "scaalcoffee." And the third line identifies the fields that will be included in the page, which are "PRID, PRTitle, PRShort, PRDate" from the table "PressReleases." The remaining lines define standard elements of the recordset and for most simple examples these stay the same.

To get the data showing on your page, you now need to connect the database fields to text elements on your page. Continuing with the example started above, we can create a regular HTML table with four cells containing the text - PRID, PRTitle, PRShort, and PRDate. In the UltraDev Data Bindings window we then highlight each field and drag and drop it on to the corresponding text in a cell. UltraDev changes the cell contents, for example the text PRTitle changes to:

<%=(rsReleaseSummaries.Fields.Item("PRTitle").Value)%>

This will create the cell content dynamically from the database.

There's one final step in this simple example, which is setting up the HTML table so it is repeated for each row of data in the database. In UltraDev that's done in the Server Behaviors window by highlighting the table and adding the behavior "Repeat Region." This adds the following VB code before and after the table.

<%
  While ((Repeat1__numRows <> 0) AND (NOT rsReleaseSummaries.EOF))
  %> 

<Table HTML goes here>

<%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rsReleaseSummaries.MoveNext()
  Wend
  %>

That's the simple example completed. Selecting View > Live Data should now show the ASP in action and return a page full of data.

Debugging and Transfer to a Real Server

Although UltraDev is a great piece of software, when you're creating your first ASP pages there are many things you need to get right, and it shouldn't be too much of a surprise if your pages fail the first few times you try to run them. Here are a couple of practical debugging tips.

When you've created your recordset, there's a Test button available in the recordset window, under Data Bindings. It's a good idea to use this. This confirms (or otherwise) that your database connection is working as it should, that the fields and tables you've selected are valid and that any SQL statements you've made are also correct. If you've got something wrong, the error message will tell you whether it's your connection or your SQL. It will even try to tell you which bit of your SQL is wrong, though often without success. Any syntax error you get here will relate to your SQL, not your VB, which of course UltraDev is writing flawlessly.

Whenever a page fails it's a good idea to go back to this Test button and try it out. In one case I had a page that had previously worked and then failed. I wasted time looking at the server and at file versions, then pressed the Test button and found that a colleague had renamed all the fields in the database.

If Live Data won't work, especially at the beginning of a work session, it's a good idea to check your server and offline/online mode. One easy way to do this is to have a simple HTML file on the server and bookmark it in your browser. Then access the file and press shift refresh/reload to check that you're not getting a cached version. If you can see the file then you know the server's working. In practice I find that I use Live Data in the early stages of building a page, but towards the later stages when I'm changing cell sizes and appearance I'm more likely to "put" the page to the remote site after each change and view it in a browser.

Finally, the UltraDev plus PWS combination is aimed at the development and testing phase. Once your pages are working, you'll need to transfer your work to a real server connected to the Internet or an intranet. This means that some of the details on your pages may change. One that will almost certainly change is the Data Source Name (dsn) in the first line of any recordset. If you developed using an Access database and the real system uses SQLServer or other alternatives then you may also need to change the names of tables, queries and even individual fields.

That's where the Dreamweaver element of Dreamweaver UltraDev steps in, with its powerful global editing facility. In the Site window, select the files or folders you'll be transferring - but don't open them. Then choose Edit > Replace and make sure HTML Source is selected rather than Text. Now you can make changes across multiple files. Personally, I like to create a small text file with all the necessary changes listed. Then I copy and paste the individual lines into the fields of Dreamweaver's Replace feature. Eight or ten global changes take just a few minutes.

Like every other aspect of Dreamweaver Ultradev, it's fast and effective. This is one program that will repay your purchase investment through saved time in just a few days.

Recommended Links:

WDVL ASP articles
More on ASP, plus a host of links

Starting with UltraDev - Page 2
ASP using Dreamweaver UltraDev and PWS