Editing and Displaying Database Values through a DropDownList in GridView

• Download source - 919 B

Introduction

This article will be very useful for anyone who is working with the GridView control. I will try and explain how a dropdownlist can be added into a GridView Column to display and edit database values. The DropDownList will have an OnSelectedIndexChanged event which is used to update the database without the need of an edit button. The code that is behind the function is very simple but it is assumed that you already have a GridView created with fields populated.

Background

The background to this article is to try and show how a dropdownlist can be used inside a GridView. I wanted to find a solution to showing database values in a dropdownlist in a gridview and then how to edit these values without the user having to navigate off to another page i.e. using an edit button. The dropdownlist would contain other values which the user could select from but the highest number would not be larger then the total number of rows in the GridView. For example if there were 5 rows in the GridView the numbers 1,2,3,4,5 would be displayed in the DropDownList with the default number being the correct number for that row.

Using the Code

As has been mentioned before the amount of code is relatively brief but very useful. Comments are provided below to explain how the code works. The main things to note are that:

• ddroworder is the dropdownlist in the GridView
• GridView1 is the gridview
• dropdownlist has been declared at the start of the page

The following code should be put into the Page_Load event.

protected void Page_Load(object sender, EventArgs e)
{
    if (Page.IsPostBack == false)
    {
        Int32 countrows = 0;
        foreach (GridViewRow row in GridView1.Rows)
        {

            dropdownlist = ((DropDownList)row.FindControl("ddroworder"));
            countrows++;
            dropdownlist.DataValueField = countrows.ToString();

            dropdownlist.Items.Add(dropdownlist.DataValueField);

            //The following code adds the numbers to the dropdownlist until the 
            //highest number is the same as the total number of rows in the gridview

            for (int countallrows = 1; countallrows <= GridView1.Rows.Count; 
                countallrows++)
            {
                dropdownlist.Items.Add(new ListItem(countallrows.ToString(),
                    countallrows.ToString()));
            }
        }

        //The code below applies the correct datakey value as the DropDownList's
        //default selected value for that row

        for (int x = 0; x < GridView1.Rows.Count; x++)
        {
            dropdownlist =
                ((DropDownList)GridView1.Rows[x].FindControl("ddroworder"));
            dropdownlist.SelectedValue =
                GridView1.DataKeys[x].Values[2].ToString().Trim();
        }
    }
}

Once the code is added to the page load event the next thing to do is to create the code for the OnSelectedIndexChanged event.

public void dd_OnSelectedIndexChanged(object sender, EventArgs e)
{
    //This will get the dropdownlist which has been clicked
    DropDownList ddl = (DropDownList)sender;

    //The row will be the currently selected row from which the dropdownlist
    //was clicked
    GridViewRow row = (GridViewRow)ddl.NamingContainer;

    //Now we know which row is clicked we can get the datakey for that row

    string stselectedDatakey =
        GridView1.DataKeys[row.RowIndex].Values["Datakey1"].ToString();

    //Standard stored procedure can be used to update the database.
    //The selected value is the new value to be updated into the database
    //stselectedDatakey is the datakey for that row

    SqlCommand sqlcommand_ddupdate = new SqlCommand("update_table", con);
    sqlcommand_ddupdate.CommandType = CommandType.StoredProcedure;

    sqlcommand_ddupdate.Parameters.Add(new SqlParameter("@value1", SqlDbType.Int));
    sqlcommand_ddupdate.Parameters["@value1"].Value = ddl.SelectedValue;

    sqlcommand_ddupdate.Parameters.Add(new SqlParameter("@value2",
        SqlDbType.VarChar));
    sqlcommand_ddupdate.Parameters["@value2"].Value = stselectedDatakey;
}

The dropdownlist

<asp:TemplateField HeaderText="Row Order">
    <ItemTemplate>

        <asp:DropDownList ID="ddroworder" runat="server"
           OnSelectedIndexChanged="dd_OnSelectedIndexChanged" AutoPostBack="true">
        </asp:DropDownList>
    </ItemTemplate>
</asp:TemplateField>

Points of Interest

The GridView is a very useful component and I found whilst researching that there are not a lot of articles explaining how to use the GridView with other controls.

History

If I find any other useful things to do with the GridView then I will add them here. Enjoy!