DreamweaverFAQ.com
Search the site!Search DWfaq.com | Print This PagePrinter Friendly Page
Current: Default | Default: Snazz-o-Matic


DreamweaverFAQ.com » Tutorials » Dynamic » Working With Datashaping


Working With DataShaping

Introduction

One of the most difficult parts of designing a site based on relational data, is getting all the data to make sense on a webpage. If you have multiple child records for each parent record, for example multiple chapters (child records) for each book (parent record), then you have to build multiple recordsets, build nested repeat regions and manually code If...Then...Else statements to check for record changes.

To get around all of this mess you can use a function of Microsoft's ADO Components. The MSDataShape driver takes care of all of the record relationship management. Even after playing with DataShaping for a bit, those opening sentences even confuse me. So in order to make it clear exactly what's going on I'm going to jump right into a real life example.

After all is said and done, we're going to build something like this:

Final Results
Professor Class Book
Professor 1
  Class1
  Book 1
  Book 2
Professor 2    
  Class2
  Book 3
  Book 4
  Class3
  Book 5
  Book 6

What are the ingredients?

  1. A relational database with related records in several tables.
  2. A database connection using the MSDataShape Provider.
  3. Some hand coded DO WHILE...LOOP statements.
  4. Four DataShape related Snippets:

Building the database tables

Our example is for a school book list, based on professor and classes that each professor teaches (I got the idea from a 4GuysFromRolla article). We'll first build our Class list by Professor, and then for the more adventurous, we'll add the books. So in order to do this, we obviously need a database to do some shaping. Here are the three tables we're going to use in our database:

tblProfessors
FieldName DataType Description
intProfID Autonumber Primary Key for each professor
txtProfName Text Professor's full name

tblClasses
FieldName DataType Description
intClassID Autonumber Primary Key for each class
intProfID Number Foreign Key to determine professor teaching the class
txtClassName Text Class' name

tblBooks
FieldName DataType Description
intBookID Autonumber Primary Key for each book
intClassID Number Foreign Key to determine which class the book belongs in
txtBookname Text Book's name

Now that we've got all our tables, here's a graphical representation of how they're all related. This is done in Microsoft Access by clicking Tools > Relationships, and dragging and dropping the related fields on top of each other.

Database Table Relationships

In order to do all our datashaping, we obviously need some data in those tables. Here's the content for each table:

tblProfessors
intProfID txtProfName
1 Daniel Short
2 Angela Buraglia
3 Danilo Celic
4 Massimo Foti
5 Kindler Chase

tblClasses
intClassID intProfID txtClassName
1 1 Hand Coding VBScript
2 5 Working with DataShaping
3 1 Gotta Getta gifStream
4 2 Building a Style Changer
5 2 Dreamweaver Power Tips
6 2 Advanced CSS
7 3 Advanced Extensionology
8 3 Harassing Bloggers
9 4 Supa Advanced Extensionology
10 4 How to Convert a Short to CF
11 4 Working with ColdFusion MX
12 5 Learning VBScript by Trial and Error

tblBooks
intBookID intClassID txtBookName
1 1 VBScript 101
2 1 ASP for Shorties
3 1 Wrox ASP 3.0
4 2 ADO for Dummies
5 3 Visual JavaScript
6 3 Eddie's JavaScript Primer
7 4 CSS 101
8 5 Dreamweaver MX Bible
9 5 Dreamweaver Tips
10 6 Eric Meyer on CSS
11 7 MX Extensionology
12 8 How to Harass Your Friends
13 9 Moving the World With APIs
14 10 ColdFusion vs ASP, The War
15 11 Forta on CF
16 11 CFWACK
17 7 Dreamweaver Extension PDF

Building a database connection

To work with datashaping, I recommend using two different and distinct connections. The reason for this, is that Dreamweaver can't handle the custom provider we need to do the datashaping. To do this, follow these steps:

  1. Open the Databases Panel, Window > Databases or Ctrl+Shift+F10.
  2. Click the plus symbol and choose Custom Connection String.
  3. Name your connection "connNoShaper" and use this as your custom connection string:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\datashape.mdb;"
  4. Click the plus symbol and choose Custom Connection String again. This time we need to change our previous connection string to add a new Provider. Our second connection string, which we're going to name "connDataShaper".
    "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\datashape.mdb;"
    Notice we added "Provider="MSDataShape;"" and changed the previous "Provider" declaration to "Data Provider".

Note: If you're using a DSN connection, you may be able to simply add a Provider declaration to your recordset code. In the Code View, simply add the Provider declaration before the Open statement.

rsProfessors.LockType = 1
rsProfessors.Provider = "MSDataShape" 'Use this line for DSN connections. rsProfessors.Open()

