Hello Everyone,
I'm checking the records before inserting but it's still inserting the records. Can you please check my below code. The gridview can be generated by this Link Previous Question
protected void Button2_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4] { new DataColumn("ID", typeof(int)),
new DataColumn("Year", typeof(int)),
new DataColumn("Quarter", typeof(string)),
new DataColumn("Value",typeof(string))});
foreach (GridViewRow row in gvYearQuarter.Rows)
{
Label id1 = (Label)gvYearQuarter.Rows[0].Cells[0].FindControl("lblid");
int ID = int.Parse(id1.Text);
int Year = int.Parse(row.Cells[1].Text);
string Quarter = row.Cells[2].Text;
TextBox Value1 = (TextBox)gvYearQuarter.Rows[0].Cells[3].FindControl("txtValue");
float Value = float.Parse(Value1.Text);
//string updateid = row.Cells[4].Text;
dt.Rows.Add(ID, Year, Quarter, Value);
SqlConnection con = new SqlConnection(ConString);
string s = @"SELECT COUNT(*) FROM KPI_Yearly WHERE id = @id and year=@year";
sCommand = new SqlCommand(s, con);
sCommand.Parameters.AddWithValue("@id", ID);
sCommand.Parameters.AddWithValue("@year", Year);
con.Open();
int records = (int)sCommand.ExecuteScalar();
if (records == 0)
{
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.KPI_Quaterly";
//[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("ID", "ID");
sqlBulkCopy.ColumnMappings.Add("Year", "Year");
sqlBulkCopy.ColumnMappings.Add("Quarter", "Quarter");
sqlBulkCopy.ColumnMappings.Add("Value", "Value");
//sqlBulkCopy.ColumnMappings.Add("updateid", "updateid");
con.Close();
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
div_sucess.Visible = true;
string message = "KPI data added to the system.";
string script = "window.onload = function(){ alert('";
script += message;
script += "');";
script += "window.location = '";
script += Request.Url.AbsoluteUri;
script += "'; }";
ClientScript.RegisterStartupScript(this.GetType(), "SuccessMessage", script, true);
}
}
}
else
{
}
}
}
Thanks