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) </pre> 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: <pre> Order |
__ OrderCart </pre> 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 </pre> See this Microsoft KB article for more information on how to use the ADO Shape command. |