Nested Repeat Region using Data Shaping

T-Cubed
Tom's Top Tutorials - Ultradev/ASP

nested repeat region using data shaping

This tutorial assumes that you are proficient at:
Editing code using HTML Source Window or Code View in UD4

Download the Database & Sample Page

The first thing to know about data shaping is that you have to use an OLEDB connection to the database. ODBC links are not supported as a special connection string is used to 'talk' to the database.

In the connection string box you need to type:

"Provider=MSDataShape; Data Provider=Microsoft.Jet.OLEDB.4.0; Data Source= c:\t_cubed\data_shaping.mdb"

This need to be typed all on one line. If you follow the example described in this tutorial, ensure that the 'data_shaping.mdb' file in the folder c:\t_cubed\

If you have the database in another folder, change this file path in the connection string appropriately.

Next we create 2 recordsets using the standard Data Bindings >> Recordset (Query) menu.

This may seem a little strange, but now go into the code view and modify the recordset code that UD4 created by putting an apostrophe (') at the beginning of all the lines of code inside the <% %> tags.

This means that the code is not 'executed' when the page is loaded but we can still use the drag and drop nature of UD4 to create our page layout. (UD4 does not support data shaping SQL syntax in the recordset dialog box).

So, now we need to create our page layout.

Create a 2 x 2 table and drag the elements onto the page from the Databindings window so you end up with something like this:

(The reason for using a 2 x 2 table is that UD4 has a bug when using the Repeat Region Behaviour with tables of only one column)..

Select the 2 table rows and apply the Repeat Region Behaviour and choosing 'rsSections' as the recordset.

If you try to preview the page after adding the table and content you will get an error, this is because rsCatalogue doesn't exist in the code (remember - we commented it out earlier on).

So.....now onto the Data Shaping part.

We need to add the DataShaping Query which will link the 2 tables together.

Go to the code view and add the following code just before the opening HTML tag.

This creates a Query which Select everything from 'tblSections' & 'tblCatalogue' and uses 'SectionID' to link these tables together. This link is called 'rsCatalogue_Link'.

So we have now created the recordset (parent) and defined the second recordset (child), we need to add code inside the Repeat Region, to create the 'child' recordset, and only display items that have a matching 'SectionID'.

Set rsCatalogue = rsSections("rsCatalogue_Link").Value

We then need to add the code after this to loop through all entries in thie 'child' recordset and display them. The code will look something like this below. This code completely replaces the second row of the table, and is located just after the first </tr> tag.

That's all there is to it.

The sample database and completed page is available to download here.