[RESOLVED] Deleting row manually from DataGridView - DataRowState.Deleted

[Ms.Longstocking]

I've got a DataGridView with a ton of records for the user to manipulate.
When done, a user can save all the changes to db with one click.
Using a switch statement, I go through each of the rows in my db that have changed [insert, delete, update] and effect that change row by row.
My code:

var dataTable = ((DataTable)dgvSchedule.DataSource).GetChanges();
if (dataTable != null && dataTable.Rows.Count > 0)
{
   using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString))
   {
      foreach (DataRow row in dataTable.Rows)
      {
         try
         {
            switch (row.RowState)
            {
                case DataRowState.Added:
                   //SPROC-INSERT - works ok
                   break;
                case DataRowState.Deleted:
                   using (SqlCommand cmd = new SqlCommand("DeleteSproc", conn))
                   {
                      //string RowToDelete = dgvSchedule.CurrentRow.Cells[0].Value.ToString();
                      cmd.Connection = conn;
                      cmd.CommandType = CommandType.StoredProcedure;
                      cmd.Parameters.AddWithValue("@EmployeeId", row["EmployeeId"]);
                      conn.Open();
                      cmd.ExecuteNonQuery();
                      conn.Close();
                      dt.AcceptChanges();
                   }
                   break;
                case DataRowState.Modified:
                   //SPROC-UPDATE - works ok
                   break;
            }
            dgvSchedule.Sort(dgvSchedule.Columns["EmployeeId"], ListSortDirection.Ascending);
         }
         catch (Exception ex)
         {
              MessageBox.Show(ex.ToString());
              break;
         }
     }
}

((DataTable)dgvSchedule.DataSource).AcceptChanges();

My INSERT and UPDATE SPROCS work just fine. I pass along an employeeID and the SPROC INSERTS or UPDATES accordingly. My issue is when a record is deleted. I'm having trouble retrieving the EmployeeID (PK) from the datatable after I delete the record from my datagridview. How can I access column information from my deleted rows? I do not believe that an AcceptChanges is going off on manual delete using datagridview because my function above still detects the DataRowState as Deleted.

Can anyone suggest a solution?

MzPippz

[jmcilhinney]

You're taking something easy and making it hard. Create a SqlDataAdapter, set its InsertCommand, UpdateCommand and DeleteCommand and call Update. That's it, that's all.

[Ms.Longstocking]

Something tells me that I'm p*ssing in the wind with this code... but here goes...

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
{
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }
    SqlDataAdapter da = new SqlDataAdapter();

    SqlCommand update = new SqlCommand("ModifySchedule", conn);
    update.CommandType = System.Data.CommandType.StoredProcedure;
    update.Parameters.AddWithValue("@EmployeeId", dgvSchedule.CurrentRow.Cells[0].Value);
    update.Parameters.AddWithValue("@xxx", dgvSchedule.CurrentRow.Cells[1].Value);
    update.Parameters.AddWithValue("@DateField", dgvSchedule.CurrentRow.Cells[2].Value);
    update.Parameters.AddWithValue("@qqq", dgvSchedule.CurrentRow.Cells[3].Value);
    update.Parameters.AddWithValue("@vvv", dgvSchedule.CurrentRow.Cells[4].Value);
    update.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbSelectScheduleType.SelectedValue;
    da.UpdateCommand = update;

    SqlCommand delete = new SqlCommand("DeleteFromSchedule", conn);
    delete.CommandType = System.Data.CommandType.StoredProcedure;
    delete.Parameters.AddWithValue("@EmployeeId", dgvSchedule.CurrentRow.Cells[0].Value);
    da.DeleteCommand = delete;

    SqlCommand insert = new SqlCommand("AddToSeason", conn);
    insert.CommandType = System.Data.CommandType.StoredProcedure;
    insert.Parameters.AddWithValue("@EmployeeId", dgvSchedule.CurrentRow.Cells[0].Value);
    insert.Parameters.AddWithValue("@xxx", dgvSchedule.CurrentRow.Cells[1].Value);
    insert.Parameters.AddWithValue("@DateField", dgvSchedule.CurrentRow.Cells[2].Value);
    insert.Parameters.AddWithValue("@qqq", dgvSchedule.CurrentRow.Cells[3].Value);
    insert.Parameters.AddWithValue("@vvv", dgvSchedule.CurrentRow.Cells[4].Value);
    insert.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbSelectScheduleType.SelectedValue;
    da.InsertCommand = insert;

    //AND FINALLY
    da.Update(dt);  
    ((DataTable)dgvSchedule.DataSource).AcceptChanges();
}

The INSERT command actually works. But I can't get UPDATE or Delete to work.
No error message. It just doesn't update the db.

[jmcilhinney]

You should not be calling AddWithValue when adding your parameters because you're not supposed to be setting the Value. How can you set a single value for each parameter when you want to save multiple rows? You're supposed to call Add and specify which DataColumn the Value is supposed to be drawn from. The adapter will then set the Value of each parameter itself as it saves each row.

Also, there's no point opening the connection or calling AcceptChanges because the adapter will handle all that for you. If you were going to call AcceptChanges though, why would you use that expression to get the DataTable when you used 'dt' in the line above?

[Ms.Longstocking]

I'm even more confused now.

Ok, I removed the AddWithValue parameter and am now using "Add", like so:
update.Parameters.Add("@EmployeeId");

I'm now getting a null exception error.

In addition, my insert command no longer works. It's sending the whole table instead of just the added(dirty) rows.

What do I need to do to this code:

