Multiple DataKeyNames not filtering sqldatasource

Question

I have two grid-views on a page both bound to an sqldatasource. The first gridview (GVMonthReport) displays data from a table
My goal is to select a row from GVMonthReport to filter three items (Location, Trans_Budget_Code, Transaction Date) in the other table(GridView2) sqldatasource.
The problem is as following: I can use one of the filtering items (Location or Trans_Budget_Code) and GridView2 filters accordingly, both work. However when i try both Location and Trans_Budget_Code together as filters nothing shows on GridView2. I can manually put the values into GridView2s sqldatasource for Trans_Budget_Code and Location and the results are what i expect. Why does that work but not when i select a row in GVMonthReport.
I think it has something to do with the GVMonthReports DataKeyNames ("Trans_Budget_Code,Location"). But so far i have had no luck. Trans_Budget_Code and Location are not primary keys is that the problem?

<asp:SqlDataSource ID="MonthUsed0" runat="server" , SelectCommand="SELECT DISTINCT dbo.[ID Inventory$].[Item Description], dbo.[ID Transactions This Week$].Location, dbo.[ID Transactions This Week$].[Trns Date], dbo.[ID Transactions This Week$].Budget, dbo.[ID Inventory$].[Item Number] FROM dbo.[ID Transactions This Week$] INNER JOIN dbo.[ID Inventory$] ON dbo.[ID Transactions This Week$].[Item Number] = dbo.[ID Inventory$].[Item Number] WHERE (dbo.[ID Transactions This Week$].Location = @Location) AND (dbo.[ID Transactions This Week$].Budget = @Trans_Budget_Code)"
UpdateCommand="UPDATE zBudgetDetails SET Budget_ID = @Budget_ID, Budget_Account_ID = @Budget_Account_ID, Amount = @Amount, Memo = @Memo WHERE (Budget_Detail_ID = @Budget_Detail_ID)">
  <SelectParameters>
    <asp:ControlParameter ControlID="GVMonthReport" Name="Location" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="GVMonthReport" Name="Trans_Budget_Code" PropertyName="SelectedValue" />
  </SelectParameters>
  <UpdateParameters>
    <asp:Parameter Name="Budget_ID" Type="Int32" />
    <asp:Parameter Name="Budget_Account_ID" Type="Int32" />
    <asp:Parameter Name="Amount" Type="Decimal" />
    <asp:Parameter Name="Memo" Type="String" />
    <asp:Parameter Name="Budget_Detail_ID" Type="Int32" />
  </UpdateParameters>
</asp:SqlDataSource>
<br />
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="MonthUsed0" style="margin-top: 1px">
  <Columns>
    <asp:BoundField DataField="Item Description" HeaderText="Item Description" SortExpression="Item Description" />
    <asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location" />
    <asp:BoundField DataField="Trns Date" HeaderText="Trns Date" SortExpression="Trns Date" />
    <asp:BoundField DataField="Budget" HeaderText="Budget" SortExpression="Budget" />
    <asp:BoundField DataField="Item Number" HeaderText="Item Number" SortExpression="Item Number" />
  </Columns>
