Can anyone tell me what is the wrong in my code
Error message is
"Source and destination tables column mismatch"
(SQL bulkcopy insert)
//=========================================================
my sql Table name is :dl_Temp_Investigation<br />
Columns are : <br />
GroupId<br />
TestCode<br />
TestName<br />
GroupName<br />
Charge<br />
Discount<br />
testno<br />
<br />
my Datatable name is: dt<br />
column are :<br />
GroupId<br />
TestCode<br />
TestName<br />
Group<br />
Charge<br />
Discount<br />
testno
//==============================================================
my code is as follows
//========================================
public int InsertInvestigation(DataTable dt)
{
int retVal = 0;
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["epracticedb_connectstring"];
SqlConnection sqlConn = null;
SqlCommand sqlCommand = null;
sqlConn = new SqlConnection(connectionString);
sqlConn.Open();
SqlTransaction lTransaction = sqlConn.BeginTransaction();
string aa = dt.Columns[6].ToString();
try
{
int rowcount = dt.Rows.Count;
using (SqlBulkCopy bcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, lTransaction))
{
bcopy.DestinationTableName = "dl_investigationFinal";
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[6].ToString(), "testno"));
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[0].ToString(), "GroupId"));
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[1].ToString(), "TestCode"));
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[2].ToString(), "TestName"));
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[3].ToString(), "GroupName"));
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[4].ToString(), "Charge"));
bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[5].ToString(), "Discount"));
bcopy.BatchSize = rowcount;
bcopy.NotifyAfter = 200;
bcopy.WriteToServer(dt);
}
lTransaction.Commit();
retVal = 1;
}
catch (Exception e)
{
lTransaction.Rollback();
throw;
retVal = 0;
}
finally
{
if (sqlCommand != null) sqlCommand.Dispose();
if (sqlConn != null)
{
if (sqlConn.State == ConnectionState.Open) sqlConn.Close();
sqlConn.Dispose();
}
}
return retVal;
}
[edit]Code block added - OriginalGriff[/edit]