Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The excel file is getting stored on server but the database and hence the grid is supposed to get updated but here why it is not getting updated? Please tell me the solution thanks...!
C#
private String uploadAttachmentOnServer(FileUpload fileUpload)
    {
        String fileName = null;
        int filesize = 0;

        try
        {
            fileName = fileUpload.FileName;
            filesize = fileUpload.FileBytes.Length;
            String fileType;
            String fileSize;
            String destinationPath = Server.MapPath("./Files/"); // ExcelFiles is folder name


            String extension = Uploader.fileUpload(fileUpload, destinationPath, out fileType, out fileSize);
            String fileVirtualPath = null;
            if (extension != null)
            {
                fileVirtualPath = "../Files/" + fileUpload.FileName;
                return destinationPath + extension;
            }
            return null;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return null;
    }


C#
protected void Upload(object sender, System.EventArgs e)
    {
        //Upload and save the file

       // string excelPath = Server.MapPath(string.Concat("/brainlines.in/starsforum.org/Forum/Files/", FileUpload1.PostedFile.FileName));

        String excelPath = uploadAttachmentOnServer(FileUpload1); //Please used this line for path & calling fuction uploadAttachmentOnServer

        //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", "Pin");
                        sqlBulkCopy.ColumnMappings.Add("District", "District");
                        sqlBulkCopy.ColumnMappings.Add("State", "State");
                        sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                        sqlBulkCopy.ColumnMappings.Add("Landline", "Landline");
                        sqlBulkCopy.ColumnMappings.Add("Con1", "Con1");
                        sqlBulkCopy.ColumnMappings.Add("Con2", "Con2");
                        sqlBulkCopy.ColumnMappings.Add("Email1", "Email1");
                        sqlBulkCopy.ColumnMappings.Add("Email2", "Email2");
                        sqlBulkCopy.ColumnMappings.Add("Website", "Website");
                        sqlBulkCopy.ColumnMappings.Add("Description", "Description");

                        con.Open();
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        con.Close();

                        ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "Message", "alert('Record Added Sucessfully')", true);
                    }
                }

            }
}
Posted
Updated 8-Sep-15 23:18pm
v2
Comments
Suvabrata Roy 9-Sep-15 5:29am    
is there any error ?
Member 11932995 9-Sep-15 7:16am    
this is the error :

Line 75: using (OleDbConnection excel_con = new OleDbConnection(conString))
Line 76: {
Line 77: excel_con.Open();
Line 78: string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
Line 79: DataTable dtExcelData = new DataTable();

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.

[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\patientshealthyselves.com\brainlines\starsforum.org\Forum\Import_Org_Excel.aspx.cs:77
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
ASP.starsforum_org_forum_import_org_excel_aspx.ProcessRequest(HttpContext context) in App_Web_lwoqhtny.12.cs:0
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +341
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +69
Sinisa Hajnal 9-Sep-15 8:34am    
Request for the permission of type oledbpermission failed.

You're not connecting to the database properly.
Sinisa Hajnal 9-Sep-15 5:32am    
Set a breakpoint in the code and check that the file is correctly loaded. Check that the table is filled. Debug the app...try with single row instead of full excel list etc.
Schatak 9-Sep-15 6:25am    
what is the issue?

1 solution

The error can be caused by various things. I suggest you google, "Request for the permission of type 'System.Data.OleDb.OleDbPermission failed" and use the links to troubleshoot the issue.
 
Share this answer
 

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