You should now have two separate connections in your Databases Panel. If you expand your connections in the Databases panel, you'll notice that the tables in "connDataShaper" are prefixed with "null.", since Dreamweaver doesn't know how to handle them. These tables can't be used by Dreamweaver to drop data on the page. The "connNoShaper" connection will be used to build some plain "non-shaped" recordsets to use in our Bindings Panel.

Databases Panel

Building our working recordsets

The one disadvantage to working with DataShaping is the inability of Dreamweaver to handle them in a visual manner. To get over that we can build some regular recordsets and then simply comment out the server side code in the Code View. Even though the recordsets are commented out in the actual code, you'll still be able to use the bindings panel to work with the data.

For our first example (without the books) we're going to need two different recordsets, rsProfessors and rsClasses. To create our recordsets, follow these steps:

  1. Open the Bindings Panel, Windows > Bindings or Ctrl+F10.
  2. Click the Plus symbols and choose Recordset(Query).
  3. Create the rsProfessors recordset using the Simple Recordset dialog:
    • Name: rsProfessors
    • Connection: connNoShaper
    • Table: tblProfessors
    • Columns: All
    • Sort: txtProfName, Ascending
  4. Create the rsClasses recordset using the Simple Recordset dialog:
    • Name: rsClasses
    • Connection: connNoShaper
    • Table: tblClasses
    • Columns: All
    • Sort: txtClassName, Ascending

