Comments by Member 11932995 (Top 11 by date)

yes i want to automate it but actually i want to move the excel table data to contact detail table where the person id is refferd from master table

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

issue is the database on server is not getting updated by fetching the data from these excel files and storing it to the database....

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

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

thanks for solution but i tried it edited code accordingly and uploaded on domain but its still showing this error now
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IO.DirectoryNotFoundException: Could not find a part of the path '....\Forum\Files\Indvisual.xlsx'.

m sorry may be this is very common mistake but as m novice here so could not understand these small things please correct me to fix this issue