Sqlbulkcopy doesn't seem to work for me

Question

I created a datatable and am trying to insert this datatable via SqlBulkCopy, but somehow this does not work for me ....

I got an error

The given value of type DateTime from the data source cannot be converted to type decimal of the specified target column. 

My data source

 DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("EmpId", typeof(Int64))); dt.Columns.Add(new DataColumn("FromDate", typeof(DateTime))); dt.Columns.Add(new DataColumn("ToDate", typeof(DateTime))); dt.Columns.Add(new DataColumn("DaysPresent", typeof(decimal))); dt.Columns.Add(new DataColumn("OpeningAdvance", typeof(double))); dt.Columns.Add(new DataColumn("AdvanceDeducted", typeof(double))); dt.Columns.Add(new DataColumn("RemainingAdvance", typeof(double))); dt.Columns.Add(new DataColumn("SalaryGiven", typeof(double))); dt.Columns.Add(new DataColumn("CreatedDate", typeof(DateTime))); foreach (GridViewRow row in gridEmployee.Rows) { if (row.RowType == DataControlRowType.DataRow) { DataRow dr = dt.NewRow(); dr["EmpId"] = Convert.ToInt64(((HiddenField)row.Cells[0].FindControl("HiddenId")).Value); dr["FromDate"] = Convert.ToDateTime(GetMonthNumberFromAbbreviation(fromdate[1].ToString()) + '/' + fromdate[0].ToString() + '/' + fromdate[2].ToString()); dr["ToDate"] = Convert.ToDateTime(GetMonthNumberFromAbbreviation(todate[1].ToString()) + '/' + todate[0].ToString() + '/' + todate[2].ToString()); dr["DaysPresent"] = Convert.ToDecimal(((TextBox)row.Cells[3].FindControl("TxtDaysPresent")).Text); dr["OpeningAdvance"] = Convert.ToDouble(((TextBox)row.Cells[4].FindControl("txtOpeningAdv")).Text); dr["AdvanceDeducted"] = Convert.ToDouble(((TextBox)row.Cells[5].FindControl("TxtAdvanceDeducted")).Text); dr["RemainingAdvance"] = Convert.ToDouble(((TextBox)row.Cells[6].FindControl("TxtClosingAdvance")).Text); dr["SalaryGiven"] = Convert.ToDouble(((TextBox)row.Cells[7].FindControl("TxtSalary")).Text); dr["CreatedDate"] = Convert.ToDateTime(System.DateTime.Now.ToString()); dt.Rows.Add(dr); } } SqlBulkCopy sbc = new SqlBulkCopy(connectionString); sbc.DestinationTableName = "SalaryDetails"; sbc.WriteToServer(dt); sbc.Close(); 

And my destination table looks like this:

alt text http://img231.imageshack.us/img231/5448/mytable.jpg

Answer 1

You need to specify a column mapping, since you do not have the same number of columns in the data table and destination table.

You can do it as follows:

 sbc.ColumnMappings.Add("EmpId", "EmpId"); sbc.ColumnMappings.Add("FromDate", "FromDate"); // and so on, with the rest of the columns; and after that comes sbc.WriteToServer(dt); sbc.Close();