Stone Coast Web Design's Weblog

This example shows an example of linking controls together, this time providing a master/details view. The first grid, a GridView, shows the basic details of a product. The second control, a DetailsView is bound to a SqlDataSource, but with the data source having a SelectParameter bound to the SelectedValue property of the first grid. This means that when no row is selected on the first grid, the DetailsView is not shown as there is no data to bind to. Once a row is selected, however, the SelectedValue holds the ProductID and the DetailsView only shows data for that selected product.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
    SelectCommand="SELECT ProductID, ProductName FROM Products" />
    
  <asp:GridView ID="GridView1" runat="server"
    AllowPaging="true" PageSize="5"
    DataKeyNames="ProductID" AutoGenerateColumns="true"
    DataSourceID="SqlDataSource1">
    <Columns>
      <asp:CommandField ShowSelectButton="true" />
    </Columns>
  </asp:GridView>
  
  <br /><br />
  
  <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
    DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
    InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], 
[UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, 
@UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"
SelectCommand="SELECT Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, 
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, 
Products.Discontinued, Suppliers.CompanyName, Categories.CategoryName FROM Products INNER JOIN Suppliers 
ON Products.SupplierID = Suppliers.SupplierID INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID 
WHERE (Products.ProductID = @ProductID)"
UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = 
@UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, 
[ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID">
    <DeleteParameters>
      <asp:Parameter Name="ProductID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
      <asp:Parameter Name="ProductName" Type="String" />
      <asp:Parameter Name="SupplierID" Type="Int32" />
      <asp:Parameter Name="CategoryID" Type="Int32" />
      <asp:Parameter Name="QuantityPerUnit" Type="String" />
      <asp:Parameter Name="UnitPrice" Type="Decimal" />
      <asp:Parameter Name="UnitsInStock" Type="Int16" />
      <asp:Parameter Name="UnitsOnOrder" Type="Int16" />
      <asp:Parameter Name="ReorderLevel" Type="Int16" />
      <asp:Parameter Name="Discontinued" Type="Boolean" />
      <asp:Parameter Name="ProductID" Type="Int32" />
    </UpdateParameters>
    <SelectParameters>
      <asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue"
        Type="Int32" />
    </SelectParameters>
    <InsertParameters>
      <asp:Parameter Name="ProductName" Type="String" />
      <asp:Parameter Name="SupplierID" Type="Int32" />
      <asp:Parameter Name="CategoryID" Type="Int32" />
      <asp:Parameter Name="QuantityPerUnit" Type="String" />
      <asp:Parameter Name="UnitPrice" Type="Decimal" />
      <asp:Parameter Name="UnitsInStock" Type="Int16" />
      <asp:Parameter Name="UnitsOnOrder" Type="Int16" />
      <asp:Parameter Name="ReorderLevel" Type="Int16" />
      <asp:Parameter Name="Discontinued" Type="Boolean" />
    </InsertParameters>
  </asp:SqlDataSource>
  
  <asp:DetailsView ID="DetailsView1" runat="server"
    AutoGenerateRows="False" DataSourceID="SqlDataSource2"
     GridLines="None">
    <Fields>

      <asp:BoundField DataField="ProductName"
        HeaderText="Name" SortExpression="ProductName" />

      <asp:TemplateField HeaderText="Supplier">
        <ItemTemplate>
          <%#Eval("CompanyName") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:SqlDataSource ID="Sds1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
            SelectCommand="SELECT SupplierID, CompanyName FROM Suppliers ORDER BY CompanyName" />
          <asp:DropDownList ID="SupplierID" runat="server"
            DataSourceId="Sds1"
            DataValueField="SupplierID" DataTextField="CompanyName"
            SelectedValue='<%#Bind("SupplierID")%>' />
        </EditItemTemplate>
      </asp:TemplateField>

      <asp:TemplateField HeaderText="Category" >
        <ItemTemplate>
          <%#Eval("CategoryName") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:SqlDataSource ID="Sds2" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
            SelectCommand="SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName" />
          <asp:DropDownList ID="CategoryID" runat="server"
            DataSourceID="Sds2"
            DataValueField="CategoryID" DataTextField="CategoryName"
            SelectedValue='<%#Bind("CategoryID")%>' />
        </EditItemTemplate>
      </asp:TemplateField>

      <asp:BoundField DataField="QuantityPerUnit"
        HeaderText="Quantity Per Unit" SortExpression="QuantityPerUnit" />
      <asp:BoundField DataField="UnitPrice"
        HeaderText="Unit Price" SortExpression="UnitPrice" />
      <asp:BoundField DataField="UnitsInStock"
        HeaderText="Units In Stock" SortExpression="UnitsInStock" />
      <asp:BoundField DataField="UnitsOnOrder"
        HeaderText="Units On Order" SortExpression="UnitsOnOrder" />
      <asp:BoundField DataField="ReorderLevel"
        HeaderText="Reorder Level" SortExpression="ReorderLevel" />
      <asp:CheckBoxField DataField="Discontinued"
        HeaderText="Discontinued" SortExpression="Discontinued" />
      <asp:CommandField ShowDeleteButton="True"
        ShowEditButton="True" ShowInsertButton="True" />
    </Fields>
    <HeaderStyle BackColor="Green" />
    <FieldHeaderStyle BackColor="LightGray" Height="20px" Width="110px"/>
  </asp:DetailsView>