locked
Pass a Parameter to a Stored Procedure using ObjectDataSource RRS feed

  • Question

  • User-1700528568 posted

    Hi,

    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?

    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>

    Would appreciate any help. This is for academic purposes.

    Thanks!

    Friday, October 3, 2014 9:00 PM

Answers

  • User-1700528568 posted

    Hi.

    Tried it, didn't work.

    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>

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 6, 2014 4:58 PM

All replies

  • User-1716253493 posted

    You can set first gridview datakeynames property

    DataKeyNames = "au_id"

    Then in second Gridview DataSource, use gv1 selectedvalue as controlparameter in selectparameter

        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" >
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="au_id" 
                    PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:ObjectDataSource>

    Use SELECT button to select a row in first gridview

    Friday, October 3, 2014 9:11 PM
  • User-1700528568 posted

    Hello,

    I think you didn't understand my problem very well.

    I have a GridView which has 3 columns (au_id, au_name, titles). Titles column is a template, so I edited its template to include a GridView and an ObjectDataSource so it would be replicated for each line of the first GridView.

    In the end I would have a table similar to this:

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

    I tried what you said, but when I run the page I get a NullPointerException server error:

    Line 1433:            this.Adapter.SelectCommand = this.CommandCollection[0];
    Line 1434:            if ((au_id == null)) {
    Line 1435:                throw new global::System.ArgumentNullException("au_id");
    Line 1436:            }
    Line 1437:            else {

    So, apparently it is still passing a null parameter to GetTitlesByAuthor() function.

    I now have this code:

    <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:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="ObjectDataSource2" ForeColor="#333333" GridLines="None" ShowHeader="False" BackColor="Transparent">
                            <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="GridView1" Name="au_id" PropertyName="SelectedValue" 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>

    I appreciate your help.

    Saturday, October 4, 2014 11:58 AM
  • User-1716253493 posted
    I guess, you don't need to write code behind for it. You only need select button. CommandField or button with CommandName = "select"
    Saturday, October 4, 2014 1:05 PM
  • User-1700528568 posted

    I didn't write codebehind.

    The code presented above was shown to me when the NullReferenceException occurred.

    How do I need the Select command if I don't need the user to select anything from the GridView?

    Saturday, October 4, 2014 8:05 PM
  • User-1716253493 posted

    Try set defaulvalue

        <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" >
            <SelectParameters>
                <asp:ControlParameter ControlID="GridView1" Name="au_id" 
                    PropertyName="SelectedValue" DefaultValue="0"/>
            </SelectParameters>
        </asp:ObjectDataSource>

    Saturday, October 4, 2014 9:03 PM
  • User-1700528568 posted

    Hi.

    Tried it, didn't work.

    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>

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 6, 2014 4:58 PM