Update Multiple Records From a GridView C#

Question

I'm trying to update multiple rows in a data grid, the code gets the job done but I still seem to get a

Object reference not set to an instance of an object

When I check the records the desired status updates accordingly for the selected records within the gridview.

private void UpdateWorkerStatus()
{
    SqlCommand cmdUpdate = new SqlCommand(@"UPDATE Workers2
                                       SET WorkerStatus = @WorkerStatus
                                       WHERE FullName = @FullName", cn);

    cmdUpdate.Parameters.AddWithValue("@WorkerStatus", SqlDbType.VarChar).Value = txtWorkerStatus.Text;
    cmdUpdate.Parameters.AddWithValue("@FullName", SqlDbType.VarChar).Value = txtFullName.Text;

    foreach (DataGridViewRow row in grdWorkers.Rows)
    {
        cmdUpdate.Parameters["@WorkerStatus"].Value = row.Cells["WorkerStatus"].Value.ToString();
        cmdUpdate.Parameters["@FullName"].Value = row.Cells["FullName"].Value.ToString();
        cmdUpdate.ExecuteNonQuery();
    }
}

thank you in advance! :)

For this to maybe not be a duplicate please specify on what row it happens with what data and what you have seen when debugging it — Gilad Green, Aug 18 '16 at 09:49

score 1 · Answer 1 · answered Aug 18 '16 at 10:11

private void UpdateWorkerStatus()
{
    SqlCommand cmdUpdate = new SqlCommand(@"UPDATE Workers2
                                   SET WorkerStatus = @WorkerStatus
                                   WHERE FullName = @FullName", cn);

    cmdUpdate.Parameters.AddWithValue("@WorkerStatus", SqlDbType.VarChar).Value = txtWorkerStatus.Text;
    cmdUpdate.Parameters.AddWithValue("@FullName", SqlDbType.VarChar).Value = txtFullName.Text;

    foreach (DataGridViewRow row in grdWorkers.Rows)
    {
        cmdUpdate.Parameters["@WorkerStatus"].Value = row.Cells["WorkerStatus"].Value!=DBNull.Value? row.Cells["WorkerStatus"].Value.ToString():"";
        cmdUpdate.Parameters["@FullName"].Value = row.Cells["FullName"].Value!= DBNull.Value ? row.Cells["FullName"].Value.ToString():"";
        cmdUpdate.ExecuteNonQuery();
    }
}

score 0 · Answer 2 · edited Aug 18 '16 at 09:54

0

Try this,Instead of .ToString() use Convert.Tostring( row.Cells["FullName"].Value) Same for "WorkStatus"

edited Aug 18 '16 at 09:54

Gilad Green

36,708
7
61
95

answered Aug 18 '16 at 09:52

Akshay Belure

204
3
14

score 0 · Accepted Answer · answered Aug 18 '16 at 10:07

Since you don't supply us with the information of where the exception is thrown, I'm going to assume it's on one of the .ToString() calls as those are the most likely. Probably one of the values for WorkerStatus or FullName are null, resulting in the exception when you call the .ToString() method.

I would check the values for null before calling the .ToString(). This enables you to fill the parameter values with something meaningful if the value you are trying to read is null:

if (row.Cells["WorkerStatus"].Value != null)
    cmdUpdate.Parameters["@WorkerStatus"].Value = row.Cells["WorkerStatus"].Value.ToString();
else
    cmdUpdate.Parameters["@WorkerStatus"].Value = string.Empty // or some meaningful default value of your chosing

You can reduce this statement to one line using this:

cmdUpdate.Parameters["@WorkerStatus"].Value = (row.Cells["WorkerStatus"].Value != null) ? row.Cells["WorkerStatus"].Value.ToString() : string.Empty;

And of course you should do the same for FullName.

Update Multiple Records From a GridView C#

3 Answers3