c# Insert,Delete, and Edit in Same form load datagridview

Question

I have a datagridview with 4 columns. 1 of which is a manually added deletecheckbox column (The first column). There's some strange behavior when I try and delete multiple rows based on the checked value. For example, if I check 2 rows, 1 gets deleted. If I check 5 rows, 3 gets deleted. So basically half of what I click gets deleted. The ones don't get deleted are remained checked, and I would have to click the update button again to remove more. If I want to delete ALL checked checkboxes with one button click, what do I need to fix?

Entire Button Click Event:

private void button1_Click(object sender, EventArgs e)
    {
        SqlCommand cmdAdd = new SqlCommand("insert into Josh_REL_Table (UserID, RoleName) Values (@UserID,@RoleName)", con);
        SqlCommand cmdEdit = new SqlCommand("update Josh_Rel_Table SET UserID = @UserId, RoleName=@RoleName where RelationID=@RelationID", con);
        con.Open();


        cmdAdd.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmdAdd.Parameters.Add("@RoleName", SqlDbType.VarChar);
        cmdAdd.Parameters.Add("@RelationID", SqlDbType.VarChar);

        cmdEdit.Parameters.Add("@UserID", SqlDbType.VarChar);
        cmdEdit.Parameters.Add("@RoleName", SqlDbType.VarChar);
        cmdEdit.Parameters.Add("@RelationID", SqlDbType.VarChar);

        if (DGV1.RowCount >= 1)
        {
            for (int x = 0; x < DGV1.RowCount - 1; x++)
            {
                if (DGV1.Rows[x].Cells[1].Value.ToString() == "" || DGV1.Rows[x].Cells[1].Value == null)
                {
                    cmdAdd.Parameters["@UserID"].Value = DGV1.Rows[x].Cells[2].Value.ToString();
                    cmdAdd.Parameters["@RoleName"].Value = comboBox1.Text;
                    cmdAdd.Parameters["@RelationID"].Value = DGV1.Rows[x].Cells[1].Value.ToString();
                    cmdAdd.ExecuteNonQuery();
                }
                if (!DGV1.Rows[x].IsNewRow)
                {
                    cmdEdit.Parameters["@UserID"].Value = DGV1.Rows[x].Cells[2].Value.ToString();
                    cmdEdit.Parameters["@RoleName"].Value = comboBox1.Text;
                    cmdEdit.Parameters["@RelationID"].Value = DGV1.Rows[x].Cells[1].Value.ToString();
                    cmdEdit.ExecuteNonQuery();
                }
                if (Convert.ToBoolean(DGV1.Rows[x].Cells[0].Value))
                {
                     List<DataGridViewRow> selectedRows = (from newRow in DGV1.Rows.Cast<DataGridViewRow>() where Convert.ToBoolean(newRow.Cells["Delete"].Value) == true select newRow).ToList();
                    if (MessageBox.Show(string.Format("Do you want to delete {0} rows?", selectedRows.Count), "Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        foreach (DataGridViewRow newRow in selectedRows)
                        {

                                using (SqlCommand cmd = new SqlCommand("DELETE FROM Josh_REL_Table WHERE RelationID = @RelationID", con))
                                {
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Parameters.AddWithValue("@RelationID", newRow.Cells["RelationID"].Value);

                                    cmd.ExecuteNonQuery();

                                    DGV1.Rows.Remove(newRow);
                                }

                        }
                    }
                }

            }
        }


            MessageBox.Show("Record updated!");
            con.Close();
    }

Fuzzzzel · Accepted Answer · 2015-02-26 19:29:55Z

1

You should delete the rows backwards, beginning with the last row working your way through to the first, because otherwise the index is wrong. Consider you want to delete the rows with index 2 and 4:

index  actual row
1      Row 1
2      Row 2 <-- If you delete this
3      Row 3
4      Row 4
5      Row 5

This basically means that after deleting the row with index 2 you will continue on the row with index 3 which is now Row 4 so you never handled what should happen to Row 3:

After deletion you land here

index  actual row
1      Row 1
2      Row 3
3      Row 4
4      Row 5

Just change your loop to:

for (int x = DGV1.RowCount - 1; x >= 0; ; x--)

The part for the expression:

Everywhere you assume you van have a cell value that you want to convert to a string but might be null needs to be escaped, otherwise you will receive an error:

// Old
cmdEdit.Parameters["@UserID"].Value = DGV1.Rows[x].Cells[2].Value.ToString();

// New
cmdEdit.Parameters["@UserID"].Value = (DGV1.Rows[x].Cells[2].Value != null) ? DGV1.Rows[x].Cells[2].Value.ToString() : "";

The part below new evaluates the expression and converts the value to string if it is not null or sets the value to "" if it is null.

Kind of like this:

variable = (Expression) ? (will be evaluated if true) : (will be evaluated if false)

edited Feb 26, 2015 at 19:29

answered Feb 26, 2015 at 17:44

Fuzzzzel

1,7833 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Josh M Over a year ago

Thank you. However, when I re-run it I get an error "object reference not set to an instance of an object" on line " if (DGV1.Rows[x].Cells[1].Value.ToString() == "" || DGV1.Rows[x].Cells[1].Value == null)" Even though I'm looping the other way, the data is the same so why am I getting this?

Fuzzzzel Over a year ago

Can you move the test for null to the left of th OR? Then it is first tested for NULL and if it is not NULL the other condition is tested (empty string). Null cannot be converted to string and so it will throw an error. Try this: if (DGV1.Rows[x].Cells[1].Value == null || DGV1.Rows[x].Cells[1].Value.ToString() == "")"

Josh M Over a year ago

When I debug, it looks in the if statement now, but that first line causes that same error. ( cmdAdd.Parameters["@UserID"].Value = DGV1.Rows[x].Cells[2].Value.ToString();)

Fuzzzzel Over a year ago

The row where this happens might have empty fields. Can you check the value of "x" when the error occurs and check the value that should be converted? My best quess is that you have a new no value for that cell. This in turn means the value is Null, which again cannot be converted to string.

Josh M Over a year ago

Right, My goal is to just add rows to the datagridview/database just by filling out the rows, edit the rows by just replacing the values, and delete rows based on checkboxes. When the form and datagridview loads, the last line shows n unchecked checkbox and an empty cell under "userID" to fill in.

|

Collectives™ on Stack Overflow

c# Insert,Delete, and Edit in Same form load datagridview

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related