Updating Foreign keys in a GridView

A GridView generally shows and allows the user to update rows in a database.

They are easy to set up until the values that you want to update is a foreign key. Its not obvious how to send the appropriate value back to the database or how to limit input to valid values (from the foreign key table).

The solution to this is pleasingly simple...

You simply need to add an asp:Dropdown control that takes its values from the foreign key table and bind the value of this as you would have done an input box.

The code is shown below, the example assumes that the grid shows 'Person' records referring to a 'Job' table:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="PersonId" DataSourceID="MainGridSQL" 
AutoGenerateEditButton="true" AutoGenerateDeleteButton="true" >
            <Columns>
                <asp:BoundField DataField="PersonId" HeaderText="PersonId" ReadOnly="True" SortExpression="PersonId" />
                <asp:BoundField DataField="PersonName" HeaderText="PersonName" SortExpression="PersonName" />
                <asp:TemplateField>
                    <EditItemTemplate>
                        <asp:DropDownList ID="JobIDDropDown" SelectedValue='<%# Bind("JobId") %>' DataSourceID="DropDownSQL" 
runat="server" DataTextField="Job" DataValueField ="JobId" >
                        </asp:DropDownList>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="JobLabel" Text='<%# Eval("Job") %>' runat="server" ></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:dbConnString %>" 
SelectCommand="spSelPersonJobs" SelectCommandType="StoredProcedure" UpdateCommand="spUpdPerson" 
UpdateCommandType="StoredProcedure" ></asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:dbConnString %>" 
SelectCommand="spSelJobs" SelectCommandType="StoredProcedure"></asp:SqlDataSource>