Update in C# using SQL Server Database.

[Pete OHanlon]

The reason you're getting this exception is because you're attempting to add the parameters to the same command object inside a loop. This will work on the first trip through the loop, but will fail on the next trip through. A way around this is to do cmd.Parameters.Clear(); before you start adding the parameters in. This will ensure the parameters collection has no entries on each journey through the loop.

[Norris Chappell]

Okay that got rid of that error. Thanks but my update delete all of the fields. I now have in my database the id's number of all of the previous rows. How can I just update the database with what I have in my datgridview?

[Pete OHanlon]

As I don't know what keys are on either side of the equation here, I can't help you. It's up to you now. Step through the code and note the values that you're seeing - in particular, what's getting assigned to @ID. That's the one that's causing you issues, so that's the line that you are going to have to inspect. It's pretty apparent that the wrong value is being written to that field - you are in the best position to see what that value is.

[Norris Chappell]

Thanks for your help. I am now writing out a record to the database. The problem is writing the same first record over 42 times. You are correct the @ID is the culprit. I got to figure how to increment it.

[Pete OHanlon]

Looking at your code, you have put the ID in cell 0 and made it invisible. Why not just use the contents of that cell for your ID instead?

cmd.Parameters.AddWithValue("@id", row.Cells[0].Text));
cmd.Parameters.AddWithValue("@Name", row.Cells[1].Text);
cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells[2].Text);
cmd.Parameters.AddWithValue("@VDCFFS", row.Cells[3].Text);
cmd.Parameters.AddWithValue("@VDCHIM", row.Cells[4].Text);
cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells[5].Text);
cmd.Parameters.AddWithValue("@VDCCWF", row.Cells[6].Text);

[Norris Chappell]

Sorry that didn't work. It started loaded at the second record. and loaded the database with that record only.

[Pete OHanlon]

Post your updated code.

[Norris Chappell]

