2. ASPRunnerPro General questions
  3. Master Detail Join error
This topic is locked

Master Detail Join error

 		5/29/2004 2:32:59 AM
ASPRunnerPro General questions
T
 Toad author

I have two tables dbo.Orders and dbo.Attempt, each with numerous inner joins to get the appropriate FK lookups. Each table has a maseter details relatonship with the other and since I added the lookups I am now gettting errors for each.
Error description [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'AttemptID'.

URL /Orders_list.asp

SQL query SELECT O.OrderID, O.TicketID, O.AttemptID,A.EventID AS EventID, E.Ratio1 AS Ratio1, O.ContractCode, O.OrderLegCode,O.MarketActionCode,O.OrderVolume,O.OrderPrice,O.OrderTypeCode,O.S

tatus,O.FilledVolume, O.OriginalOrderDateTime,O.PlaceOrderDateTime,O.Monitorflag FROM Orders O INNER JOIN Attempt A ON O.AttemptID = A.AttemptID INNER JOIN Event E ON A.EventID = E.EventID where AttemptID = 1
Error description [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'AttemptID'.

URL /Attempt_list.asp

SQL query SELECT T.AssetCode AS AssetCode, E.ContractCode, E.Ratio1 AS Ratio1, A.AttemptID,A.EventID,L.AttemptLevelName, M.MarketActionName, S.StatusName,A.AttemptVolume, A.EntryPrice,A.StopPrice,A.BEPrice,A.ProfitPrice, A.CurrentOP,A.FilledEntryVolume,A.FilledProfitVolume,A.FilledStopLossVolume FROM Attempt A INNER JOIN AttemptLevel L ON A.AttemptLevelCode= L.AttemptLevelCode INNER JOIN MarketAction M ON A.MarketActionCode= M.MarketActionCode INNER JOIN AttemptState S ON A.Status=S.Status INNER JOIN Event E ON A.EventID = E.EventID INNER JOIN Asset T ON E.AssetCode = T.AssetCode where AttemptID = 1
I can get rid of the second error by changing AttemptID to A.AttemptID in AttemptList.jsp strMasterSQL.

However nothing will fix the first error above for the Attempt table when I click on the dbo.Orders detail link.

Any suggestions?
T
 		Toad author 5/30/2004

I might be mistaken but is seams that once I start using large INNER JOIN queries for FK lookups the search functionality breaks down.
As an example for my Orders table: I have an FK lookup for the Status field. My generated ASP Orders table displays the Status Name (not the status code value). However when I try to search for all orders with StatusName="Filled" it fails because StatusName does not exist in the orders table. It is an FK lookup in the OrderState table.
When I try to search on the StatusName the folllowing query is generated.
SELECT Orders.OrderID, Orders.TicketID, A.AttemptID, Orders.FK_Prod_Alias, Orders.ContractCode, L.OrderLegName, M.MarketActionName, Orders.OrderVolume, Orders.OrderPrice, T.OrderTypeName, S.StatusName, Orders.FilledVolume, Orders.OriginalOrderDateTime, Orders.PlaceOrderDateTime, Orders.MonitorFlag FROM Orders INNER JOIN Attempt A ON Orders.AttemptID = A.AttemptID INNER JOIN MarketAction M ON Orders.MarketActionCode = M.MarketActionCode INNER JOIN OrderLegs L ON Orders.OrderLegCode = L.OrderLegCode INNER JOIN OrderType T ON Orders.OrderTypeCode = T.OrderTypeCode INNER JOIN OrderState S ON Orders.Status = S.Status where Status=Filled order by StatusName asc
Is there some way around this or was the application not designed for this functionality? Would there be a possible hack for the ASP's or do the FK's need to be completely discarded?
Thank you.
Sergey Kornilov admin 6/1/2004

I would recommend you to create a view in SQL Server on the top of this INNER JOIN query. After that you can use this view as a datasource in ASPRunner.
This way you pull data from several joined tables without breaking search functionality.
T
 		Toad author 6/5/2004

The view has worked for the INNER JOIN issues.
However, when using the view the Master / detail relationship no longer shows up on the web page. Is there a workaround for this?
Sergey Kornilov admin 6/5/2004

Toad,
please explain what exactly is the problem with view and master-detail relationships.
T
 		Toad author 6/5/2004

Everything appears as normal in ASPRunner when I set up the maseter relationship.

However, since I have begun using views to access data, the master/ detail link is no longer appearing on the left hand side of Orders_list.asp screen when the page is viewed in a browser.
No error, it is just completely gone. I am using the same master detail relationships that were used for identical tables before I converted them to views.
Hopefully this is a simple fix. Please let me know if more detail is needed.
T
 		Toad author 6/5/2004

Sergey, it seams that this was an IE issues and that the previous asp's must have still been cached in the browser. After removing all temp internet files and rebuilding again it looks like the detail relationships are indeed working.