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>
Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.

Comments

Ashok Jingar said:

Hi, How about if you want to purely just display a foreign Key value in a column? So rather than display say, CategoryID (=4), you might want to display the actual category itself (e.g. CategoryID of 4 relates to 'Website feedback'). Any help would be appreciated.

Regards, Ash

05/May/2009 10:35 AM

Add Comment

Name
Comment
 

Your comment has been received and will be shown once it passes moderation.