use GridView boundfield value for DropDownList select where clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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/aspnet20/dropdownlist_gridview/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" />
 
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 said:
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/aspnet20/dropdownlist_gridview/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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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/aspnet20/dropdownlist_gridview/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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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/aspnet20/dropdownlist_gridview/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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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/aspnet20/dropdownlist_gridview/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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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>

:

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/aspnet20/dropdownlist_gridview/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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top