2. ASPRunnerPro General questions
  3. Master/Detail relationship
This topic is locked

Master/Detail relationship

 		3/8/2007 10:35:22 AM
ASPRunnerPro General questions
T
 tgunther author

I have a table that shows me totals for some of our finances, I also have a table that shows the individual items that make of the totals. I am trying to make the Totals table be a master table to the details table based on three fields in the totals table: metricdescription, month and year. I was able to get it to link based off of one field (MetricDescription) using the master detail relationship off of the Datasource tables menu and then I attempted to place this code on the Details list page as an on load function:

Sub ListOnLoad()
set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.open "select [MetricDescription], [Month], [Year] from [dbo].[SS-Metrics-Totals] where [MetricDescription] = " & SESSION("masterkey"), dbConnection
Session("MasterField1") = rstmp("Month")

Session("MasterField2") = rstmp("Year")
rstmp.close : set rstmp = nothing
End Sub



I then made the year and month fields on the details with default values: Session("MasterField1") for month and Session("MasterField2") for the year. Unfortunately I have not been able to get this to work. Am I going in the right direction or do I need to make the change somewhere else? I am using ASP Runner Pro 4.1 build 147 if that makes a difference.
Sergey Kornilov admin 3/8/2007

Try the following:

Sub ListOnLoad()

set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.open "select [MetricDescription], [Month], [Year] from [dbo].[SS-Metrics-Totals] where [MetricDescription] = " & SESSION(strTableName & "_masterkey1"), dbConnection
Session("MasterField1") = rstmp("Month")

Session("MasterField2") = rstmp("Year")
rstmp.close : set rstmp = nothing
End Sub
T
 		tgunther author 3/9/2007

Try the following:


Unfortunately this did not work either. When I apply this code to the onload parameter of the list page, I get an error from the primary key. This is the error that I get:

Error number -2147217900

Error description [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.

URL /SSMetrics/SS-metrics_overview/SS-Metrics_Totals/SS_Metrics_Overview_list.asp

SQL query select [MetricDecription], [MetricLevelDescription], [SiteLocation], [Budget], [Year], [Amount1], [Month], [UID], [MetricTypeDesc], [SiteUID], [TypeUID] From [dbo].[SS-Metrics_Overview] where ([dbo].[SS-Metrics_Overview].[MetricDecription]='Current Month Sales $K')



However, when I remove the code from the list page onload parameter the primary key works, however it gives me too much information, thus why I am trying to figure out how to have it filter by two additional fields. Thank you for you help, it is greatly appreciated.
J
 		Jane 3/12/2007

If MetricDescription is the text field you need to use single quotes around it:

Sub ListOnLoad()

set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.open "select [MetricDescription], [Month], [Year] from [dbo].[SS-Metrics-Totals] where [MetricDescription] = '" & Session(strTableName & "_masterkey1")&"'", dbConnection
Session("MasterField1") = rstmp("Month")

Session("MasterField2") = rstmp("Year")
rstmp.close : set rstmp = nothing
End Sub
T
 		tgunther author 3/20/2007

If MetricDescription is the text field you need to use single quotes around it:



Unfortunately I still am unable to get this to work. I tried all of the methods suggested and nothing seems to work. It still only filters my detail page based off of one master field (metric description). Thank you for all of your suggestions, do you have any other ideas how to solve this?