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

    }

Accepted Answer

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

  1. Add a proper site name

  2. "Content Directory" Chose correct path of your release folder

  3. 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.

  4. 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.




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