protected void gvKeyPersonnel_RowUpdating(object sender, EventArgs e)
{
int @ID = Convert.ToInt32(gvKeyPersonnel.DataKeys[0].Value.ToString());
TextBox @Name = (TextBox)gvKeyPersonnel.Rows[0].FindControl("txtName");
TextBox @VDCIDIQ = (TextBox)gvKeyPersonnel.Rows[1].FindControl("txtVDCIDIQ");
TextBox @VDCFFS = (TextBox)gvKeyPersonnel.Rows[2].FindControl("txtVDCFFS");
TextBox @VDCHIM = (TextBox)gvKeyPersonnel.Rows[3].FindControl("txtVDCHIM");
TextBox @VDCWEBHOSTING = (TextBox)gvKeyPersonnel.Rows[4].FindControl("txtVDCWEBHOSTING");
TextBox @VDCCWF = (TextBox)gvKeyPersonnel.Rows[5].FindControl("txtVDCCWF");

        using (SqlCommand cmd = new SqlCommand())
        {
           cmd.Connection = conn;
            conn.Open();
            // SqlDataReader myReader = null;
            // myReader = cmd.ExecuteReader();
            
                
            
            foreach (GridViewRow row in gvKeyPersonnel.Rows)
          //    for (int i = 0; i < gvKeyPersonnel.Rows.Count; i++)
                  
              {
                  
                 
               
               cmd.CommandText = @"UPDATE SP2010\_EDCStaffing\_AppDB.dbo.CMS\_Key\_Personnel  SET  Name = @Name, VDCIDIQ = @VDCIDIQ, VDCFFS = @VDCFFS, VDCHIM = @VDCHIM, VDCWEBHOSTING = @VDCWEBHOSTING, VDCCWF = @VDCCWF WHERE ID = @id";
               //cmd.Parameters.AddWithValue("@id", row.Cells\[0\].Text);
               //cmd.Parameters.AddWithValue("@Name", row.Cells\[0\].Text);
               //cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells\[1\].Text);
               //cmd.Parameters.AddWithValue("@VDCFFS", row.Cells\[2\].Text);
               //cmd.Parameters.AddWithValue("@VDCHIM", row.Cells\[3\].Text);
               //cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells\[4\].Text);
               //cmd.Parameters.AddWithValue("@VDCCWF", row.Cells\[5\].Text);
               cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys\[row.RowIndex\].Values\[0\]));
               cmd.Parameters.AddWithValue("@Name", Name.Text);
               cmd.Parameters.AddWithValue("@VDCIDIQ", VDCIDIQ.Text);
               cmd.Parameters.AddWithValue("@VDCFFS", VDCFFS.Text);
               cmd.Parameters.AddWithValue("@VDCHIM", VDCHIM.Text);
               cmd.Parameters.AddWit

[Pete OHanlon]

Whoah. Okay, that's really not going to work. You load the TextBoxes with one set of values and then you assign them on each iteration through the foreach. That's just going to set the values to the same values and I doubt that's what you want. You need to stop and have a think about what you're trying to achieve rather than just throwing things together in the hope that they will work. Write out the steps on a bit of paper and then code that up. Drop the TextBox approach here - it's just not going to work.

[Norris Chappell]

Okay thanks. I am a newbie to C#. Don't I need to use Label instead of TextBox in ASP.Net GridView control?

[Pete OHanlon]

It seems you have two separate issues that you're trying to fix here. The first is how to save the data from your GridView and the second one is how to display it/edit it. Is that correct? Let's solve the first one - by assuming that you have somehow managed to display and edit the data back into the GridView. If we assume that, then you use the approach I outlined to perform your save. Don't try to save directly off the TextBoxes as that only applies to saving a single row. So, follow the code I outlined using the value in cell 0 to get your Id and save off that.

[Norris Chappell]

Yes I can display my Gridview and edit it. When I click update that is where the issue is? You told me to cmd.Parameters.Clear(); Should that be in the foreach loop?

protected void gvKeyPersonnel_RowUpdating(object sender, EventArgs e)
{

        using (SqlCommand cmd = new SqlCommand())
        {
           cmd.Connection = conn;
            conn.Open();
                               
            
            foreach (GridViewRow row in gvKeyPersonnel.Rows)
                               
              {

               cmd.Parameters.Clear(); 
               
               cmd.CommandText = @"UPDATE SP2010\_EDCStaffing\_AppDB.dbo.CMS\_Key\_Personnel  SET  Name = @Name, VDCIDIQ = @VDCIDIQ, VDCFFS = @VDCFFS, VDCHIM = @VDCHIM, VDCWEBHOSTING = @VDCWEBHOSTING, VDCCWF = @VDCCWF WHERE ID = @id";
               cmd.ExecuteNonQuery();   
               cmd.Parameters.AddWithValue("@id", row.Cells\[0\].Text);
               cmd.Parameters.AddWithValue("@Name", row.Cells\[1\].Text);
               cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells\[2\].Text);
               cmd.Parameters.AddWithValue("@VDCFFS", row.Cells\[3\].Text);
               cmd.Parameters.AddWithValue("@VDCHIM", row.Cells\[4\].Text);
               cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells\[5\].Text);
               cmd.Parameters.AddWithValue("@VDCCWF", row.Cells\[6\].Text);
                                  
                
               
            }
            conn.Close();
        }
    }

I must be missing something here? Thanks for your help and looking at it?

[Pete OHanlon]

Yes it should. You need that to ensure you don't try and add the same parameters in again.

[Norris Chappell]

While in debug, I don't see any values in the fields? When I update all fields except the ID is deleted in the database. Don't I have to set the parameters first before using cmd.Parameters.AddWithValue?

[Agent__007]

Ta da! You are executing the command "before" adding the parameters. Move

cmd.ExecuteNonQuery();

line after you have added the parameters. Also a tip: - Remove the foreach loop and add the below line instead:

GridViewRow row = gvKeyPersonnel.Rows[e.RowIndex];

Hope this helps.

You have just been Sharapova'd.

[Norris Chappell]