using (SqlConnection connZ = new SqlConnection(ConfigurationManager.ConnectionStrings["TheTool.Properties.Settings.TheToolConnectionString"].ConnectionString))
{
    SqlCommand update = new SqlCommand("ModifySchedule", conn);
    update.CommandType = System.Data.CommandType.StoredProcedure;
    update.Parameters.Add("@EmployeeId");
    update.Parameters.Add("@XXX");
    update.Parameters.Add("@DateTimeField");
    update.Parameters.Add(CbScheduleType.SelectedValue);
    da.UpdateCommand = update;

    SqlCommand delete = new SqlCommand("DeleteFromSchedule", conn);
    delete.CommandType = System.Data.CommandType.StoredProcedure;
    delete.Parameters.Add("@EmployeeId");
    da.DeleteCommand = delete;

    SqlCommand insert = new SqlCommand("AddToSchedule", conn);
    insert.CommandType = System.Data.CommandType.StoredProcedure;
    insert.Parameters.AddWithValue("@EmployeeId", WHAT GOES HERE ?);
    insert.Parameters.AddWithValue("@XXX", WHAT GOES HERE ?);
    insert.Parameters.AddWithValue("@DateTimeField", WHAT GOES HERE ?);
    insert.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;
    da.InsertCommand = insert;

    da.Update(dt);
}

..... to update my database table with one button push?
I included both failed versions of the "ADD" and "ADDWITHVALUE" code samples.

This is how I am populating my datatable and datagridview:

conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
SqlCommand cmd = new SqlCommand("GetSchedule", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;
da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
dgvSchedule.DataSource = dt;

And these are my globally declared variables:

DataTable dt;
SqlConnection conn
SqlDataAdapter da;

LOL! All I originally wanted to do was to find a way to reflect the deleted item in my dgv in the database and now it's all gone to *bleep!*

[jmcilhinney]

Man, you really need to learn how to create parameters. You call AddWithValue if you want to create a parameter with a name and a value and let the data type be inferred from that value. This is so wrong:

cmd.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;

That should either be:

cmd.Parameters.AddWithValue("@sid", CbScheduleType.SelectedValue);

or:

cmd.Parameters.Add("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;

If you're setting the Value separately then you're not providing a value so you're not calling AddWithValue because you're not adding a parameter with a value. As for the rest, you're supposed to be calling ONLY Add, NOT AddWithValue, and calling an overload that allows you to specify the DataColumn from which to get the values, which is what I said earlier.

I suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data. There's a couple of examples there that use a data adapter, one with a command builder and one without. That will show you how to add the parameters.

[Ms.Longstocking]

I agree with you that I need to brush up on my coding skills.
Coming back to it after 7 years off...

The good news is that I have it working now.
Your help has been invaluable. Thank you.

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString))
{
    SqlCommand update = new SqlCommand("ModifySchedule", conn);
    update.CommandType = System.Data.CommandType.StoredProcedure;
    update.Parameters.Add("EmployeeId", SqlDbType.Char).SourceColumn = "EmployeeId";
    update.Parameters.Add("@DateTimeField", SqlDbType.DateTime).SourceColumn = "DateTimeField";
    update.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;
    da.UpdateCommand = update;

    SqlCommand delete = new SqlCommand("DeleteFromSchedule", conn);
    delete.CommandType = System.Data.CommandType.StoredProcedure;
    delete.Parameters.Add("@EmployeeId", SqlDbType.Char).SourceColumn = "EmployeeId";
    da.DeleteCommand = delete;

    SqlCommand insert = new SqlCommand("AddToSchedule", conn);
    insert.CommandType = System.Data.CommandType.StoredProcedure;
    insert.Parameters.Add("EmployeeId", SqlDbType.Char).SourceColumn = "EmployeeId";
    insert.Parameters.Add("@DateTimeField", SqlDbType.DateTime).SourceColumn = "DateTimeField";
    insert.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;
    da.InsertCommand = insert;

    da.Update(dt);
}

On a closing note, consider the mechanism that I use to populate my dgv and dt:

conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
SqlCommand cmd = new SqlCommand("GetSchedule", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbScheduleType.SelectedValue;
da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
dgvSchedule.DataSource = dt;

If I were to enclose it in a 'using' block, would my global variable changes be reflected outside its scope? Namingly the da? I ran a test and generated a null exception so I figure this is not the case.
I like the idea of encapsulating as much as I can in 'using' blocks. Would there be a way to do it in this particular case?

try
{
    using(conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString)
        {
             using(SqlCommand cmd = new SqlCommand("GetSchedule", conn)
             { 
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sid", SqlDbType.Char).Value = CbSelectScheduleType.SelectedValue;
                using (da = new SqlDataAdapter(cmd))
                {            {
                    dt = new DataTable();
                    da.Fill(dt);
                    dgvSchedule.DataSource = dt;
                }
              }
         }
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

[FunkyDexter]

would my global variable changes be reflected outside its scope?

Variable changes are reflected at the scope of the variable. So if a variable is global and you change it's value then you change it at a global scope. Where you do the changing from is irrelevant.

However

Namingly the da?

In your final code snippet the instance of DA is local, not global. This

using (da = new SqlDataAdapter(cmd))

creates a new Data Adapter and assigns it to the global variable DA (replacing anything that was already in DA). However, at the end of the using block the Adapter will be disposed and the global variable DA will be left pointing to nothing.

Using block are all about making sure entities get cleaned up and disposed of when we're finished with them, and we define "finished with" as "the end of the using block". If you're hoping to fill a data adapter in that routine and have the filled adapter usable elsewhere in the code then you should not put it in a Using. I would say, though, that this stinks to me. A general principle of data handling in .Net is that you keep things as local as possible. You open a connection, issue a command, get some data and get out as quickly as possible.