Data Data Shaping
=============
A data shaping or hierarchical Recordset can display a tree structure or related records.
Applying data shaping must:
1. Using the MSDataShape OLE DB provider
Provider=msdatashape;data provider=sqloledb;data source= ...
A more concise approach is as follows:
Connection string:
strcon= "Provider=sqloledb;data source=servername;" &_
"Initial Catalog=defaultsql; User Id=sa; Password= "
Building MSDataShape OLE DB connections
strcon= "Provider=msdatashape;data=" &strcon
2. Using a special cosmetic language, it is an extension of SQL that allows for a hierarchy of constructs.
(1) The grammar of the Shaping language:
Shape{parent command} [as parent alias]
APPEND ({Child Command} [as child alias]
RELATE Parent_column to Child_column) [as Parent_column_name]
EXAMPLE:
Take the publishers and titles in the pubs library for example
Shape{select * FROM Publishers}
APPEND ({SELECT * from Titles}
RELATE pub_id to pub_id) as Rstitles
In this example, the first row is the parent Recordset, the second row is the child Recordset, and the third row indicates the associated parent, child recordset
Two fields, and each of the two labels has a field named pub_id. This command returns a publisher containing the
Recordset, which contains a new column (Rstitles) with a child Recordset, through the AS clause.
3. Using Data Shaping in ASP
How do I access the titles recordset in the example above?
Set rstitles=rspublishers ("Rstitles"). Value
A complete example of traversing a recordset:
<%
Dim rspublishers
Dim rstitles
Dim strshapeconn
Dim strshape
Dim strconn
strconn = "PROVIDER=SQLOLEDB; Data source= "& _
Request.ServerVariables ("SERVER_NAME") & _
"; Initial catalog=pubs; User Id=sa; Password= "
Set Rspublishers=server. Creatobject ("ADODB.") Recordset ")
' Create a connection string
Strshapeconn = "Provider=msdatashape; Data "& strconn
' Create a parent recordset that contains a publisher and a child Recordset with a title
Strshape = "SHAPE {select * FROM publishers}" & _
"APPEND ({SELECT * FROM titles}" & _
"RELATE pub_id to pub_id) as Rstitles"
' Open recordset
Rspublishers.open Strshape, Strshapeconn
' Traversing the recordset
Response.Write "<UL>"
While not rspublishers.eof
Response.Write "<LI>" & Rspublishers ("Pub_name")
' Now the titles
Response.Write "<UL>"
' Set Variable rstitles value that you want to record only
Set rstitles = rspublishers ("Rstitles"). Value
' Loop through the titles
While not rstitles.eof
Response.Write "<LI>" & Rstitles ("title")
Rstitles.movenext
Wend
Response.Write "</UL>"
' Move to the ' next publisher
Rspublishers.movenext
Wend
Response.Write "</UL>"
Rspublishers.close
Set rspublishers = Nothing
Set rstitles = Nothing
%>
--------------------------------------------------------------
China&boy Finishing
Transfer from: "ASP3 advanced Programming"