You should now have two recordsets in your Bindings Panel. Switch to Code View, View > Code or Ctrl+`, and scroll to the top of the page. Comment out all of the recordset code by putting an apostrophe at the beginning of each line (thanks to the guys at T-Cubed for the tip). This allows you to use the Bindings Panel using the recordsets without actually executing the code on your live page. You should now see something similar to this:

Commented Out Recordsets

Shape dat Data

Now we need to build the actual DataShaping piece of the puzzle. To do this, we're going to do a little handcoding directly below the recordsets we just commented out. The first thing we need to do is add an include to our DataShape connection. While still in Code View, place your cursor on Line 31, and choose Insert > Script Objects > Server Side Include and browse to "Connections/connDataShaper.asp". That will place this include on Line 31:

<!--#include file="../../Connections/connDataShaper.asp" -->

On Line 32, we're going to use our DataShape Recordset Snippet. Install the Snippet and then place your cursor on Line 32, open the Snippets Panel, Window > Snippets or Shift+F9, and insert the DataShape Recordset snippets from the DataShaping category. Lines 32-41 should now contain this code:

<%
Dim SQL
SQL = "SHAPE {SELECT * FROM table1} "
SQL = SQL & " APPEND ({SELECT * FROM table2} AS Alias "
SQL = SQL & " RELATE field1 TO field2)" Dim rs
Set rs= Server.CreateObject("ADODB.Recordset")
rs.Open SQL, MM_conn_STRING
%>

Here's an explanation of each of the pieces of the SQL statement:

DataShape SQL Explanation

The Snippet is set up so you can just copy/paste in the necessary pieces. The SQL lines define our DataShape statement. The syntax is fairly self explanatory.

  1. Copy the SELECT statement from the rsProfessors recordset we created earily, and paste it in place of "SELECT * FROM table1".
  2. Copy the SELECT statement from the rsClasses recordset and paste in in place of "SELECT * FROM table2".
  3. Replace "AS Alias" with "AS Classes ".
  4. Replace field1 and field2 with the columns that relate our two recordsets, in this instance it's "intProfID".
  5. Replace the "rs" in the last three lines with "rsProfessors", which is the same name as the first recordset we created.
  6. Replace "conn" in "MM_conn_STRING" with "connDataShaper".

Our finished code block should like this:

<%
Dim SQL
SQL = "SHAPE {SELECT * FROM tblProfessors ORDER BY txtProfName ASC} "
SQL = SQL & " APPEND ({SELECT * FROM tblClasses ORDER BY txtClassName ASC} AS Classes "
SQL = SQL & " RELATE intProfID TO intProfID)" Dim rsProfessors
Set rsProfessors= Server.CreateObject("ADODB.Recordset")
rsProfessors.Open SQL, MM_connDataShaper_STRING
%>

We can now create the base table for our DataShaping. We just need 2 columns and three rows. We just need to put the Professor's name in the first row and merge the two columns, and the class name in the second row. When you're done, the page should look like this:

Now switch to code view, and wrap the Parent Recordset Repeat snippet around both the Professor name table row and the Class name table row. Your finished code should look like this:

<%
DO WHILE NOT rs1.EOF
set rs2 = rs1("Alias").Value
%>

<tr>
<td colspan="2"><%=(rsProfessors.Fields.Item("txtProfName").Value)%></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><%=(rsClasses.Fields.Item("txtClassName").Value)%></td>
</tr>
<%
rs1.MoveNext()
LOOP
%>

Now make all of the same replacements you made when you first added the DataShape SQL to the page. Except this time, we need to replace "rs1" with rsProfessors, our parent recordset and "rs2" with "rsClasses", which is our child recordset. We also need to replace "Alias" with the Alias we defined in our APPEND statement in the DataShape SQL. This tells the ADO component which records from the Child Recordset to display. After making all those changes your code should look like this:

<%
DO WHILE NOT rsProfessors.EOF
set rsClasses = rsProfessors("Classes").Value
%>

<tr>
<td colspan="2"><%=(rsProfessors.Fields.Item("txtProfName").Value)%></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><%=(rsClasses.Fields.Item("txtClassName").Value)%></td>
</tr>
<%
rsProfessors.MoveNext()
LOOP
%>

Now wrap the Class name row with the Child Recordset Repeat snippet and once again replace "rs2" with "rsClasses". This is our completed code:

<%
DO WHILE NOT rsProfessors.EOF
set rsClasses = rsProfessors("Classes").Value
%>
<tr>
<td colspan="2"><%=(rsProfessors.Fields.Item("txtProfName").Value)%></td>
</tr>
<% DO WHILE NOT rsClasses.EOF %>
<tr>
<td>&nbsp;</td>
<td><%=(rsClasses.Fields.Item("txtClassName").Value)%></td>
</tr>
<% rsClasses.MoveNext()
LOOP %>
<%
rsProfessors.MoveNext()
LOOP
%>

If Dreamweaver could handle applying nested repeat regions (whether it really supported them or not) then we could use the standard Repeat Region Server Behaviors, instead of applying the While statements manually. However, Dreamweaver checks to see if you're applying the repeat inside another repeat, which generates an error.

Check out the completed example.

A Gourmet Treat

We've now completed a Parent/Child DataShape, which builds our nested repeat regions with just a few lines of code. Now I know I've whet your appetite for more. So our Gourmet Treat for this lesson is going to be adding a third dimension, a Grandchild Recordset (they're breeding like rabbits), to handle our books for each class. To do this, we're basically going to nest a SHAPE statement inside our main SHAPE statement. Here's what the Grandchild Recordset setup looks like:

Nested Shape Grandchild Recordset

Notice that we've nested a second SHAPE statement inside our first APPEND statement. So we just add our third recordset, a new alias and some new key references. To add our books to our class lists, we'll use this SQL:

<%
Dim SQL
SQL = "SHAPE {SELECT * FROM tblProfessors ORDER BY txtProfName} "
SQL = SQL & "APPEND(("
SQL = SQL & " SHAPE {SELECT * FROM tblClasses ORDER BY txtClassName} AS Classes "
SQL = SQL & " APPEND ({SELECT * FROM tblBooks ORDER BY txtBookname} AS Books "
SQL = SQL & " RELATE intClassID TO intClassID)) "
SQL = SQL & "RELATE intProfID TO intProfID)" Dim rsProfessors
Set rsProfessors= Server.CreateObject("ADODB.Recordset")
rsProfessors.Open SQL, MM_connDataShaper_STRING
%>

You should notice the new SELECT statement and alias "Books". Now we just need to add a new SET statement inside our Classes Loop, and add new Grandchild DO WHILE...LOOP statements.

<%
DO WHILE NOT rsProfessors.EOF
set rsClasses = rsProfessors("Classes").Value
%>
<tr>
<td colspan="3"><%=(rsProfessors.Fields.Item("txtProfName").Value)%></td>
</tr>
<% DO WHILE NOT rsClasses.EOF
set rsBooks = rsClasses("Books").Value %>
<tr>
<td>&nbsp;</td>
<td colspan="2"><%=(rsClasses.Fields.Item("txtClassName").Value)%></td>
</tr>
<% DO WHILE NOT rsBooks.EOF %>
<tr>
<td colspan="2">&nbsp;</td>
<td><%=(rsBooks.Fields.Item("txtBookName").Value)%></td>
</tr>
<% rsBooks.MoveNext()
LOOP

rsClasses.MoveNext()
LOOP rsProfessors.MoveNext()
LOOP
%>

Check out the completed Grandchild example.

The Full Course Meal

Don't just stop there. You can expand it to multiple grandchildren, display information programatically by writing dynamic SQL statements, and on and on. Check out our final example, also available in the full download.

Thanks to Kindler Chase for pointing me toward several DataShaping resources that helped get me started.

::back to top::

::This page last modified 8/13/2013 at 09:37::

Copyright © 2001-2017 DreamweaverFAQ.com All Rights Reserved.
All brands, trademarks, tutorials, extensions, code, and articles are the property of their respective owners.
A production of Site Drive Inc.
Legal Notice | Privacy Policy | Disclaimer & Notice