i have this gridview, i want their checkbox checked if their posi no is in the database on page load from the table cylinder
i have three(3) cylinders available with posino on issuesheet N'['A101','A102','A103']
On page load i want the available cylinders whose posino appears on the issue sheet to be checked (check box)
My code
CREATE TABLE [dbo].[issuesheet](
[id] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NULL,
[machine] [nvarchar](255) NULL,
[orderquantity] [float] NULL,
[workdocket] [nvarchar](50) NULL,
[cylinders] [nvarchar](255) NULL,
[comment] [nvarchar](255) NULL,
[solvents] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[issuesheet] ON
GO
INSERT [dbo].[issuesheet] ([id], [pid], [machine], [orderquantity], [workdocket], [cylinders]) VALUES (1, 206, N'Griller', 67543, N'DCK001', N'['A101','A102','A103'])
GO
SET IDENTITY_INSERT [dbo].[issuesheet] OFF
GO
ALTER TABLE [dbo].[issuesheet] ADD DEFAULT ('[]') FOR [cylinders]
GO
/////////////////////////////////////////////////////////////////////
Cylinder Table
CREATE TABLE [dbo].[cylinder](
[id] [int] NULL,
[posino] [nvarchar](50) NULL,
[pid] [nvarchar](50) NULL,
[diameter] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (1, N'A101', N'206', N'25mm')
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (2, N'A102', N'206', N'24mm')
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (3, N'A103', N'206', N'26mm')
GO
INSERT [dbo].[cylinder] ([id], [posino], [pid], [diameter]) VALUES (4, N'A104', N'206', N'27mm')
public partial class gridcontinue : System.Web.UI.Page
{
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 cylinde ", con1))
{
// Response.Write(sda1);
using (DataTable dt1 = new DataTable())
{
sda1.Fill(dt1);
avDetails.DataSource = 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[1].Text;
string pid ="206";
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='206'", con))
{
cmd.Parameters.AddWithValue("@cylinders", "['" + number.Trim() + "']");
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('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='206'", con))
{
cmd.Parameters.AddWithValue("@cylinders", "['" + string.Join("','", cylinderNo.Distinct()) + "']");
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('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='206' ", con))
{
cmd.Parameters.AddWithValue("@cylinders", cylinderNo.Distinct().Count() > 0 ? "['" + string.Join("','", cylinderNo.Distinct()) + "']" : "[]");
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('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", 206);
con.Open();
acceptCylinder = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
return acceptCylinder;
}
}