Database row not updating via GridView update

Question

I have a GridView which has the property:

OnRowUpdating="GridViewRowUpdateEventHandler"

Within the GridView, I have the following control:

<asp:TemplateField HeaderText="Progress" SortExpression="progress">
    <ItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true">
            <asp:ListItem Value="0">Incomplete</asp:ListItem>
            <asp:ListItem Value="1">Complete</asp:ListItem>
        </asp:DropDownList>
    </ItemTemplate>
</asp:TemplateField> 

The GridViewRowUpdateEventHandler looks like this:

protected void GridViewRowUpdateEventHandler(object sender, GridViewUpdateEventArgs e)
{
    SqlConnection connection;
    SqlCommand command;

    GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];

    DropDownList ddlPriority = (DropDownList)row.FindControl("DropDownList1");

    using (connection = new SqlConnection(ConfigurationManager.AppSettings["connString"]))
    {
        using (command = new SqlCommand(@"update table1 set priority = @priority where id = @id", connection))
        {
            command.Parameters.Add("@priority", SqlDbType.Int, 1).Value = ddlPriority.SelectedValue;
            command.Parameters.Add("@id", SqlDbType.Int, 1).Value = row.RowIndex;

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }

    GridView1.DataBind();
}

I am getting no error messages at all, and the relevant row in the database is not being updated. Anyone know why?

Could it be that I am looking at the wrong cell Cells[5]? Or maybe because I have nothing in page_load? Or maybe something else?

Answer 1

Since the DropDiownList is in a TemplateField and it's NamingContainer is the GridViewRow, you should use row.FindControl to get the reference:

DropDownList ddlPriority = (DropDownList)row.FindControl("DropDownList1");

instead of

DropDownList ddlPriority = (DropDownList)row.Cells[5].FindControl("DropDownList1");

But that is not the core of your problem since row.Cells[5].FindControl might work also when it is in the 6th cell. Otherwise you would get a NullreferenceException.

I assume that you are also binding the GridView on postbacks, you should check the IsPostBack property:

protected void Page_Load()
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

Apart from that:

command.Parameters.Add("@id", SqlDbType.Int, 1).Value = row;

Does not work because row is the GridViewRow. You should also DataBind your GridView at the end, otherwise the changes won't be reflected.

Answer 2

You add DataBind in the end of your GridViewRowUpdateEventHandler

GridView.DataBind();

In order to find control

var ddlPriority = (DropDownList)row.FindControl("DropDownList1");

Answer 3

I would assume that you have forgotten to rebind your gridView data

gridview1.DataSource = YOUR_DATASOURCE;
gridview1.DataBind();

Furthermore you should access your dropDownList by

DropDownList ddlPriority = (DropDownList) row.FindControl("DropDownList1");

EDIT

Found another error

command.Parameters.Add("@id", SqlDbType.Int, 1).Value = row; // wrong! primaryKey value needed!

this won't work as row is of type GridViewRow. You should assign your primaryKey value I assume!

No matter what you decide to do first I would add some Debug.WriteLine(..) statements to see what values will be sent to the SQL Server.

Answer 4

I think the issue is that you are using the row's rowIndex as the ID. So your sql command at the end would be something like this

update table1 set priority = 'blah' where id = 0 //(where its the first row and so on)

So it is a perfectly good query, which will run without errors or exceptions but there is no ID = 0. You are binding it to the wrong ID. What you need to bind it to, is the ID from the table.

You can double check this by running SQL profiler and you should be able to locate the query that is being sent to the database.

Answer 5

You need to update the EditIndex of GridView

as

GridView.EditIndex = -1;