Update existing GridView row data on CheckBox Click using C# in ASP.Net

lingers
 
on Jul 31, 2021 04:05 AM
Sample_114604.zip
817 Views

i have a set of check Box in gridview which is loaded with data from issuesheet table.

Please find below the table structure

INSERT [dbo].[issuesheet] ([id], [pid], [machine], [orderquantity], [workdocket], [cylinders], [dies], [embossings], [inks], [ints], [comment], [solvents])

Now on page load this issuesheet table is already loaded with

[id], [pid], [machine], [orderquantity], [workdocket] by default.

but the code below does not insert into the row as shown in my code, instead it forms a new rows.

So want i want is for the code to insert into the pid not form a row when (acceptedCylinder == "[]") .

My Code

protected void OnChckedChanged(object sender, EventArgs e)
{
    CheckBox chk = (CheckBox)sender;
    GridViewRow row = (GridViewRow)chk.NamingContainer;

    string number = row.Cells[2].Text;
    string pid = Label27.Text;

    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        string acceptedCylinder = GetAcceptedCylinder(pid);
        if (chk.Checked)
        {
            if (acceptedCylinder == "[]")
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO issuesheet (cylinders) VALUES(@cylinders) ", con))
                {
                    cmd.Parameters.AddWithValue("@cylinders", "['" + number.Trim() + "']");
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record inserted successfully.')", true);
            }
            else
            {
                List<string> cylinderNo = acceptedCylinder.Replace("[", "").Replace("]", "").Replace("'", "").Split(',').ToList();
                cylinderNo.Add(number);
                using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='" + pid + "'", con))
                {
                    cmd.Parameters.AddWithValue("@cylinders", "['" + string.Join("','", cylinderNo.Distinct()) + "']");
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record updated successfully.')", true);
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(acceptedCylinder))
            {
                List<string> cylinderNo = acceptedCylinder.Replace("[", "").Replace("]", "").Replace("'", "").Split(',').ToList();
                cylinderNo.Remove(number);
                using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='" + pid + "' ", con))
                {
                    cmd.Parameters.AddWithValue("@cylinders", "{'" + string.Join("','", cylinderNo.Distinct()) + "'}");
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Deleted successfully')", true);
            }
        }
    }
}

private string GetAcceptedCylinder(string pid)
{
    string acceptCylinder = "";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT cylinders FROM issuesheet WHERE pid=@pid ";
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@pid", pid);
            con.Open();
            acceptCylinder = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
        }
    }

    return acceptCylinder;
}

please help

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 05, 2021 03:58 AM

Hi lingers,

Refer below modified code.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindUserDetails1();
    }
}

protected void BindUserDetails1()
{
    string constr1 = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con1 = new SqlConnection(constr1))
    {
        using (SqlDataAdapter sda1 = new SqlDataAdapter("SELECT  * FROM issuesheet", con1))
        {
            // Response.Write(sda1);
            using (DataTable dt1 = new DataTable())
            {
                sda1.Fill(dt1);
                avDetails.DataSource = dt1;
                avDetails.DataBind();
            }
        }
    }

    //Required for jQuery DataTables to work.
    if (avDetails.Rows.Count > 0)
    {
        avDetails.UseAccessibleHeader = true;
        avDetails.HeaderRow.TableSection = TableRowSection.TableHeader;
    }
}

protected void OnChckedChanged(object sender, EventArgs e)
{
    CheckBox chk = (CheckBox)sender;
    GridViewRow row = (GridViewRow)chk.NamingContainer;

    string number = row.Cells[2].Text;
    string pid = Label27.Text;

    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        string acceptedCylinder = GetAcceptedCylinder(pid);
        if (chk.Checked)
        {
            if (acceptedCylinder == "[]")
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='" + pid + "'", con))
                {
                    cmd.Parameters.AddWithValue("@cylinders", "['" + number.Trim() + "']");
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record inserted successfully.')", true);
            }
            else
            {
                List<string> cylinderNo = acceptedCylinder.Replace("[", "").Replace("]", "").Replace("'", "").Split(',').ToList();
                cylinderNo.Add(number);
                using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='" + pid + "'", con))
                {
                    cmd.Parameters.AddWithValue("@cylinders", "['" + string.Join("','", cylinderNo.Distinct()) + "']");
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record updated successfully.')", true);
            }
        }
        else
        {
            if (!string.IsNullOrEmpty(acceptedCylinder))
            {
                List<string> cylinderNo = acceptedCylinder.Replace("[", "").Replace("]", "").Replace("'", "").Split(',').ToList();
                cylinderNo.Remove(number);
                using (SqlCommand cmd = new SqlCommand("UPDATE issuesheet SET cylinders = @cylinders where pid='" + pid + "' ", con))
                {
                    cmd.Parameters.AddWithValue("@cylinders", cylinderNo.Distinct().Count() > 0 ? "['" + string.Join("','", cylinderNo.Distinct()) + "']" : "[]");
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
                ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Deleted successfully')", true);
            }
        }
    }
}

private string GetAcceptedCylinder(string pid)
{
    string acceptCylinder = "";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT cylinders FROM issuesheet WHERE pid=@pid ";
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@pid", pid);
            con.Open();
            acceptCylinder = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
        }
    }

    return acceptCylinder;
}