How to avoid duplicate data before inserting. I used the below code but I'm getting this error.
No column name was specified for column 1 of 'temp'.
No column name was specified for column 2 of 'temp'.
No column name was specified for column 3 of 'temp'.
No column name was specified for column 4 of 'temp'.
No column name was specified for column 5 of 'temp'.
No column name was specified for column 6 of 'temp'.
No column name was specified for column 8 of 'temp'.
No column name was specified for column 9 of 'temp'.
Invalid column name 'datelist'.
using (SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
var fromdate = DateTime.Parse(txtfromdate.Text);
var todate = DateTime.Parse(txttodate.Text);
var datedif1 = (todate - fromdate).Days;
var sqlInsert = new SqlCommand("INSERT INTO datelist ([datedif],[batch],[daywk],[semester],[weekbatch],[subject],[facultyname],[facultyid],[WeekMonth]) SELECT * from (SELECT @datedif,@batch,@daywk,@semester,@weekbatch,@subject,@facultyname,@facultyid,@weekMonth) AS temp WHERE NOT EXISTS (SELECT datelist,batch from datelist where batch=@batch and datedif=@datedif)", con2);
var sqlParamater = sqlInsert.Parameters.Add("@datedif", SqlDbType.Date);
var sqlParameter1 = sqlInsert.Parameters.Add("@batch", SqlDbType.NVarChar);
var sqlParameter2 = sqlInsert.Parameters.Add("@daywk", SqlDbType.NVarChar);
var sqlParameter3 = sqlInsert.Parameters.Add("@semester", SqlDbType.NVarChar);
var sqlParameter4 = sqlInsert.Parameters.Add("@weekbatch", SqlDbType.NVarChar);
var sqlParameter5 = sqlInsert.Parameters.Add("@subject", SqlDbType.NVarChar);
var sqlParameter6 = sqlInsert.Parameters.Add("@facultyname", SqlDbType.NVarChar);
var sqlParameter7 = sqlInsert.Parameters.Add("@facultyid", SqlDbType.NVarChar);
var sqlParameter8 = sqlInsert.Parameters.Add("@WeekMonth", SqlDbType.NVarChar);
con2.Open();
// try
//{
for (var i = 0; i <= datedif1; i++)
{
var consecutiveDate = fromdate.AddDays(i);
sqlParamater.Value = consecutiveDate;
sqlParameter1.Value = batch1;
sqlParameter2.Value = dayweek;
sqlParameter3.Value = semester;
sqlParameter4.Value = weekbatch;
sqlParameter5.Value = subject;
sqlParameter6.Value = faculty;
sqlParameter7.Value = facultyid;
sqlParameter8.Value = weekmonth;
int s = sqlInsert.ExecuteNonQuery();
}
}
I'll admit to not having tested this, but given the error message, the subquery:
SELECT
@datedif,
@batch,
-- and so on
@weekMonth
) AS temp
needs to have column names defined, as the value comes from a variable.
That would be:
SELECT
@datedif as datedif,
@batch as batch,
-- and so on
@weekMonth as weekMonth
) AS temp