"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
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
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)
Step 3
Go to your web application main panel find Directory Browsing (under IIS section)
Step 4
Double click directory browsing and enable it
Step 5
Find ASP in your application on main panel(under IIS section)
Step 6
Right click on ASP find Enable Parent Path (under Behavior). Then make it value as True
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
Step 8
Right click on that folder go to properties then give permission to relevant users
After above changes my problem solved hope yours too.