Click here to Skip to main content
12,750,710 members (36,271 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET MS-Excel
hi everyone,
When i try to upload excel sheet on asp.net gridview, my coding works in IE, but doesn't work in Chrome and mozilla, its give below error, why so? plz suggest...

"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."
Posted 21-Aug-12 2:49am
Updated 13-Sep-16 7:14am
Comments
@amitgajjar 21-Aug-12 8:21am
   
can you post your code for reading your uploaded file ?
Wes Aday 21-Aug-12 9:05am
   
Suggest what exactly? The error message is perfectly clear.
Gopal Rakhal 22-Aug-12 2:04am
   
//sqlconnection
string StrCon = ConfigurationManager.ConnectionStrings["idol"].ToString();
SqlConnection objcon = new SqlConnection(StrCon);
objcon.Open();
//oledbconnection
string filepath = FileUpload1.PostedFile.FileName;
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
string stroledbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 ";
// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";


OleDbConnection oledbCon = new OleDbConnection(stroledbCon);
oledbCon.Open();
string strOledbQuery = "select * from [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(strOledbQuery, oledbCon);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strinsert = "insert into student_tbl(slNo,Rollno,Name) VALUES ('"+dr[0].ToString ()+"','" + dr[1].ToString() + "','" + dr[2].ToString() + "')";
SqlCommand cmd2 = new SqlCommand(strinsert, objcon);
cmd2.ExecuteNonQuery();
}
@amitgajjar 22-Aug-12 2:12am
   
Use Improve question link to update your question. this is not readable.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Controls and Properties:

1. FileUpload Control. name=”FileUpload1″

2. Button. name=”btnUpload” Text=”Upload”

3. GridView: name=”GridView1″


NameSpaces:


using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

 


Source Code:
<form id="form1"  runat="server">
 <div>
 <asp:fileupload id="FileUpload1" runat="server" forecolor="#993399" xmlns:asp="#unknown" />
       <asp:button id="btnUpload" runat="server" text="Upload" onclick="btnUpload_Click" xmlns:asp="#unknown" />
       <asp:gridview id="GridView1" runat="server" xmlns:asp="#unknown">
          OnPageIndexChanging="PageIndexChanging" BackColor="White" BorderColor="#999999"
          BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">

aspx.cs Page Code:

1. Uploading an Excel File:
protected void btnUpload_Click(object sender, EventArgs e)
 {
        if (FileUpload1.HasFile)
 {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension);
        }
    }

2. Import Excel File in GridView Control:protected void Page_Load(object sender, EventArgs e)
private void Import_To_Grid(string FilePath, string Extension)
 {
        string conStr = "";
        switch (Extension)
 {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, 1);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        cmdExcel.Connection = connExcel;
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();
        GridView1.DataSource = dt;
 GridView1.DataBind();

    }

protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
 {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);
        Import_To_Grid(FilePath, Extension);
 GridView1.PageIndex = e.NewPageIndex;
 GridView1.DataBind();
    }
  Permalink  
v2
Comments
santosh_k 21-Aug-12 16:20pm
   
Go through this code it will help you to solve your question..:)
@amitgajjar 22-Aug-12 2:13am
   
don't forget to add pre tag for code.
Gopal Rakhal 22-Aug-12 11:52am
   
thanks.....
santosh_k 22-Aug-12 12:31pm
   
ok sure...thanks
Kuthuparakkal 22-Aug-12 22:47pm
   
good answer 5+..
santosh_k 22-Aug-12 23:44pm
   
thanks :)..
Member 12487609 13-Sep-16 12:13pm
   
when I use pagging the grideview I see error "There is no row at position 0." in line" string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();"
ppolymorphe 14-Sep-16 4:43am
   
This is not an open discussion.
In this forum, you ask a new question, and you get answers
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Go through your code and see where you get the data and send to database. There must be some missing closed object.

Here is a complete article which will help you,
http://csharpdotnetfreak.blogspot.com/2011/12/upload-and-read-excel-file-in-aspnet.html[^]

Hope this helps
cheers
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

it is a permissions issue, please give read/write permissions to IUSER account on that particular folder and on that file...also make sure that file is not being used by any other process in the system...

You should give security permissions to the application pool user if you use IIS 7 hosting.

Give "read and write" permission to "network service" user on the file, then reboot your computer. It would be working fine

I think that will solve your problem...
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 8

I recommend you to use ready and tested packages with more features instead of writing your own code. the one i can recommend is EPPlus with great features.
you can download it via NuGet:
https://www.nuget.org/packages/EPPlus/[^]
  Permalink  
Comments
Maciej Los 14-Sep-16 4:29am
   
Why to post an answer to the question with approved answer posted almost 4 years ago?
zamanipour 14-Sep-16 6:37am
   
you are right. but it was in quick answers list! maybe because of an reply down in solution 4. I'm new here and i will keep in mind to check the question date. thanks for mentioning that.
Maciej Los 14-Sep-16 10:16am
   
I'd suggest to remove it to avoid down-voting.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

it was not a permission issue rather it was location issue and i solved it by getting hint from santosh_k answer..
  Permalink  

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

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170215.1 | Last Updated 14 Sep 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100