Click here to Skip to main content
15,064,915 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 13-Sep-16 6:14am
Comments
AmitGajjar 21-Aug-12 8:21am
   
can you post your code for reading your uploaded file ?
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.
[no name] 21-Aug-12 9:05am
   
Suggest what exactly? The error message is perfectly clear.

Controls and Properties:

1. FileUpload Control. name=”FileUpload1″

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

3. GridView: name=”GridView1″


NameSpaces:

C#
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:
ASP.NET
<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">
          <headerstyle cssclass="hdr" backcolor="#000084" font-bold="True">
             ForeColor="White" />
          <alternatingrowstyle backcolor="#DCDCDC" />
          <footerstyle cssclass="ftr" backcolor="#CCCCCC" forecolor="Black" />
          <pagerstyle backcolor="#999999" forecolor="Black" horizontalalign="Center" />
          <rowstyle backcolor="#EEEEEE" forecolor="Black" />
          <selectedrowstyle backcolor="#008A8C" font-bold="True" forecolor="White" />
          <sortedascendingcellstyle backcolor="#F1F1F1" />
          <sortedascendingheaderstyle backcolor="#0000A9" />
          <sorteddescendingcellstyle backcolor="#CAC9C9" />
          <sorteddescendingheaderstyle backcolor="#000065" />
       </headerstyle></asp:gridview>
 </div>
</form>

aspx.cs Page Code:

1. Uploading an Excel File:
C#
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)
C#
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();
    }
   
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.
santosh_k 22-Aug-12 12:31pm
   
ok sure...thanks
Gopal Rakhal 22-Aug-12 11:52am
   
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();"
Patrice T 14-Sep-16 4:43am
   
This is not an open discussion.
In this forum, you ask a new question, and you get answers
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
   
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...
   
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/[^]
   
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.
it was not a permission issue rather it was location issue and i solved it by getting hint from santosh_k 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