"The ConnectionString property has not been initialized." Error in SQL bulkcopy upload

Question

"The ConnectionString property has not been initialized." Error in SQL bulkcopy upload

asp.net-mvc c# connection-string iis sqlbulkcopy

Question

I just created mvc 4 application,in that application I have function to upload Excel File to the database.

this is working fine in localhost .

But when I deploy in IIS and try to upload excel file.I'm getting following error

The ConnectionString property has not been initialized.

These are the connection strings I'm using to upload excel files

<add name="dbconnection" connectionString="Data Source="000.000.00.00";Initial Catalog=AFFHEC_DB;Persist Security Info=True;User ID=**;Password=****" providerName="System.Data.SqlClient" />
<add name="constr" connectionString="data source=000.000.0.000;Initial catalog=AFFHEC_DB;user id=**;password=*****;" />
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';" />
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'" />

This is the code for SQL bulk copy upload

    [HttpPost]
    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Student(HttpPostedFileBase FileUpload1, tbl_hec_Programme programme)
    {

        try
        {
            string conString = string.Empty;
            //Upload and save the file


           if (Request.Files["FileUpload1"].ContentLength > 1)
            {
                try
                {
                    string excelPath = Path.Combine(HttpContext.Server.MapPath("~/Content/"), Path.GetFileName(FileUpload1.FileName));
                    FileUpload1.SaveAs(excelPath);


                    string extension = System.IO.Path.GetExtension(FileUpload1.FileName);
                                            conString = string.Format(conString, excelPath);  
                }
                catch (Exception ex)
                {

                    this.SetNotification("The file type submitted is invalid", NotificationEnumeration.Error);

                }

                try
                {

                   using (OleDbConnection excel_con = new OleDbConnection(conString))
                    {
                        excel_con.Open();

                        DataTable dtExcelData = new DataTable();

                        string query =    "SELECT " +
                                          "s1.HEC_ID, " +
                                          "s1.Student_Personal_ID, " +
                                          "s1.Student_Passport_Number, " +
                                          "s1.Student_ID_by_University, " +
                                          "s1.First_Name, " +


                         //    // join three tables
                        "FROM (((([Personal_Data$] as s1) " +
                        "LEFT OUTER JOIN [Enrolment_Data$] as s2 ON s1.HEC_ID = s2.HEC_ID) " +

                        using (OleDbDataAdapter oda = new OleDbDataAdapter(query, excel_con))
                        {
                            oda.Fill(dtExcelData);
                        }

                        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                        using (SqlConnection con = new SqlConnection(consString))
                        {
                            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con,
                                                        SqlBulkCopyOptions.CheckConstraints |
                                                        SqlBulkCopyOptions.FireTriggers |
                                                        SqlBulkCopyOptions.KeepNulls |
                                                        SqlBulkCopyOptions.TableLock |
                                                        SqlBulkCopyOptions.UseInternalTransaction |
                                                        SqlBulkCopyOptions.KeepIdentity,
                                                        null))
                            {
                                //Set the database table name
                                sqlBulkCopy.DestinationTableName = "tbl_HEI_student";

                                sqlBulkCopy.BulkCopyTimeout = 0;

                                sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                                sqlBulkCopy.ColumnMappings.Add("Student_Personal_ID", "Student_Personal_ID_CPR");
                                sqlBulkCopy.ColumnMappings.Add("Student_Passport_Number", "Student_Passport_Number");
                                ................


                                con.Open();

                                try
                                {

                                    sqlBulkCopy.WriteToServer(dtExcelData);

                                    int totalRowsAdded = dtExcelData.Rows.Count;


                                    UploadStatusWriter(User.Identity.GetUserId(), Status_type, Date_type);



                                    this.SetNotification("Student data successfully uploaded", NotificationEnumeration.Success);



                                    return RedirectToAction("StudentIndex", "HEI");





                                }
                                catch (Exception ex)
                                {
                                    this.SetNotification(ex.Message, NotificationEnumeration.Error);
                                    return RedirectToAction("Student", "Excel");
                                }
                                finally
                                {

                                    con.Close();


                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    this.SetNotification(ex.Message, NotificationEnumeration.Error);
                    return RedirectToAction("Student", "Excel");
                }

            }

            else
            {
                this.SetNotification("Please select a file first and then click the submit button", NotificationEnumeration.Error);
                return RedirectToAction("Student", "Excel");

            }



        }
        catch (Exception ex)
        {
            return RedirectToAction("Student", "Excel");
        }

    }

kez

View more on Stack Overflow

Kelum Priyadarshane · Accepted Answer · 7/12/2015 4:00:57 PM

I had the same problem. After many many researches I found we should do some configurations in IIS Deployment as well as Allow permissions for release folder.

Here those configurations

Step 1

Add a proper site name
"Content Directory" Chose correct path of your release folder
For the binding, "Type" give https or http , if you deploy in your machine for IP address give your machine IP address else server IP Address and find a proper port also.
For application pool keep it as DefaultAppPool for now

enter image description here

Step 2

Then go to Applications Pools Find your web site's application pool and change it to ASP.NET v4.0 (if your .NET framework 4.0)

enter image description here

Step 3

Go to your web application main panel find Directory Browsing (under IIS section)

enter image description here

Step 4

Double click directory browsing and enable it

enter image description here

Step 5

Find ASP in your application on main panel(under IIS section)

enter image description here

Step 6

Right click on ASP find Enable Parent Path (under Behavior). Then make it value as True

enter image description here

Step 7

Then go to main panel in your IIS manger tool , under sites find your deployed site.Then right click it .Find explore go to location of that release folder

enter image description here

Step 8

Right click on that folder go to properties then give permission to relevant users

enter image description here

After above changes my problem solved hope yours too.

"The ConnectionString property has not been initialized." Error in SQL bulkcopy upload

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator