SqlBulkCopy insérant null au lieu de double

asp.net c# excel sqlbulkcopy sql-server

Question

J'essaie d'importer des fichiers Excel sur mon site Web, puis enregistre son contenu sur le serveur SQL. Mon problème avec sqlbulkcopy lorsqu'il remplit la base de données de colonnes remplies de valeurs incorrectes qui est NULL! alors que la colonne a une double valeur non nulle!

voici le 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());
    }
}

et voici la définition de la table sql:

        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)
 );

Il n’ya pas d’exception quand j’ai exécuté le code, mais quand j’ai vérifié les données à l’intérieur du serveur, il contient des valeurs NULL pour certaines valeurs qui ne sont pas supposées être null = (

J'utilise Visual Studio Express 2012 pour le Web. Que faire pour le corriger?

Je vous remercie

Réponse acceptée

Merci à tous pour votre aide, enfin ça marche avec moi quand j'utilise ce que @GarethD a dit

Je posterai le code modifié au cas où quelqu'un en aurait besoin:

 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();
   }
}


Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi