Inspired? No home

ADO Shape

The Shape command in ADO lets you create a hierarchical recordset. It's very useful and much better than creating two recordsets or a big join where you have to make a script that checks if you shall show a main record (i.e. order) or a related record (i.e. the products in the order). Here is how you use it:



SHAPE {SELECT OrderId FROM Order} AS Order APPEND ({SELECT OrderId, ProductId FROM Order_Cart)} AS OrderCart RELATE OrderId TO OrderId)



What we are doing here is creating two separate SQL queries. The first (SHAPE) is the main recordset while the other (APPEND) is the related recordset in the hierarchy. You can relate many recordsets into one big hierarchy, for example like this:


Order
|____ OrderCart


Each of these elements in the hierarchy is a standalone recordset. The recordset of the next element can be retrieved as a field using the name specified in the SHAPE command (AS OrderCart). We then set it as it's own recordset. Set RsOrderCart = RsOrder.Fields("OrderCart").Value.



Do Until RsOrder.EOF
Response.Write RsOrder("OrderId")
Set RsOrderCart = RsOrder.Fields("OrderCart").Value
' loop through items in this order
Do Until RsOrderCart.EOF
Response.Write RsOrderCart("ProductId")
RsOrderCart.MoveNext
Loop
RsOrder.MoveNext
Loop



See this Microsoft KB article for more information on how to use the ADO Shape command.

Written on 29 August 2004.
blog comments powered by Disqus