Help!! Hierarchical Data SQL Problem

[AnthonyJ20]

Hello, I'm in the process of trying to display hierarchical data just using two tables. This is my first time doing this. I found this script that accmplishes the taks without .Net using the Northwinds DB. Can someone help me break this down so that I can use it with just two tables? Right now I'm getting the error: Too few parameters. Expected 1, and I can't find it. I'm sure it has something to do with there not being a third table.

My tables are:

Customerboxes: The primary key is CustomerID. This is the parent table

tblDataTableNew: The ID which is the same as CustomerID is called Datatable. This is the child table.


SQL Statement

'-----------------------------------------------------------
'--- Generate the SQL to share customers,
'--- their orders and their line items.
'-----------------------------------------------------------
strSQL = " SHAPE("
strSQL = strSQL & " SHAPE"
strSQL = strSQL & " {"
strSQL = strSQL & " SELECT top 18 CustomerID, CustomerName, BoxNumber"
strSQL = strSQL & " FROM CustomerBoxes"
strSQL = strSQL & " }"
strSQL = strSQL & " APPEND"
strSQL = strSQL & " ("
strSQL = strSQL & " ("
strSQL = strSQL & " SHAPE"
strSQL = strSQL & " {"
strSQL = strSQL & " SELECT DataTable,"
strSQL = strSQL & " VolDate"
strSQL = strSQL & " FROM tblDataTableNew"
strSQL = strSQL & " ORDER BY VolDate ASC"
strSQL = strSQL & " }"
strSQL = strSQL & " APPEND"
strSQL = strSQL & " ("
strSQL = strSQL & " {"
strSQL = strSQL & " SELECT t.DataTable, c.BoxNumber AS [Product],"
strSQL = strSQL & " t.ImageItems, t.FullPay,"
strSQL = strSQL & " t.PartialPay, "
strSQL = strSQL & " (t.SLC * t.ImageItems) AS [Extended Price]"
strSQL = strSQL & " FROM [tblDataTableNew] t INNER JOIN tblDataTableNew c"
strSQL = strSQL & " ON t.DataTable = c.CustomerID"
strSQL = strSQL & " ORDER BY t.BoxNumber"
strSQL = strSQL & " }"
strSQL = strSQL & " RELATE CustomerID TO DataTable"
strSQL = strSQL & " ) AS rsLineItems,"
strSQL = strSQL & " COUNT(rsLineItems.Product) AS [Items On Order],"
strSQL = strSQL & " SUM(rsLineItems.[Extended Price]) AS [Order Total]"
strSQL = strSQL & " ) RELATE CustomerID TO DataTable"
strSQL = strSQL & " ) AS rsOrders,"
strSQL = strSQL & " SUM(rsOrders.[Order Total]) AS [Total Amount]"
strSQL = strSQL & " ) AS rsCustomers"


'--- Create and open the topmost parent recordset (Customers)
set objCustomerRS = server.CreateObject("ADODB.Recordset")
objCustomerRS.Open strSQL, objConn

 

[guitardave78]

what else is in the page, any functions you are calling?

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[AnthonyJ20]

Thanks for the response. Yes, I have two. Here they are:

'--- Create and open the topmost parent recordset (Customers)
set objCustomerRS = server.CreateObject("ADODB.Recordset")
objCustomerRS.Open strSQL, objConn

Function FormatValue(objField)
'--- Format the values
If IsCurrency(objField.Type) then
FormatValue = FormatCurrency(objField.Value, 2)
Else
FormatValue = objField.Value
End If
End Function

Function IsCurrency(intType)
'--- Determine if the datatype needs decimals,
'--- we'll call it currency for this page.
select case intType
case adDouble, adCurrency
IsCurrency = True
case else
IsCurrency = False
end select
End Function
%>