asp.net grid view bulk updating all cells

Question

I am using a asp.net grid view to load data from my sql data table.I am able to successfully load the data.

Sql Table Desgin: 3 cloumns:Location,Firstname,LastName

Location is the primary key.

Design :

Aspxpage has a gridview which has two buttons in the bottom:

Edit
Save

When user hits Edit button all the cells in the gridview are made editable so that user can edit and save the values.

My problem arises with the save button where i am unable to save the edited data back to the SQL.

Here is the code for the save button click:

protected void btnSave_Click(object sender, EventArgs e)
{
    int RowIndex=0;

    GridViewRow row = (GridViewRow)gvres.Rows[RowIndex];

    TextBox txtLanguage1 = row.FindControl("txtFName") as TextBox;
    TextBox txtLanguage2 = row.FindControl("txtLName") as TextBox;

    SqlConnection myConnection = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UPDATE UsersTable SET FirstName = @FirstName, LastName = @LastName WHERE Location = @Location", myConnection);


    cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text.Trim());
    cmd.Parameters.AddWithValue("@LastName", txtLastName.Text.Trim());

    myConnection.Open();
    cmd.ExecuteNonQuery();
    gvusers.EditIndex = -1;
    DataBind();
}

Exception:"Must declare the scalar variable "@Location"."

@Location means that the Insert / Update expects that field to be passed in / added to your cmd.Parameters where are you adding @Location...? look at the line where you have this WHERE Location = @Location", myConnection); add / declare cmd.Parameters.AddWithValue("@Location", someLocationValue.Trim()); — MethodMan, Dec 13 '11 at 14:37
The RowIndex variable is hard coded to 0, so only the first row will be updated. If there's only 1 save button for the entire grid, that will be an issue. — Andy Wilson, Dec 13 '11 at 14:41
yeah thats what i found now .its only upadating the first row..So how do i loop through all the rows and update. — Macnique, Dec 13 '11 at 14:53

Andy Wilson · Accepted Answer · 2011-12-13T15:49:28.323

You need to add a parameter to the SqlCommand object named "@Location". You mention that Location is one of the columns in your grid---you can either read the value from the column (similar to how you are getting the first and last name values), or you can specify "Location" as the data key, and get it from the DataKeys property of the grid.

I'd look at the ASP.NET Real World Controls project on Codeplex. It allows for bulk editing and it smart enough to only update the rows that have changed.

score 2 · Answer 2 · answered Dec 13 '11 at 14:39

//@Location means that the Insert / Update expects that field to be passed in / added //to your cmd.Parameters where are you adding @Location...?

//look at the line where you have this WHERE Location = @Location", myConnection); //add declare cmd.Parameters.AddWithValue("@Location", someLocationValue.Trim());

score 2 · Answer 3 · answered Dec 13 '11 at 14:41

protected void btnSave_Click(object sender, EventArgs e)
{
    int RowIndex=0;

    GridViewRow row = (GridViewRow)gvres.Rows[RowIndex];

    TextBox txtLanguage1 = row.FindControl("txtFName") as TextBox;
    TextBox txtLanguage2 = row.FindControl("txtLName") as TextBox;
    TextBox txtLanguage3 = row.FindControl("txtLocation") as TextBox;


    SqlConnection myConnection = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("UPDATE UsersTable SET FirstName = @FirstName, LastName = @LastName WHERE Location = @Location", myConnection);

cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text.Trim());
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text.Trim());

cmd.Parameters.AddWithValue("@Location", txtLocation.Text.Trim());

myConnection.Open();
cmd.ExecuteNonQuery();
gvusers.EditIndex = -1;
DataBind();

}

asp.net grid view bulk updating all cells

3 Answers3

Linked