Click here to Skip to main content
15,039,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a gridview with a simple Insert, Update, Delete processes which works great.

I want to now have an 'update all' button that will instead of updating one row at a time in my gridview, updates all rows and their changes.

I want to achieve this without checkboxes. (closest example I've seen, but it complicates my form).

What's happening is, I am updating the values across the grid, I click update all, it looks like it's updated, the values are in the viewstate, but when I check the database table values, there are no changes.

Can anyone help?

What I have tried:

Code behind:
 
protected void gridview1_RowCommand(object sender, GridViewCommandEventArgs e)
{
gridview1 row = (GridViewRow)(((Control)e.CommandSource).NamingContainer);
string Id = e.CommandArgument.ToString();
DropDownList Ddllsit1 = gridview1.FooterRow.FindControl("Ddllsit1") as DropDownList;
TextBox box1 = (TextBox)row.FindControl("box1");
if (e.CommandName == "Insert")
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("Storeproc", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Action", "INSERT");
sqlCmd.Parameters.AddWithValue("@Id", Id);
sqlCmd.Parameters.AddWithValue("@type", Ddllsit1.SelectedItem.ToString());
sqlCmd.Parameters.AddWithValue("@value", box1.Trim());
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
}
else if (e.CommandName == "Update")
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("Storeproc", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Action", "UPDATE");
sqlCmd.Parameters.AddWithValue("@Id", Id);
sqlCmd.Parameters.AddWithValue("@type", Ddllsit1.SelectedItem.ToString());
sqlCmd.Parameters.AddWithValue("@value", box1.Trim());
sqlCmd.ExecuteNonQuery();
sqlCon.Close();
lblDeleteMessage.Text = "";
}
else if (e.CommandName == "Delete")
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand("Storeproc", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Action", "DELETE");
sqlCmd.Parameters.AddWithValue("@Id", Id);
sqlCmd.ExecuteNonQuery();
lblDeleteMessage.Text = "Deleted Successfully";
}
}

//To update all values on button click.

protected void UpdateValues()
{
 if (e.CommandName == "UpdateAll")
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
foreach (GridViewRow row1 in this.gridview1.Rows)
{
TextBox value = row1.FindControl("value") as TextBox;
DropDownList Ddllsit1 = row1.FindControl("Ddllsit1") as DropDownList;  
 
SqlCommand sqlCmd = new SqlCommand("Storeproc", sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@Action", "UPDATEALL");
sqlCmd.Parameters.AddWithValue("@Id", Id);
sqlCmd.Parameters.AddWithValue("@type", Ddllsit1.SelectedItem.ToString());
sqlCmd.Parameters.AddWithValue("@value", box1.Trim());
sqlCmd.ExecuteNonQuery();
}
sqlCon.Close();

 }
}

 protected void LnkUpdateAll_Click(object sender, EventArgs e)
        {
            UpdateValues();
        }
Posted
Updated 24-Jun-21 0:18am

1 solution

Hey my Friend!
You can batch commands, Like this (join all update SqlCommand in your gridview and execute)

static void Main(string[] args)
        {
            string sql1 = "BEGIN update Table1 set name = 'abc' where id = 10;",
                   sql2 = "update Table1 set name = 'bcd' where id = 11;",
                   sql3 = "update Table1 set name = 'efg' where id = 12;",
                   sql4 = "update Table1 set name = 'hjk' where id = 13; END;";

            // Join command (with StringBuilder.Append)
            string sql = string.Format("{0}{1}{2}{3}", sql1, sql2, sql3, sql4);

            using (SqlConnection conn = new SqlConnection())
            using (SqlCommand cmdUpdate = new SqlCommand(sql, conn))
            {
                conn.Open();
                cmdUpdate.ExecuteNonQuery();
                conn.Close();
            }
        }
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900