insert data using WriteToServer method datetime column gives exception

Question

insert data using WriteToServer method datetime column gives exception

c# datetime sqlbulkcopy sql-server

Question

I am trying to insert a DateTime into a column who data type is datetime. The value is assigned using DateTime.Now, so I know that there isn't an issue with incorrect types in the code.

The exception I get is:

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

This is how I am specifying my datatable columns:

DataTable HODetails = new DataTable();
HODetails.Columns.Add("MasterID", typeof(long));
HODetails.Columns.Add("ItemID", typeof(int));
HODetails.Columns.Add("SubCategoryID", typeof(int));
HODetails.Columns.Add("BatchNo", typeof(string));
HODetails.Columns.Add("ExpiryDate", typeof(DateTime));

And here is how I am setting the data:

HODetails.Rows[HODetails.Rows.Count - 1]["ItemID"] = Convert.ToInt32(lblItemId.Text);
HODetails.Rows[HODetails.Rows.Count - 1]["MasterID"] =HOReceipt_ID;
HODetails.Rows[HODetails.Rows.Count - 1]["SubCategoryID"] = SubCatID;
HODetails.Rows[HODetails.Rows.Count - 1]["BatchNo"] = Convert.ToString(txtBatchNo.Text == "" ? "" : txtBatchNo.Text);
HODetails.Rows[HODetails.Rows.Count - 1]["ExpiryDate"] = DateTime.Now;

So why does WriteToServer() think that a "value of type String from the data source cannot be converted to type datetime"?

Neha Thakur

View more on Stack Overflow

Accepted Answer

my problem is solved i change my writeToServer method

                if (DtWithTableName.Rows.Count > 0)
                {
                using (SqlBulkCopy s = new SqlBulkCopy(strConnection))
                {

                try
                {
                    s.DestinationTableName = "HOMaterialReceiptDetails";
                    s.ColumnMappings.Add("MasterID", "MasterID");
                    s.ColumnMappings.Add("ItemID", "ItemID");
                    s.ColumnMappings.Add("SubCategoryID", "SubCategoryID");
                    s.ColumnMappings.Add("ExpiryDate", "ExpiryDate");
                    s.ColumnMappings.Add("BrandName", "BrandName");
                    s.ColumnMappings.Add("Qty", "Qty");
                    s.ColumnMappings.Add("FreeQty", "FreeQty");
                    s.ColumnMappings.Add("ReturnQty", "ReturnQty");
                    s.ColumnMappings.Add("ReplacementQty", "ReplacementQty");
                    s.WriteToServer(DtWithTableName);
                }
                catch (Exception)
                {

                    IsInserted = false;
                }

            }
        }

insert data using WriteToServer method datetime column gives exception

Question

Accepted Answer

Performance

Expression Evaluator