Click here to Skip to main content
15,892,809 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to import excel sheet and store that data to sql server this is what my basic requirement so i did it successfully on local machine and uploaded on godaddy now the excel sheet is being saved in the folder on godaddy but the database is not getting updated accordingly the following is the exception and description.

Description: The application attempted to perform an operation not allowed by the security policy. To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.


this is the code:
Source Error:


Line 44: using (OleDbConnection excel_con = new OleDbConnection(conString))
Line 45: {
Line 46: excel_con.Open();
Line 47: string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
Line 48: DataTable dtExcelData = new DataTable();

Source File: g:\pleskvhosts\patientshealthyselves.com\brainlines\starsforum.org\Forum\Import_Org_Excel.aspx.cs Line: 46

and this is stack trace:
Stack Trace:


[SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.]
System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) +0
System.Security.CodeAccessSecurityEngine.Check(PermissionSet permSet, StackCrawlMark& stackMark) +31
System.Security.PermissionSet.Demand() +68
System.Data.Common.DbConnectionOptions.DemandPermission() +40
System.Data.OleDb.OleDbConnection.PermissionDemand() +47
System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection) +20
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +146
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +16
System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +17
System.Data.OleDb.OleDbConnection.Open() +42
Import_Org_Excel.Upload(Object sender, EventArgs e) in g:\pleskvhosts\Domain\...\...\Forum\Import_Org_Excel.aspx.cs:46
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9628722
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +6704
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +245
System.Web.UI.Page.ProcessRequest() +72
System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) +21
System.Web.UI.Page.ProcessRequest(HttpContext context) +58

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34248



why this is hapening ??please tell me i will be very thankful if resolved this issue....
thanks
Posted
Comments
aarif moh shaikh 8-Sep-15 6:50am    
This is Permission issue ... So you have to give security permission on godaddy server
F-ES Sitecore 8-Sep-15 7:16am    
Try saving the file to the app_data folder, you should have write access to that folder already.

1 solution

You have to give full permission(Read,Write,Modify,Delete) on folder where you store your excel file on server.

If you are not storing it on server then first you have to store it in a folder on server and then make connection string according to that location. Please make sure that your folder where you store excel file have full permission(Read,Write,Modify,Delete).
 
Share this answer
 
Comments
Member 11932995 8-Sep-15 7:26am    
yess i had already given the write permission to the folder where excel sheets will reside....fact is excel sheets are getting stored but the database is not getting updated accordingly
[no name] 8-Sep-15 7:36am    
You have to first upload excel in a folder and then make connection string with the location of uploaded excel file on server using Server.MapPath(); Only then you will be able to read excel file and save data in database.
Member 11932995 8-Sep-15 8:20am    
protected void Upload(object sender, System.EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
string DuplicateEtry = "These Records are Already Present..!";
FileUpload1.SaveAs(excelPath);

string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
//select virsion of Excel 2007 0r 2010
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);

try
{
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();

//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[19] { new DataColumn("NGOName", typeof(string)),
new DataColumn("RegiID", typeof(int)),
new DataColumn("ExName", typeof(string)),
new DataColumn("Designation", typeof(string)),
new DataColumn("Skill", typeof(string)),
new DataColumn("Membership", typeof(string)),
new DataColumn("Address ", typeof(string)),
new DataColumn("City ", typeof(string)),
new DataColumn("Pin ", typeof(int)),
new DataColumn("District ", typeof(string)),
new DataColumn("State ", typeof(string)),
new DataColumn("Country ", typeof(string)),
new DataColumn("Landline ", typeof(int)),
new DataColumn("Con1", typeof(string)),
new DataColumn("Con2 ", typeof(string)),
new DataColumn("Email1 ", typeof(string)),
new DataColumn("Email2 ", typeof(string)),
new DataColumn("Website ", typeof(string)),
new DataColumn("Description", typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();

string consString = ConfigurationManager.ConnectionStrings["CS2"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.ORG_Tb";

//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("NGOName", "NGOName");
sqlBulkCopy.ColumnMappings.Add("RegiID", "RegiID");
sqlBulkCopy.ColumnMappings.Add("ExName", "ExName");
sqlBulkCopy.ColumnMappings.Add("Designation", "Designation");
sqlBulkCopy.ColumnMappings.Add("Skill", "Skill");
sqlBulkCopy.ColumnMappings.Add("Membership", "Membership");
sqlBulkCopy.ColumnMappings.Add("Address", "Address");
sqlBulkCopy.ColumnMappings.Add("City", "City");
sqlBulkCopy.ColumnMappings.Add("Pin

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900