Unable to insert null values in SQL Table using SQL bulk

excel sqlbulkcopy sql-server

Question

I am trying to upload Excel file data into SQL table,

below is my code

using (SqlConnection objCon = new SqlConnection(strCoon))
                {
                    objCon.Open();
                    SqlCommand sqlCommand = new SqlCommand("DeleteDump", objCon);
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;                      
                    sqlCommand.ExecuteNonQuery();       
                    using (System.Data.SqlClient.SqlBulkCopy sqlBulk = new System.Data.SqlClient.SqlBulkCopy(strCoon))
                    {
                        sqlBulk.BulkCopyTimeout = 0;
                        sqlBulk.DestinationTableName = "InformationDump";

                        for (int i = 0; i < DumpResources.GetLength(0); i++)
                        {
                            sqlBulk.ColumnMappings.Add(dt.Columns[i].ColumnName.ToString(), DumpResources[i, 1].ToString());
                        }
                        sqlBulk.WriteToServer(dt);// Getting error at this line
                    }
                }

I am getting error as The given value of type String from the data source cannot be converted to type datetime of the specified target column. Excel sheet I am trying to upload contains null data which I am unable to upload in destination table.

Popular Answer

I guess the issue is that you are trying to convert a null value to a string and then try to put it in a field that only accepts datetime. Check if there is anything (so notnull) before converting it to string.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why