Update DataGridView Checked Record to the Database

Question

So I have this DataGridView on which there are two columns which I am retrieving from my SQL Server database. Now, in the second column, we have a bit field which shows as a CheckBox in my Windows Application designer. So, I want to, on CellContentClick event be able to update the value that just got deselected into my database. But seems like I am going nowhere.

Here is my code below:

 private void gvTurnOffNotifications_CellContentClick(object sender, DataGridViewCellEventArgs e)
 {
         foreach (DataGridViewRow row in gvTurnOffNotifications.Rows)
         {
             DataGridViewCheckBoxCell cell = row.Cells[1] as DataGridViewCheckBoxCell;
             //We don't want a null exception!
             if (cell.Value != null)
             {
                 bool result = Convert.ToBoolean(row.Cells[1].Value);
                 if (result == true)
                 {
                     //It's checked!
                     btnUpdateTurnOff.Enabled = true;
                     myConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                     using (mySQLConnection = new SqlConnection(myConnectionString))
                     {
                         int temp = 1;
                         bool change = false;

                         string procedureName = "update UsersNotified Set AllowNotification='" + change + "' where AllowNotification='" + false+ "'";
                         mySQLCommand = new SqlCommand(procedureName, mySQLConnection);
                         mySQLCommand.CommandType = CommandType.Text;
                         mySQLCommand.Connection = mySQLConnection;
                         mySQLCommand.Connection.Open();
                         mySQLCommand.ExecuteNonQuery();
                     }
                 }
             }
         }
     }

And then when I click on my "Update" button, I want to send the updated griddata for storing in my database as below:

 private void btnUpdateTurnOff_Click(object sender, EventArgs e)
        {
            myConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (mySQLConnection = new SqlConnection(myConnectionString))
            {
                mySQLDataAdapter = new SqlDataAdapter("spGetAllUpdatedNotifications", mySQLConnection);
                mySQLDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySQLCommand.Connection = mySQLConnection;
                mySQLCommand.Connection.Open();
                DataSet ds = new DataSet();
                mySQLDataAdapter.Fill(ds);
                mySQLDataAdapter.UpdateCommand = mySQLCommand;
                mySQLDataAdapter.Update(ds);
            }
        }

The spGetAllUpdatedNotifications object in my Update block is a stored procedure I am calling just to retrieve the records from the database so I can update them on the fly in my DataSet. Here is the definition below:

create proc spGetAllUpdatedNotifications
as
begin
SELECT UserName, AllowNotification FROM UsersNotified where AllowNotification=1
  end   
GO

For more context: When my form loads, I am selecting all the records from the database which have their AllowNotification field set to bit 1 (true in C#) and once a user unticks a specific user (in other words, that user would not be allowed to receive notifications anymore) and once I click on the Update button, it should set the property to false (bit 0 in the database).

Instead of updating the one record which I have deselected, it updates all of them. "All" in this case are the records which have AllowNotification=1. I only want to set AllowNotification=0 for the deselected/unchecked record only

Any suggestions on how I can go about achieving this?

Chetan · Accepted Answer

I am not sure what logic makes you to loop thru all the rows of the DataGridView just to update one row in the database.

If you want to update AllowNotification value for the username for which checkbox is checked or unchecked the logic would be this.

Figure out the updated value of the checkbox which is clicked in the gridview.
Store the updated value (True or False) in a boolean variable.
Retrieve the corresponding username of from the other cell of the same row the gridview.
Execute update query with criteria "WHERE UserName = {userName}".

You need to write CellContentClick event of the DataGridView as following.

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    if (e.ColumnIndex == 1) //Assuming Checkbox is displayed in 2nd column.
    {
        this.dataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);

        var result = this.dataGridView1[e.ColumnIndex, e.RowIndex].Value;
        var userName = this.dataGridView1[0, e.RowIndex].Value; //Assumin username is displayed in fist column

        var connectionString = "Your Connection String";
        //Set value of your own connection string above.

        var sqlQuery = "UPDATE UsersNotified SET AllowNotification = @allowNotification WHERE UserName = @userName";

        using (var connection = new SqlConnection(connectionString))
        {
            using (var command = new SqlCommand(sqlQuery, connection))
            {
                command.CommandType = CommandType.Text;
                command.Parameters.Add("@allowNotification", SqlDbType.Bit).Value = result;
                command.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = userName;
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}

This should help you resolve your issue.

Update DataGridView Checked Record to the Database

Answers (2)

Related Questions