Pass a Parameter to a Stored Procedure using ObjectDataSource

Question

I am doing a simple project on Visual Studio 2013 about a book library. I have a database from which I will get the information for the GridView's.

The purpose of this page is to display the list of authors and their respective titles.

So, basically, I have a GridView which will display the author's names and IDs. I added a column to the end and edited it as a template so I could include another GridView - this one will contain the book titles for the respective author in the first GridView.

The first GridView is connected to a first ObjectDataSource which will call a function GetData() (which will return the right table to be shown on the GridView). The second GridView is connected to a second ObjectDataSource which will call a function GetTitlesByAuthor(). But, this function receives an argument: the au_id (author_ID).

My problem is: How can I pass the parameter au_id depending on the value of the au_id displayed on the first GridView rows?

EDIT: To be more clear:

I have a GridView with 3 columns (au_id, au_name, titles). I set the titles field to be a template so I could insert a gridview in the ItemTemplate section. That GridView will contain only a collumn with all the titles from a specific au_id (depending on the outter GridView row's au_id value). My problem is obtaining that value to send to the second ObjectDataSource containing a list of the titles. So my final table would be something like:

au_id    au_name    titles
--------------------------
1        Gary       A Book Title
                    Another Book I Wrote
2        Sarah      Cooking book
                    Tech Book
        ... and so on ...

This is my current code:

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" DataKeyNames="au_id" DataSourceID="ObjectDataSource1">
  <AlternatingRowStyle BackColor="White" />
  <Columns>
    <asp:BoundField DataField="au_id" HeaderText="Author ID" ReadOnly="True" SortExpression="au_id" />
    <asp:BoundField DataField="au_lname" HeaderText="LastName" SortExpression="au_lname" />
    <asp:BoundField DataField="au_fname" HeaderText="FirstName" SortExpression="au_fname" />
    <asp:TemplateField HeaderText="Titles">
      <ItemTemplate>
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="ObjectDataSource2" ForeColor="#333333" GridLines="None" ShowHeader="False" DataKeyNames="au_id">
          <AlternatingRowStyle BackColor="Transparent" />
          <Columns>
            <asp:TemplateField>
              <ItemTemplate>
                <asp:Image ID="Image1" runat="server" ImageUrl="~/Images/bullet.png" />
              </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="title" HeaderText="title" SortExpression="title" />
          </Columns>
          <EditRowStyle BackColor="#7C6F57" />
          <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
          <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
          <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
          <RowStyle BackColor="Transparent" />
          <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
          <SortedAscendingCellStyle BackColor="#F8FAFA" />
          <SortedAscendingHeaderStyle BackColor="#246B61" />
          <SortedDescendingCellStyle BackColor="#D4DFE1" />
          <SortedDescendingHeaderStyle BackColor="#15524A" />
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetTitlesByAuthor" TypeName="MyStore.DataSet1TableAdapters.AuthorTitlesTableAdapter">
          <SelectParameters>
            <asp:Parameter DefaultValue="409-56-7008" Name="author_ID" Type="String" />
          </SelectParameters>
        </asp:ObjectDataSource>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
  <EditRowStyle BackColor="#7C6F57" />
  <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
  <RowStyle BackColor="#E3EAEB" />
  <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
  <SortedAscendingCellStyle BackColor="#F8FAFA" />
  <SortedAscendingHeaderStyle BackColor="#246B61" />
  <SortedDescendingCellStyle BackColor="#D4DFE1" />
  <SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="MyStore.DataSet1TableAdapters.AuthorsTableAdapter"></asp:ObjectDataSource>

Answer 1

I didn't want to change c# code. So, to accomplish my goal I added an invisible Label next to the nested GridView, bound it to au_id and then configured the ObjectDataSource to get the parameter from the Label.

The resulting code is below:

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False" DataKeyNames="au_id" DataSourceID="ObjectDataSource1" AllowPaging="True">
  <AlternatingRowStyle BackColor="White" />
  <Columns>
    <asp:BoundField DataField="au_id" HeaderText="Author ID" ReadOnly="True" SortExpression="au_id" />
    <asp:BoundField DataField="au_lname" HeaderText="LastName" SortExpression="au_lname" />
    <asp:BoundField DataField="au_fname" HeaderText="FirstName" SortExpression="au_fname" />
    <asp:TemplateField HeaderText="Titles">
      <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Eval("au_id", "{0}") %>' Visible="False"></asp:Label>
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="ObjectDataSource2" ForeColor="#333333" GridLines="None" ShowHeader="False" BackColor="Transparent" DataKeyNames="au_id">
          <AlternatingRowStyle BackColor="Transparent" />
          <Columns>
            <asp:TemplateField>
              <ItemTemplate>
                <asp:Image ID="Image1" runat="server" ImageUrl="~/Images/bullet.png" />
              </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="title" HeaderText="title" SortExpression="title" ConvertEmptyStringToNull="False" />
          </Columns>
          <EditRowStyle BackColor="#7C6F57" />
          <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
          <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
          <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
          <RowStyle BackColor="Transparent" />
          <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
          <SortedAscendingCellStyle BackColor="#F8FAFA" />
          <SortedAscendingHeaderStyle BackColor="#246B61" />
          <SortedDescendingCellStyle BackColor="#D4DFE1" />
          <SortedDescendingHeaderStyle BackColor="#15524A" />
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetTitlesByAuthor" TypeName="MyStore.DataSet1TableAdapters.AuthorTitlesTableAdapter">
          <SelectParameters>
            <asp:ControlParameter ControlID="Label1" Name="au_id" PropertyName="Text" Type="String" />
          </SelectParameters>
        </asp:ObjectDataSource>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
  <EditRowStyle BackColor="#7C6F57" />
  <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#666666" ForeColor="#333333" HorizontalAlign="Center" />
  <RowStyle BackColor="#E3EAEB" />
  <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
  <SortedAscendingCellStyle BackColor="#F8FAFA" />
  <SortedAscendingHeaderStyle BackColor="#246B61" />
  <SortedDescendingCellStyle BackColor="#D4DFE1" />
  <SortedDescendingHeaderStyle BackColor="#15524A" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="MyStore.DataSet1TableAdapters.AuthorsTableAdapter"></asp:ObjectDataSource>

Answer 2

The Select parameters for ObjectDataSources are stored in the InputParameters array. So, the value of the author id in the row needs to be added to this array.

Try this:

protected void GVAuthor_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
           var authorId = e.Row.DataItem("au_id");
           var ods = e.Row.FindControl("ObjectDataSource2") as ObjectDataSource;
           ods.InputParamenters["author_ID"] = authorId; 
        }
    }

You also may need to remove the DataSource attribute in GridView2 in the aspx page, set it dynamically in the rowDataBound event and then call DataBind as @coder001 does.

Answer 3

ohh it's then nested gridview.I havent done work on objectdatasource but as I understand you have a main grid where you want to pass author id in child grid whuch shows books.

The way I would have done it..

protected void GVAuthor_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            Label lblauthid= (Label)e.Row.FindControl("lblCustomerID");//I dont know what is your grid source aspx so assuming it as label.               

            GridView GvBook = (GridView)e.Row.FindControl("GvBook");
            bindChildGridview(Convert.ToInt32(lblauthid.Text), GvBook); //Bind the child gridview here ..


        }
    }
 private void bindChildGridview(int authorId, GridView ChildGridview)
    {
        try
        {
            Get datasource based on authorId
            ChildGridview.DataSource = <<Your Datasource>>;                // Set DataSource Here
            ChildGridview.DataBind();
        }
        catch (Exception) { }
    }

Answer 4

Here is nice approach described in msdn you can take a look objectdatasouce parameter.