insert statement not working, instead it updates even if the cylinder column is empty for the associated pid value
Label27.Text = "206";
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 (string.IsNullOrEmpty(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='" + Label27.Text + "'", 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='" + Label27.Text + "' ", 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", Label27.Text);
con.Open();
acceptCylinder = Convert.ToString(cmd.ExecuteScalar());
con.Close();
}
}
return acceptCylinder;
}
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,
[dies] [nvarchar](255) NULL,
[embossings] [nvarchar](255) NULL,
[inks] [nvarchar](255) NULL,
[ints] [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], [dies], [embossings], [inks], [ints], [comment], [solvents]) VALUES (1, 206, N'machine', 67543, N'bbbbb', N'[]', N'[]', N'[]', N'[]', NULL, NULL, N'[]')
GO
SET IDENTITY_INSERT [dbo].[issuesheet] OFF
GO
ALTER TABLE [dbo].[issuesheet] ADD DEFAULT ('[]') FOR [cylinders]
GO
ALTER TABLE [dbo].[issuesheet] ADD DEFAULT ('[]') FOR [dies]
GO
ALTER TABLE [dbo].[issuesheet] ADD DEFAULT ('[]') FOR [embossings]
GO
ALTER TABLE [dbo].[issuesheet] ADD DEFAULT ('[]') FOR [inks]
GO
ALTER TABLE [dbo].[issuesheet] ADD DEFAULT ('[]') FOR [solvents]
GO
please help