Trying to add a parameter to my sqldatasource during rowDataBound

Question

So I have a gridview within a gridview (I have a one to many table) my first gridview is working well, but my second gridview has a sqldatasource that has a select parameter(the default value was just for testing)

<asp:SqlDataSource ID="dsCountryByTripID" runat="server" 
        ConnectionString="<%$ ConnectionStrings:bahDatabase %>" 
        SelectCommand="spSelectCitiesByTripID" SelectCommandType="StoredProcedure">
        <SelectParameters>
        <asp:Parameter Type="Int32" Name="tripID"  DefaultValue="56" />
        </SelectParameters>
    </asp:SqlDataSource>

during my Gridview1 row databound I am trying to grab the columns that match the tripID. But dsCountryByTripID which is my datasource, is only going inputted with the last tripID.

 protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
    {
        GridView gv2 = (GridView)e.Row.FindControl("GridView2");
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            dsCountryByTripID.SelectParameters.Clear();
            DataRowView drv = (DataRowView)e.Row.DataItem;
            string tripID = (drv["pkiTripId"]).ToString();
            dsCountryByTripID.SelectParameters.Add("tripID", DbType.Int32, tripID);
            //gv2.DataBind();
            //e.Row.DataBind();

        }
    }

Answer 1

Hi,

here you can read why it only executes the last "tripId": http://msdn.microsoft.com/en-us/library/tw738475(VS.80).aspx
The sql datasource only gets executed at the end of the page, so every time you go through a row the tripID is overriden.

Do you really need to use the datasource? Can't you use ado.net or something different for you dataaccess?

Hope this helps.

Answer 2

Hi Spooks,

First, we need to clarify the structure of your page. It seems to me that dsCountryByTripID is declared outside of Gridview1, instead of inside Gridview1. That's probably why your dsCountryByTripID is only going inputted with the last tripID.

In order to do what you want, the structure should be like this:

<asp:gridview id="gv1" runat="server" DataSourceID="ds1">
...
    <asp:gridview id="gv2nested" runat="server" DataSourceID="dsCountryByTripID">
    </asp:gridview>
    <asp:SqlDataSource ID="dsCountryByTripID" runat="server" 
    ConnectionString="<%$ ConnectionStrings:bahDatabase %>" 
    SelectCommand="spSelectCitiesByTripID" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter Type="Int32" Name="tripID"  DefaultValue="56" />
        </SelectParameters>
    </asp:SqlDataSource>
...
</asp:gridview>
<asp:SqlDataSource ID="ds1" runat="server" >
</asp:SqlDataSource>

Next, to assign the correct value to the inner datasource selectparameter, you can do that in the outer gridview (gv1) RowCreated event handler.

protected void gv1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        //Retrieve the inner gridview
        //Retrieve the inner sqldatasource
        //Retrieve and assign selectparameter value
    }

It's similar to the C# code you posted, but you need to additionally use FindControl to retrieve the now nested sqldatasource (dsCountryByTripID) before assigning the value to the selectparameter. (Trying to teach a man fishing here, but let me know if you need more help or if the above is not your case :)