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

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

Question

I just developed an MVC 4 application with the ability to upload Excel files to databases.

this is successfully operating on localhost.

But when I attempt to submit an excel file when deployed in IIS. I'm encountering the next error

The ConnectionString property has not been initialized.

To upload excel files, I'm using these connection strings.

<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 SQL bulk copy upload code.

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

    }
1
1
7/2/2015 8:20:19 AM

Accepted Answer

That issue also affected me. After some study, I discovered that we must make some adjustments to IIS Deployment and provide access for the release folder.

These setups are here.

Step 1

  1. Add a suitable site name.

  2. Choose the proper release folder path in "Content Directory"

  3. Give "Type" https or http for the binding, your machine's IP address if you're deploying on it, otherwise the server's, and be sure to locate the right port as well.

  4. Keep the application pool at DefaultAppPool for the time being.

enter image description here

Step 2

after that click Applications Pools. Find the application pool for your website, then update it to v4.0 of ASP.NET (if your .NET framework 4.0)

enter image description here

Step 3

Locate Directory Browsing in the main panel of your web application (under IIS section)

enter image description here

Step 4

directory exploring by double clicking it and enableing it

enter image description here

Step 5

On the main panel of your application, locate ASP (under IIS section)

enter image description here

Step 6

Right-click ASP and then look for Turn on Parent Path (under Behavior). Make it value True after that.

enter image description here

Step 7

then locate your deployed site under sites in the main panel of your IIS manager tool. Right-click it after that. Find the release folder's location and go there.

enter image description here

Step 8

Right-click on that folder, choose properties, and then grant appropriate users access.

enter image description here

My issue was resolved with the aforementioned adjustments; perhaps, yours will be as well.

2
7/12/2015 4:00:57 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow