use GridView boundfield value for DropDownList select where clause

[Dabbler]

I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
an SqlDataSource, then have a value from a boundfield in the row be passed as
the keyfield for select where clause. Im trying to load the ddl with a list
of dates from another table keyed on GridView row field that only apply to
this row.

Any suggestions would be appreciated.

[Phillip Williams]

I have done it in this demo using an ObjectDataSource; where one of the
properties in the databound business class is a collection of the list items
specific to each row.

http://www.webswapp.com/codesamples/...w/default.aspx

If you want to do it using SQLDataSource, you will have to either intervene
during the RowUpdated event to trigger another SQL query that returns a list
of item that you bind to the dropdownlist or you can add the field that
determines the listitems selection process in the GridView. DataKeyNames then
set a SqlDataSource within the ItemTemplate with a parameter referencing that
datakey, e.g.

<asp:ControlParameter ControlID="GridView1" Name="CategoryID"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
an SqlDataSource, then have a value from a boundfield in the row be passed as
the keyfield for select where clause. Im trying to load the ddl with a list
of dates from another table keyed on GridView row field that only apply to
this row.

Any suggestions would be appreciated.

[Dabbler]

Thanks Phillip, I studied the example with ObjectDataSource but was hoping I
could implement this just using SqlDataSource.

I tried your selectedkeys.values[1] but still don't get any data in the ddl.
Here's my code:
<asp:GridView ID="RegistrantsGridView" runat="server"
DataSourceID="RegistrantsSqlDataSource"
AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
CssClass="waGridView"
DataKeyNames="RegistrantId" ... >
<Columns>
<asp:BoundField DataField="RegistrantId" HeaderText="Id"
InsertVisible="False" ReadOnly="True" SortExpression="RegistrantId" />
<asp:TemplateField>
<ItemTemplate>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="RegistrantsGridView"
Name="RegistrantId"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>

"Phillip Williams" wrote:

I have done it in this demo using an ObjectDataSource; where one of the
properties in the databound business class is a collection of the list items
specific to each row.

http://www.webswapp.com/codesamples/...w/default.aspx

If you want to do it using SQLDataSource, you will have to either intervene
during the RowUpdated event to trigger another SQL query that returns a list
of item that you bind to the dropdownlist or you can add the field that
determines the listitems selection process in the GridView. DataKeyNames then
set a SqlDataSource within the ItemTemplate with a parameter referencing that
datakey, e.g.

<asp:ControlParameter ControlID="GridView1" Name="CategoryID"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
an SqlDataSource, then have a value from a boundfield in the row be passed as
the keyfield for select where clause. Im trying to load the ddl with a list
of dates from another table keyed on GridView row field that only apply to
this row.

Any suggestions would be appreciated.

[Phillip Williams]

You are right. I just tried it and it does not work. I forgot that while the
GridViewRow is being created there are no selected rows yet to have values in
the SelectedDataKey.

However I tried this modification and it worked:

<asp:TemplateField>
<ItemTemplate>
<asp:Label id="lblRegistrantId" runat="Server"
Text='<%#Bind("RegistrantId")%' cssClass="hidden"></asp:Label>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblRegistrantId"
Name="RegistrantId"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>
where the style class definition would be:
..hidde {display:none;}
--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

Thanks Phillip, I studied the example with ObjectDataSource but was hoping I
could implement this just using SqlDataSource.

I tried your selectedkeys.values[1] but still don't get any data in the ddl.
Here's my code:
<asp:GridView ID="RegistrantsGridView" runat="server"
DataSourceID="RegistrantsSqlDataSource"
AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
CssClass="waGridView"
DataKeyNames="RegistrantId" ... >
<Columns>
<asp:BoundField DataField="RegistrantId" HeaderText="Id"
InsertVisible="False" ReadOnly="True" SortExpression="RegistrantId" />
<asp:TemplateField>
<ItemTemplate>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="RegistrantsGridView"
Name="RegistrantId"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>

"Phillip Williams" wrote:

I have done it in this demo using an ObjectDataSource; where one of the
properties in the databound business class is a collection of the list items
specific to each row.

http://www.webswapp.com/codesamples/...w/default.aspx

If you want to do it using SQLDataSource, you will have to either intervene
during the RowUpdated event to trigger another SQL query that returns a list
of item that you bind to the dropdownlist or you can add the field that
determines the listitems selection process in the GridView. DataKeyNames then
set a SqlDataSource within the ItemTemplate with a parameter referencing that
datakey, e.g.

<asp:ControlParameter ControlID="GridView1" Name="CategoryID"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
an SqlDataSource, then have a value from a boundfield in the row be passed as
the keyfield for select where clause. Im trying to load the ddl with a list
of dates from another table keyed on GridView row field that only apply to
this row.

Any suggestions would be appreciated.

[Dabbler]

Ok, I got it to work with your suggestion to load the ddl in code instead of
using SqlDataSource. I used RowDataBound event, so I could pull the key value
from a cell using e.Row.Cells[1].Text. I think the RowUpdatedEvent doesn't
trigger while the Gridview is loading, which is what i needed to load the
initial ddl values.

Thanks for the tip!

"Phillip Williams" wrote:

I have done it in this demo using an ObjectDataSource; where one of the
properties in the databound business class is a collection of the list items
specific to each row.

http://www.webswapp.com/codesamples/...w/default.aspx

If you want to do it using SQLDataSource, you will have to either intervene
during the RowUpdated event to trigger another SQL query that returns a list
of item that you bind to the dropdownlist or you can add the field that
determines the listitems selection process in the GridView. DataKeyNames then
set a SqlDataSource within the ItemTemplate with a parameter referencing that
datakey, e.g.

<asp:ControlParameter ControlID="GridView1" Name="CategoryID"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
an SqlDataSource, then have a value from a boundfield in the row be passed as
the keyfield for select where clause. Im trying to load the ddl with a list
of dates from another table keyed on GridView row field that only apply to
this row.

Any suggestions would be appreciated.

[Dabbler]

Interesting that this worked for you. The only difference between this and my
last failed attempt using SqlDataSource is that you are using a label control
in an item template, whereas I was using the RegistrantId in a visible
BoundField. I'm guessing that the BoundFields have mangeled Id names instead
of the original Id names.

"Phillip Williams" wrote:

You are right. I just tried it and it does not work. I forgot that while the
GridViewRow is being created there are no selected rows yet to have values in
the SelectedDataKey.

However I tried this modification and it worked:

<asp:TemplateField>
<ItemTemplate>
<asp:Label id="lblRegistrantId" runat="Server"
Text='<%#Bind("RegistrantId")%' cssClass="hidden"></asp:Label>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblRegistrantId"
Name="RegistrantId"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>
where the style class definition would be:
.hidde {display:none;}
--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

Thanks Phillip, I studied the example with ObjectDataSource but was hoping I
could implement this just using SqlDataSource.

I tried your selectedkeys.values[1] but still don't get any data in the ddl.
Here's my code:
<asp:GridView ID="RegistrantsGridView" runat="server"
DataSourceID="RegistrantsSqlDataSource"
AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
CssClass="waGridView"
DataKeyNames="RegistrantId" ... >
<Columns>
<asp:BoundField DataField="RegistrantId" HeaderText="Id"
InsertVisible="False" ReadOnly="True" SortExpression="RegistrantId" />
<asp:TemplateField>
<ItemTemplate>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="RegistrantsGridView"
Name="RegistrantId"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>

"Phillip Williams" wrote:

I have done it in this demo using an ObjectDataSource; where one of the
properties in the databound business class is a collection of the list items
specific to each row.

http://www.webswapp.com/codesamples/...w/default.aspx

If you want to do it using SQLDataSource, you will have to either intervene
during the RowUpdated event to trigger another SQL query that returns a list
of item that you bind to the dropdownlist or you can add the field that
determines the listitems selection process in the GridView. DataKeyNames then
set a SqlDataSource within the ItemTemplate with a parameter referencing that
datakey, e.g.

<asp:ControlParameter ControlID="GridView1" Name="CategoryID"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

> I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
> an SqlDataSource, then have a value from a boundfield in the row be passed as
> the keyfield for select where clause. Im trying to load the ddl with a list
> of dates from another table keyed on GridView row field that only apply to
> this row.
>
> Any suggestions would be appreciated.
>

[Phillip Williams]

Yes, DataBound fields do not have an ID property. If you right-mouse click
on the browser to view the rendered HTML you will see that the bound field
rendered the content of your the databound field within a TableCell.
Therefore you could not refer to it. However by specifying a templateField
in which I added a server control (the label) containing the RegistrantId, I
managed to refer to that control by its ID.
--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

Interesting that this worked for you. The only difference between this and my
last failed attempt using SqlDataSource is that you are using a label control
in an item template, whereas I was using the RegistrantId in a visible
BoundField. I'm guessing that the BoundFields have mangeled Id names instead
of the original Id names.

"Phillip Williams" wrote:

You are right. I just tried it and it does not work. I forgot that while the
GridViewRow is being created there are no selected rows yet to have values in
the SelectedDataKey.

However I tried this modification and it worked:

<asp:TemplateField>
<ItemTemplate>
<asp:Label id="lblRegistrantId" runat="Server"
Text='<%#Bind("RegistrantId")%' cssClass="hidden"></asp:Label>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblRegistrantId"
Name="RegistrantId"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>
where the style class definition would be:
.hidde {display:none;}
--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com
"Dabbler" wrote:

Thanks Phillip, I studied the example with ObjectDataSource but was hoping I
could implement this just using SqlDataSource.

I tried your selectedkeys.values[1] but still don't get any data in the ddl.
Here's my code:
<asp:GridView ID="RegistrantsGridView" runat="server"
DataSourceID="RegistrantsSqlDataSource"
AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
CssClass="waGridView"
DataKeyNames="RegistrantId" ... >
<Columns>
<asp:BoundField DataField="RegistrantId" HeaderText="Id"
InsertVisible="False" ReadOnly="True" SortExpression="RegistrantId" />
<asp:TemplateField>
<ItemTemplate>
<asp:DropDownList ID="RideDates" runat="server"
DataSourceId="RideDatesSqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="RideDatesSqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:myDB %>"
ProviderName="System.Data.SqlClient"
SelectCommand="GetRegRideDates" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="RegistrantsGridView"
Name="RegistrantId"
PropertyName="SelectedDataKey.Values[1]" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</ItemTemplate>
</TemplateField>

"Phillip Williams" wrote:

> I have done it in this demo using an ObjectDataSource; where one of the
> properties in the databound business class is a collection of the list items
> specific to each row.
>
> http://www.webswapp.com/codesamples/...w/default.aspx
>
> If you want to do it using SQLDataSource, you will have to either intervene
> during the RowUpdated event to trigger another SQL query that returns a list
> of item that you bind to the dropdownlist or you can add the field that
> determines the listitems selection process in the GridView. DataKeyNames then
> set a SqlDataSource within the ItemTemplate with a parameter referencing that
> datakey, e.g.
>
> <asp:ControlParameter ControlID="GridView1" Name="CategoryID"
> PropertyName="SelectedDataKey.Values[1]" Type="Int32" />
>
> --
> HTH,
> Phillip Williams
> http://www.societopia.net
> http://www.webswapp.com
>
>
> "Dabbler" wrote:
>
> > I have a dropdownlist in a GridView ItemTemplate. I need to bind the ddl to
> > an SqlDataSource, then have a value from a boundfield in the row be passed as
> > the keyfield for select where clause. Im trying to load the ddl with a list
> > of dates from another table keyed on GridView row field that only apply to
> > this row.
> >
> > Any suggestions would be appreciated.
> >