I have the cmd.ExecuteNonQuery(); at the end But by removing the foreach loop and adding GridViewRow row = gvKeyPersonnel.Rows[e.RowIndex]; is giving me an error on RowIndexand if I put row instead of e it gives me a error on row. I still don't see any values in the debug. I have spend 5 days on this one and still don't see what is wrong.

protected void gvKeyPersonnel_RowUpdating(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();

                           //  foreach (GridViewRow row in gvKeyPersonnel.Rows)
                            GridViewRow row = gvKeyPersonnel.Rows\[e.RowIndex\];
                            {

                                cmd.CommandText = "UPDATE SP2010\_EDCStaffing\_AppDB.dbo.CMS\_Key\_Personnel  SET  Name = @Name, VDCIDIQ = @VDCIDIQ, VDCFFS = @VDCFFS, VDCHIM = @VDCHIM, VDCWEBHOSTING = @VDCWEBHOSTING, VDCCWF = @VDCCWF WHERE ID = @id";

                                cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys\[row.RowIndex\].Values\[0\]));
                                // cmd.Parameters.AddWithValue("@id", row.Cells\[1\].Text);
                                cmd.Parameters.AddWithValue("@Name", row.Cells\[1\].Text);

                                cmd.Parameters.AddWithValue("@VDCIDIQ", row.Cells\[2\].Text);
                                cmd.Parameters.AddWithValue("@VDCFFS", row.Cells\[3\].Text);
                                cmd.Parameters.AddWithValue("@VDCHIM", row.Cells\[4\].Text);
                                cmd.Parameters.AddWithValue("@VDCWEBHOSTING", row.Cells\[5\].Text);
                                cmd.Parameters.AddWithValue("@VDCCWF", row.Cells\[6\].Text);
                                //cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys\[row.RowIndex\].Values\[0\]));
                                //cmd.Parameters.AddWithValue("@Name", Name.Text);
                                //cmd.Parameters.AddWithValue("@VDCIDIQ", VDCIDIQ.Text);
                                //cmd.Parameters.AddWithValue("@VDCFFS", VDCFFS.Text);
                                //cmd.Parameters.AddWithValue("@VDCHIM", VDCHIM.Text);
                                //cmd.Parameters.AddWithValue("@VDCWEBHOSTING", VDCWEBHOSTING.Text);

[Agent__007]

Sorry, I should have mentioned this before. If the gvKeyPersonnel_RowUpdating() method/handler is for OnRowUpdating event of the gridview (which looks like is), i.e. if you have the following property for your grid (in .aspx) :

OnRowUpdating="gvKeyPersonnel_RowUpdating"

, then change the method's signature from

protected void gvKeyPersonnel_RowUpdating(object sender, EventArgs e)

to

protected void gvKeyPersonnel_RowUpdating(object sender, GridViewUpdateEventArgs e)

That should do.

You have just been Sharapova'd.

[Norris Chappell]

Yes I have OnRowUpdating="gvKeyPersonnel_RowUpdating property for my grid in aspx. Should I still removed foreach (GridViewRow row in gvKeyPersonnel.Rows). By the way how will it transverse through the datagridview with it?

[Agent__007]

Norris Chappell wrote:

Should I still removed foreach (GridViewRow row in gvKeyPersonnel.Rows).

Yes, you should. When you update any of the rows, it will fire up OnRowUpdating event of the grid and gvKeyPersonnel_RowUpdating() event handler will be called. Note that, since the event is firing up "only" for the row you are updating, you should only do the things related to that row (note that I said you "should" :)). Now for your question:

Norris Chappell wrote:

By the way how will it transverse through the datagridview with it?

The answer is simple - you don't want to traverse through every row in the grid since you are updating only one (for which the event is fired) at any given time. Related: If you don't have it already, you may want to have a look at OnRowUpdated[^] event as well.

You have just been Sharapova'd.

[Norris Chappell]

Thanks for pointing that out to me. One last thing do my parameters look right. Should I I removed this too? cmd.Parameters.Clear();