Need help with DISTINCT on single column with multiple output with multiple joins

[mrgulic]

I have a GridView (populated by MSSQL) that displays information about order line items. Each Line Item is part of an Order and each Order belongs to a Project (3 tables).

The GridView has several DropDownList's that filter the GridView results (based on user selection). I am currently attempting to filter the results of the items in the DropDownList's that have not been selected yet based on what is selected from the preceding DropDownList (cascading effect). I have the [STATUS] DropDownList working, but I ran into trouble with the [PROJECT] DropDownList.

Here is a picture (in case a visual helps)

https://www.dropbox.com/s/qv6iag0wzrotyex/Laboratory Cost Tracking - Order Items.png

(....and its test data. we don't actually order brass poles, handcuffs and nurse's uniforms )

DropDownLists:
------------
[STATUS] = Id, Status Name (received, invoiced, ordered, etc.)
[PROJECT] = Id, Project Name (project1, project2, etc.)
[ORDER] = Id, Order Number (order1, order2, etc.)
[PART] = Id, Part Name (part1, part2, etc.)


When a user selects a [STATUS] of say 'received', the GridView filters its results to reflect this, only displaying line items with that status. In addition to this I also want the items in the [PROJECT] DropDownList to be filtered to show only DISTINCT project names that have orders with line items in the status of 'received'

I have a state that works in MS SQL SMS, but does not in ASP.NET

Working on SQL SMS

SELECT DISTINCT [Order].FK_ProjectId, Project.ProjectName
FROM         OrderLineItem 
LEFT OUTER JOIN OrderLineItemStatus ON OrderLineItem.FK_StatusId = OrderLineItemStatus.OrderLineItemStatusId 
LEFT OUTER JOIN [Order] ON OrderLineItem.FK_OrderId = [Order].OrderId 
LEFT OUTER JOIN Project ON [Order].FK_ProjectId = Project.ProjectId
WHERE FK_StatusId IN ('4','5','6','7','8','10','11','12')

The DropDownList that will be supplying the value for the WHERE is like so

<asp:ListItem Value="4','5','6','7','8','9','10','11','12">(All Items)</asp:ListItem>
<asp:ListItem Value="9">(Closed Items)</asp:ListItem>
<asp:ListItem Value="4','5','6','7','8','10','11','12" Selected="True">(Open Items)</asp:ListItem>
<asp:ListItem Value="">----------------------------------------</asp:ListItem>
.......added after these static items are ALL the individual status's from the Status table.

Problem is that the ASP apparently can not see the FK_StatusId column unless its included in the output line, which of course cancels out the whole point of having the DISTINCT

<asp:SqlDataSource ID="SqlDataSource_Filter_Project" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString_SqlClient %>" 
  SelectCommand="SELECT DISTINCT pr.ProjectId, pr.ProjectName
    FROM OrderLineItem AS i 
    LEFT OUTER JOIN Project AS pr 
    RIGHT OUTER JOIN [Order] AS o ON pr.ProjectId = o.FK_ProjectId ON i.FK_OrderId = o.OrderId 
    ORDER BY pr.ProjectName"
  FilterExpression="FK_StatusId IN ('{0}')">
  <FilterParameters>
   <asp:ControlParameter Name="Status" ControlID="ctl00$MainContent$DropDownList_Filter_Status" PropertyName="SelectedValue" />
  </FilterParameters>
</asp:SqlDataSource>

Please let me know if there is any other information that I can provide that would assist you in helping me.
I have been toiling over this for 3 days will no progress. I realize that this is a very complex issue and I would not blame anyone that would choose to avoid this challenge.

Thank-you for your time and knowledge.

 

[simian336]

Why not use your

WHERE FK_StatusId IN ('4','5','6','7','8','10','11','12')

in the sql statement in ASP?

Simi

 

[mrgulic]

The WHERE condition is NOT static.
The statement used in SMS was for example purposes.