</asp:GridView>
<br />
<br />
<br />
<asp:GridView ID="GVMonthReport" runat="server" AllowPaging="True" AllowSorting="True" DataKeyNames="Trans_Budget_Code,Location" AutoGenerateColumns="False" CellPadding="4" DataSourceID="MonthReport" ForeColor="#333333" GridLines="None" style="margin-right: 0px; margin-top: 0px;">
  <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
  <Columns>
    <asp:BoundField DataField="No#" HeaderText="No#" ReadOnly="True" SortExpression="No#" />
    <asp:BoundField DataField="Transaction Type" HeaderText="Transaction Type" ReadOnly="True" SortExpression="Transaction Type" />
    <asp:BoundField DataField="Trans_Budget_Code" HeaderText="Trans_Budget_Code" ReadOnly="True" SortExpression="Trans_Budget_Code" />
    <asp:BoundField DataField="Location" HeaderText="Location" ReadOnly="True" SortExpression="Location" />
    <asp:BoundField DataField="Supplier/Employee Name" HeaderText="Supplier/Employee Name" ReadOnly="True" SortExpression="Supplier/Employee Name" />
    <asp:BoundField DataField="Project Code" HeaderText="Project Code" ReadOnly="True" SortExpression="Project Code" />
    <asp:BoundField DataField="WOID" HeaderText="WOID" ReadOnly="True" SortExpression="WOID" />
    <asp:BoundField DataField="Transaction Date" HeaderText="Transaction Date" ReadOnly="True" SortExpression="Transaction Date" />
    <asp:BoundField DataField="Craft" HeaderText="Craft" ReadOnly="True" SortExpression="Craft" />
    <asp:BoundField DataField="Transaction Description" HeaderText="Transaction Description" ReadOnly="True" SortExpression="Transaction Description" />
    <asp:BoundField DataField="Purpose Description" HeaderText="Purpose Description" ReadOnly="True" SortExpression="Purpose Description" />
    <asp:BoundField DataField="Quantity" HeaderText="Quantity" ReadOnly="True" SortExpression="Quantity" />
    <asp:BoundField DataField="Unit Cost" HeaderText="Unit Cost" ReadOnly="True" SortExpression="Unit Cost" />
    <asp:BoundField DataField="Total Cost" HeaderText="Total Cost" ReadOnly="True" SortExpression="Total Cost" />
    <asp:BoundField DataField="Purchased by" HeaderText="Purchased by" ReadOnly="True" SortExpression="Purchased by" />
    <asp:CommandField ShowSelectButton="True" />
  </Columns>
  <EditRowStyle BackColor="#999999" />
  <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
  <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
  <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
  <SortedAscendingCellStyle BackColor="#E9E7E2" />
  <SortedAscendingHeaderStyle BackColor="#506C8C" />
  <SortedDescendingCellStyle BackColor="#FFFDF8" />
  <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="MonthReport" runat="server" SelectCommand="SELECT No#, [Transaction Type], Trans_Budget_Code, Location, [Supplier/Employee Name], [Project Code], WOID, [Transaction Date], Craft, [Transaction Description], [Purpose Description], Quantity, [Unit Cost], [Total Cost], [Purchased by] FROM dbo.[MD Transactions] WHERE ([WO Budget Code] = @Acount) AND (YEAR([Transaction Date]) = @yeardate) AND (MONTH([Transaction Date]) = @monthdate) AND (Trans_Budget_Code = 'Maintenance') UNION ALL SELECT No#, [Transaction Type], Trans_Budget_Code, Location, [Supplier/Employee Name], [Project Code], WOID, [Transaction Date], Craft, [Transaction Description], [Purpose Description], Quantity, [Unit Cost], [Total Cost], [Purchased by] FROM dbo.[MD Transactions This Week$] WHERE ([WO Budget Code] = @Acount) AND (YEAR([Transaction Date]) = @yeardate) AND (MONTH([Transaction Date]) = @monthdate) AND (Trans_Budget_Code = 'Maintenance')"
UpdateCommand="UPDATE zBudgetDetails SET Budget_ID = @Budget_ID, Budget_Account_ID = @Budget_Account_ID, Amount = @Amount, Memo = @Memo WHERE (Budget_Detail_ID = @Budget_Detail_ID)">
  <SelectParameters>
    <asp:ControlParameter ControlID="DDLMonthAcount" Name="Acount" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="DDLYear" Name="yeardate" PropertyName="SelectedValue" />
    <asp:ControlParameter ControlID="DDLMonth" Name="monthdate" PropertyName="SelectedValue" />
  </SelectParameters>
  <UpdateParameters>
    <asp:Parameter Name="Budget_ID" Type="Int32" />
    <asp:Parameter Name="Budget_Account_ID" Type="Int32" />
    <asp:Parameter Name="Amount" Type="Decimal" />
    <asp:Parameter Name="Memo" Type="String" />
    <asp:Parameter Name="Budget_Detail_ID" Type="Int32" />
  </UpdateParameters>
</asp:SqlDataSource>

Code Snippet was the only why i could get it to look good. Running it will not do anything. Sorry for the inconvenience — Itomship
– Itomship, Commented May 18, 2015 at 17:33

R.C · Accepted Answer · 2015-05-19 10:55:06Z

3

Possible issue seems with the settings defined for <asp:SqlDataSource ID="MonthUsed0" .../>

The PropertyName attribute of the <asp:ControlParameter .../> should be set as shown below

<SelectParameters>
    <asp:ControlParameter ControlID="GVMonthReport" Name="Location"
         PropertyName="SelectedDataKey.Values[1]" />
    <asp:ControlParameter ControlID="GVMonthReport" 
         Name="Trans_Budget_Code" PropertyName="SelectedDataKey.Values[0]" />
  </SelectParameters>

Here the [0] and [1] represents the position of Keys defined in DataKeyNames property of GridView.

Setting PropertyName="SelectedValue" will only use the value of FIRST Column defined in DataKeyNames property.

You can explicitly set the PropertyName parameter to a Column name as:

PropertyName="SelectedDataKey.Values[&quot;Location&quot;]"

answered May 19, 2015 at 10:55

R.C

10.6k2 gold badges38 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple DataKeyNames not filtering sqldatasource

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related