SqlBulkCopy inserting null instead of Double

Question

I'm trying to import excel files to my website then save its content in the sql server my problem with sqlbulkcopy when it fill the database some column filled with wrong values which is NULL ! while the column have a not null double values !

here is the code:

            void ImporttoDatatable()
{

    try
    {
        if (FileUpload3.HasFile)
        {
            string FileName = FileUpload3.FileName;
            string path = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload3.PostedFile.FileName));


            FileUpload3.PostedFile.SaveAs(path);




            using (OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=2\" "))
            {


                conn.Open();
                OleDbcon.Open();
                OleDbCommand command = new OleDbCommand("Select [IdOftable], [Time],[InstrumentLeftHand],[LeftSwitch],[LeftKnob],[ForceFeedbackLeftHand],[CumTimeLeftForceOverThreshold],[CumTimeLeftForceOver2xThreshold],[TranslationLeft_x],[TranslationLeft_y],[TranslationLeft_z],[quatLeft_x],[quatLeft_y],[quatLeft_z],[quatLeft_w],[InstrumentRightHand],[RightSwitch],[RightKnob],[ForceFeedbackRightHand],[CumTimeRightForceOverThreshold],[CumTimeRightForceOver2xThreshold],[TranslationRight_x],[TranslationRight_y],[TranslationRight_z],[quatRight_x],[quatRight_y],[quatRight_z],[quatRight_w],[BloodEmittedFrame],[BloodCurrentFrame],[TotalBloodEmitted],[TotalWhiteFibreCut],[TotalRedFibreCut],[Volume0_Brain],[Volume1_Tumor],[Volume2_Tumor] from [Sheet1$]", OleDbcon);

                //OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                 DbDataReader dr = command.ExecuteReader();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                bulkCopy.DestinationTableName =  "MyExcel";
                    try
                    {
                        bulkCopy.BulkCopyTimeout = 400;
                        bulkCopy.WriteToServer(dr);
                        bulkCopy.BatchSize = 16000;
                    }
                    catch (Exception ex)
                    {
                        Response.Write(ex.ToString());
                    }
                    finally
                    {
                        dr.Close();
                    }
                    OleDbcon.Close();
                    bulkCopy.Close();

                }
            }
        } lblmessage.Text = "The File Succssesfully Imported ";
    }
    catch (Exception ex)
    {
        Response.Write(ex.ToString());
    }
}

and this is the sql table definition:

        CREATE TABLE [dbo].[MyExcel] (
[IdOftable]                        NVARCHAR (50) NOT NULL,
[Time]                             FLOAT (53)    NULL,
[InstrumentLeftHand]               NVARCHAR (50) NULL,
[LeftSwitch]                       FLOAT (53)    NULL,
[LeftKnob]                         FLOAT (53)    NULL,
[ForceFeedbackLeftHand]            FLOAT (53)    NULL,
[CumTimeLeftForceOverThreshold]    FLOAT (53)    NULL,
[CumTimeLeftForceOver2xThreshold]  FLOAT (53)    NULL,
[TranslationLeft_x]                FLOAT (53)    NULL,
[TranslationLeft_y]                FLOAT (53)    NULL,
[TranslationLeft_z]                FLOAT (53)    NULL,
[quatLeft_x]                       FLOAT (53)    NULL,
[quatLeft_y]                       FLOAT (53)    NULL,
[quatLeft_z]                       FLOAT (53)    NULL,
[quatLeft_w]                       FLOAT (53)    NULL,
[InstrumentRightHand]              NVARCHAR (50) NULL,
[RightSwitch]                      FLOAT (53)    NULL,
[RightKnob]                        FLOAT (53)    NULL,
[ForceFeedbackRightHand]           FLOAT (53)    NULL,
[CumTimeRightForceOverThreshold]   FLOAT (53)    NULL,
[CumTimeRightForceOver2xThreshold] FLOAT (53)    NULL,
[TranslationRight_x]               FLOAT (53)    NULL,
[TranslationRight_y]               FLOAT (53)    NULL,
[TranslationRight_z]               FLOAT (53)    NULL,
[quatRight_x]                      FLOAT (53)    NULL,
[quatRight_y]                      FLOAT (53)    NULL,
[quatRight_z]                      FLOAT (53)    NULL,
[quatRight_w]                      FLOAT (53)    NULL,
[BloodEmittedFrame]                NVARCHAR (50) NULL,
[BloodCurrentFrame]                FLOAT (53)    NULL,
[TotalBloodEmitted]                FLOAT (53)    NULL,
[TotalWhiteFibreCut]               FLOAT (53)    NULL,
[TotalRedFibreCut]                 FLOAT (53)    NULL,
[Volume0_Brain]                    FLOAT (53)    NULL,
[Volume1_Tumor]                    FLOAT (53)    NULL,
[Volume2_Tumor ]                   FLOAT (53)    NULL,
PRIMARY KEY CLUSTERED ([IdOftable] ASC)
 );

