Database update only works SOME of the time [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Data not writing out to Database

I'm trying to update a bit field in my database on a checkbox's checkedchanged event. When it gets checked, it sends a 1. When it is u开发者_C百科nchecked, it sends a 0. Now I'm not sure why, but these changes only get saved SOME of the time. Would this have anything to do with the "!IsPostBack" ?

 protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
        {
            connection.Open();
            dataAdapter = new SqlDataAdapter("SELECT * FROM SecureOrders", connection);
            dataSet = new DataSet();             
            dataAdapter.Fill(dataSet, "SecureOrders");
            DataView source = new DataView(dataSet.Tables[0]);
            DefaultGrid.DataSource = source;
            DefaultGrid.DataBind();
            connection.Close();
        }
    }
}

protected void CheckBoxProcess_CheckedChanged(object sender, EventArgs e)
{
    bool update;
    string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
    string uncheckedString = "UPDATE SecureOrders SET processed = 0 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";
    CheckBox cb = (CheckBox)sender;
    GridViewRow gvr = (GridViewRow)cb.Parent.Parent;
    DefaultGrid.SelectedIndex = gvr.RowIndex;
    update = Convert.ToBoolean(DefaultGrid.SelectedValue);

    orderByString = orderByList.SelectedItem.Value;
    fieldString = searchTextBox.Text;



    connectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["secureodb"];

    using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
    {
        connection.Open();
        SqlCommand checkedCmd = new SqlCommand(checkedString, connection);
        SqlCommand uncheckedCmd = new SqlCommand(uncheckedString, connection);

        if (cb.Checked == true)
        {
            checkedCmd.ExecuteNonQuery();

        }
        else
        {
            uncheckedCmd.ExecuteNonQuery();
        }

        connection.Close();
    }

---

I would recommend setting "EnableViewState" to false on your DataView, and then moving the code from the if (!IsPostBack) section to your page's Pre_Init event. I think that will solve your problem and possibly also help your postbacks go faster.

---

The !IsPostBack is not your issue, whether or not I like the pattern. The postback event handler is a mess, however.

1. You have left open a potential SQL injection hole by concatenating strings rather than using parameters. Even if you have to write a sproc and pass in a comma separted list, you are better off than the code you have.
2. Your decision point is one whether or not a value is set to 1 or 0, yet you branch code on the point of deciding which of two strings to run (one will always be created, consuming cycles, yet NEVER run).
3. You are creating two command objects, one of which will NEVER be used.

You can determine where things are going wrong by stepping through the code with a wide variety of test cases. Eventually you will hit the one that triggers the issue you have.

A better option would be to separate out the SQL update code into its own routine and send the parameters in. This will reduce the number of moving parts. The only thing that should be in the main event handler (and this can be argued) is the grabbing of the variables from the Grid.

If it were me, I would also consider using the key value for my update statement, lest you have two James Smith's in the database, both which are now processed.

As for candidates why it updates some times and not others? Could very well be that you are ending up with the wrong selected row for some reason. As I don't have a copy of your code to see all the permeatations, I can only guess.