Data not writing out to Database

问题:

I'm writing a bit value to a database in the CheckboxProcess_CheckedChanged event. However, nothing is actually being written out. I had some code in there filling an adapter, but I took it out. Can anybody see where this is going wrong?

    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();
        }

回答1:

For your update statements you have:

string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '% " + DefaultGrid.SelectedRow.Cells[3].Text + "%'";

I'm wondering if you need to remove the space after the last % mark:

string checkedString = "UPDATE SecureOrders SET processed = 1 WHERE fName LIKE '%" + DefaultGrid.SelectedRow.Cells[2].Text + "%' AND lName LIKE '%" + DefaultGrid.SelectedRow.Cells[3].Text + "%'";

回答2:

I would suggest you try the following steps:

• separate the UI code (reading and writing the textboxes and grid and stuff) from the actual database code - at some point, you might want to separate those out to separate assemblies, even

• use parametrized queries for updating your data! Prevents SQL injection attacks, and makes your stuff go faster, too! Use them - always - no excuses

Your code would then look something like this:

protected void CheckBoxProcess_CheckedChanged(object sender, EventArgs e)
{
    bool update = Convert.ToBoolean(DefaultGrid.SelectedValue);

    // determine your first name and last name values
    string firstName = .......;  
    string lastName = .......;

    UpdateYourData(update, firstName, lastName);
}

private void UpdateYourData(bool isProcessed, string firstName, string lastName)
{
    Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration("/Cabot3");
    ConnectionStringSettings connectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["secureodb"];

    string updateStmt = "UPDATE dbo.SecureOrders SET processed = @Processed " + 
                        "WHERE fName LIKE @firstName AND lName LIKE @lastName";

    using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
    using (SqlCommand _update = new SqlCommand(updateStmt, connection))
    {
        _upate.Parameters.Add("@Processed", SqlDbType.Bit).Value = isProcessed;
        _upate.Parameters.Add("@firstName", SqlDbType.VarChar, 100).Value = firstName;
        _upate.Parameters.Add("@lastName", SqlDbType.VarChar, 100).Value = lastName;

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

Now I don't know if that's really going to solve your problem - I couldn't see anything at first sight.... but try it - maybe that'll give you a head start towards isolating your issues!