There is no exceptions when i ran the code but when i checked the data inside the server it has nulls for some values which is not suppose to be null =(

I use visual studio express 2012 for web. What to do to correct it?

Thank You

I haven't checked the inner workings of `SqlBulkCopy.WriteToServer(IDataReader)`, but my guess would be that, since reading from a spreadsheet does not give a strongly typed reader, there is a fairly lax converter that uses `DbNull` when the convert fails. I had this problem yesterday, and found that the time spend adding the extra code to explicitly iterate the reader, and populate a strongly typed `DataTable` with explicit conversions, then writing this table to the server was faster than trying to reflect .NET to find out exactly why this was happening. — GarethD, Nov 06 '14 at 08:40
@e4rthdog thank you for your reply but When i change it to not null the exception "system.invalidoperationexception column 'column name' does not allow dbnull.value" will arise which means the same problem comes out with null or without it =( — seetah, Nov 06 '14 at 09:33
@GarethD thank you for your reply , i tried DataTable with DataAdapter "ignoring the amount of time it will takes " it comes up with DBNull exception that's why i change to the bulk but still the same problem ! — seetah, Nov 06 '14 at 09:45
I would suggest that you explicitly add `ColumnMappings` as per this [MSDN article](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmapping%28v=vs.110%29.aspx). I have found that this helps resolve ambiguity and ensures that your data map to the table correcly — Chris, Nov 06 '14 at 09:54
I suspect that your excel data are not ok..Maybe for example you have a space character in a numeric column that you cannot see and throws the exception. Try to trim your numeric cells and also check for decimal separators...Maybe your excel has , and you should have . or vice versa — e4rthdog, Nov 06 '14 at 10:15
This could be due to mixed data types, and ADO guessing the wrong type. Give this a read for some work arounds - http://blog.lab49.com/archives/196 — GarethD, Nov 06 '14 at 11:52

score 0 · Accepted Answer · answered Nov 09 '14 at 08:27

Thank You all for your help finally it works with me when i use what @GarethD said

i'll post the edited code in case of anyone need it :

 string name = (string)(Session["LoginUserName"]);
   if(FileUpload3.FileName == null ){

   lblmessage.Text = "Choose the file First then click import ";
   string display = "             Choose the file First then click import                   ";
   ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
   }
   else 
   {


       string path = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + name + Path.GetExtension(FileUpload3.PostedFile.FileName));


   FileUpload3.PostedFile.SaveAs(path);
   OleDbConnection dbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=2\" ");
    dbConnection.Open();
    try
   {       

       // Get the name of the first worksheet:
       DataTable dbSchema = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
       if (dbSchema == null || dbSchema.Rows.Count < 1)
       {
           throw new Exception("Error: Could not determine the name of the first worksheet.");
       }
       string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

       // Now we have the table name; proceed as before:
       OleDbCommand dbCommand = new OleDbCommand("Select [IdOftable], [Time],[InstrumentLeftHand],[LeftSwitch],[LeftKnob],[ForceFeedbackLeftHand],[CumTimeLeftForceOverThreshold],[CumTimeLeftForceOver2xThreshold],[TranslationLeft_x],[TranslationLeft_y],[TranslationLeft_z],[quatLeft_x],[quatLeft_y],[quatLeft_z],[quatLeft_w],[InstrumentRightHand],[RightSwitch],[RightKnob],[ForceFeedbackRightHand],[CumTimeRightForceOverThreshold],[CumTimeRightForceOver2xThreshold],[TranslationRight_x],[TranslationRight_y],[TranslationRight_z],[quatRight_x],[quatRight_y],[quatRight_z],[quatRight_w],[BloodEmittedFrame],[BloodCurrentFrame],[TotalBloodEmitted],[TotalWhiteFibreCut],[TotalRedFibreCut],[Volume0_Brain],[Volume1_Tumor],[Volume2_Tumor] from [" + firstSheetName + "]", dbConnection);
       OleDbDataReader dbReader = dbCommand.ExecuteReader();

       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
       {

           bulkCopy.DestinationTableName = "MyExcel";
           try
           {
               conn.Open();
               bulkCopy.BulkCopyTimeout = 400;
               bulkCopy.WriteToServer(dbReader);
               bulkCopy.BatchSize = 16000;
               conn.Close();

           }

           catch (Exception ex)
           {
               Response.Write(ex.ToString());
           }
           finally
           {
               bulkCopy.Close();
               dbReader.Close();
               //lblmessage.Text = " data successfully imported  ";
           //    Response.Write(@"<script language=""javascript"">alert('Details saved successfully')</script>");
               string display = "             Data Duccessfully Imported  Now Click On Calculate the result to calculate your results taking in your account The calculation will take a while , please wait   ";
               ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
           }



       }
   }
   finally
   {
       dbConnection.Close();
   }
}

SqlBulkCopy inserting null instead of Double

1 Answers1