Query summary: the datetime column in a dataset(fetched from MySQL) is of type MySQLDateTime, which holds null values as 0000-00-00, which is incompatible when i try to bulk insert in SQL server....which gives this error
MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime
the code is as follows;MySqlConnection connection = new MySqlConnection(mysqlConnString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM ` orders`;";
MySqlDataAdapter myda = new MySqlDataAdapter(command.CommandText, connection);
myda.Fill(ds, sTableName);
the above works find and ds include the northwind''s order table''s empty date values
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(trans.Connection, SqlBulkCopyOptions.Default, trans))
{
bulkcopy.DestinationTableName = "["+sTableName+"]";
bulkcopy.WriteToServer(ds.Tables[0]);
cConvertor.AddTextOutputtoFile(ds.Tables[0].Rows.Count.ToString() + "row(s) have been added to " + sTableName + ".");
}
Please advise
解决方案Before doing your bulk copy, you need to check your dataset and replace 0000-00-00 values by null.
If you created the SqlBulkCopy class, this task should belong to it.
Before doing your bulk copy, you need to check your dataset and replace 0000-00-00 values by null.
If you created the SqlBulkCopy class, this task should belong to her.