Update in C# using SQL Server Database.

[Mycroft Holmes]

What I would do is create 2 variables to hold the cell values, put a break point in the loop and check the values before they are passed to the parameters.

Never underestimate the power of human stupidity RAH

[Agent__007]

Norris Chappell wrote:

cmd.Parameters.AddWithValue("@Name", Convert.ToString(gvKeyPersonnel.DataKeys[row.RowIndex].Values[1]));

This statement shouldn't be there, you should only have your "ID" field in the DataKeys. Instead, you should be having something like this:

cmd.Parameters.AddWithValue("@Name", row.Cells[IndexOfCellContainingNameField].Text); // Note that, I am assuming you are not having any template for your "Name" field, like a label, in which case you need to use FindControl() method on your cell and then use .Text on that label

Regarding the IndexOutOfRangeException you are getting, I think if you only have the "ID" in the DataKeys property (i.e. remove the "Name" from DataKeys and pass its value like the above line), that won't come up - assuming your RowIndex is not the culprit. Not related to your current question, but I think if you are having to iterate all rows in the grid for updating a row, you might want to have a look at examples here[^], here[^] or here[^].

You have just been Sharapova'd.

[Norris Chappell]

Hi, I don't have any of my fields with a label. I'm still getting an Out of Range error.

protected void Button_Update(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand())
{

            // SqlDataReader myReader = null;
            // myReader = cmd.ExecuteReader();
            foreach (GridViewRow row in gvKeyPersonnel.Rows)
            {

                cmd.Connection = conn;
                conn.Open();

                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("@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.ExecuteNonQuery();
            }
            conn.Close();
        }
    }

[Agent__007]

Just a quick suggestion - try your row.Cells[1].Text, row.Cells[2].Text, ... statements with [0], [1], and so on - i.e. with a Zero-based index. I think that should be it. If not, without a debugger, I can't help you any further, I am afraid.

You have just been Sharapova'd.

[Mycroft Holmes]

Caveat, can the user reorder your DGV columns, this would change the index of the cells and screw up your code.

Never underestimate the power of human stupidity RAH

[Pete OHanlon]

If you put a breakpoint on cmd.CommandText, press F5 and step through the code when the debugger hits this line. Look to see which one of those lines the application crashes on. As a hint, you can use Visual Studio to inspect the values of each cell to see what's in it.

[Norris Chappell]

Hi Pete, It crashes on this line: cmd.Parameters.AddWithValue("@id", Convert.ToInt32(gvKeyPersonnel.DataKeys[row.RowIndex].Values[0])); The value is null.

[Pete OHanlon]

Now that you know the line, you need to identify which part is the problem. Is it the DataKeys[row.RowIndex] or is it Values[0]? My suspicion is that it's the latter - in other words, you don't actually have a value in there.

[Norris Chappell]

I don't see the out of range error anymore. It doesn't update but deletes the first record in the database. I getting this error: The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure.

[Norris Chappell]

It appears I don't because I not getting that OOR error any more. I getting:The variable name '@id' has already been declared. Variable names must be unique within a query batch or stored procedure. Do I need to make any field Label instead of TextBox, The ID field is being is being hidden. I really don't know what is wrong now. Here is my present code. Maybe by looking at all of the code you might can spot something I missed. I have been working on this 4 straight days.

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls.WebParts;

namespace StaffingWebParts.KeyPerTest
{
public partial class KeyPerTestUserControl : UserControl
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString);
SqlCommand cmd = new SqlCommand();

    protected void Page\_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("VDCIDIQ");
            dt.Columns.Add("VDCFFS");
            dt.Columns.Add("VDCHIM");
            dt.Columns.Add("VDCWEBHOSTING");
            dt.Columns.Add("VDCCWF");
            SqlDataReader myReader = null;
            SqlCommand cmd = new SqlCommand("SELECT ID, Name, VDCIDIQ , VDCFFS, VDCHIM, VDCWEBHOSTING, VDCCWF  from CMS\_Key\_Personnel where Name <> '   ' order by  Name");
            cmd.Connection = conn;
            conn.Open();
            myReader = cmd.ExecuteReader();
            while (myReader.Read())
            {
                DataRow dr = dt.NewRow();
                dr\[0\] = myReader\["ID"\].ToString();
                dr\[1\] = myReader\["Name"\].ToString();
                dr\[2\] = myReader\["VDCIDIQ"\].ToString();
                dr\[3\] = myReader\["VDCFFS"\].ToString();
                dr\[4\] = myReader\["VDCHIM"\].ToString();
                dr\[5\] = myReader\["VDCWEBHOSTING"\].ToString();
                dr\[6\] = myReader\["VDCCWF"\].ToString();
                dt.Rows.Add(dr);
            }
            
            gvKeyPersonnel.DataSource = dt;

[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?