I am not able to read from Excel file and store into database using asp.net in c#?

Question

I am doing this to read :

private bool writetoven(string xlspath)
{
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)


    try
    {
        OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", excelConnection);
        excelConnection.Open();
        OleDbDataReader odr = ocmd.ExecuteReader();
        string vcode = "";
        string pswd = "";
        string vname = "";

        while (odr.Read())
        {
            vcode = valid(odr, 0);
            pswd = valid(odr, 1);
            vname = valid(odr, 2);

            insertdataintosql(vcode,pswd,vname);
        }
        excelConnection.Close();
        return true;
    }
    catch (DataException)
    {
        return false;
    }
    finally
    {
        lblmsg4.Text = "Data Inserted Sucessfully";
    }
}

and my connection string is like this:

excelConnectionString = "provider=Microsoft.jet.oledb.4.0;data source=" + 
                        filepath1 + 
                        ";extended properties='Excel 8.0;HDR=YES;'";

but I am getting an error as

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

Line 1574: OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
Line 1575:
Line 1576: excelConnection.Open();
Line 1577:
Line 1578:

It seems like the file is still open but its not and I have checked the running process and its not there

Now what should I do? ...My Excel sheet is closed but I am getting this error

i dont have microsoft access on my com is that can be an issue

is this problem is something to do with my fileupload control that i am using??

Is there any special reason why you are calling excelConnection.Open(); twice? Furthermore I would check TaskManager if there is any instance of Excel running in background (not visible) with your file open! — Pilgerstorfer Franz
– Pilgerstorfer Franz, Commented Nov 10, 2012 at 11:45
yes that was my fault sorry ... but i removed that and still not able to work — Drone
– Drone, Commented Nov 12, 2012 at 6:16
is this problem is something to do with my fileupload control that i am using?? — Drone
– Drone, Commented Nov 12, 2012 at 6:24

marc_s · Accepted Answer · 2012-11-10 11:30:02Z

2

Well, the error says :

cannot open the file ''.

So it almost seems as if your Excel connection string isn't valid - it doesn't have a valid Excel file name in there!

When and where are you setting the Excel connection string??

Is the filepath1 you use valid in that moment?

Does it contain the valid Excel file name ??

Update: why I don't understand is: you're passing in xlspath as a parameter to your method - yet, you don't seem to use that xlspath anywhere - most certainly not to concatenate together your excelConnectionString ....

private bool writetoven(string xlspath)
{
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)

Can you put a breakpoint on this line - what does your excelConnectionString look like - just before your create the connection? Could it be that xlspath contains vendor.xls - but your Excel connection string just never even gets set to that value??

edited Nov 10, 2012 at 11:30

answered Nov 10, 2012 at 10:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Drone Over a year ago

i am using that on a button click ... should i gave the connection string in my qus also i am uploading .xls file

Drone Over a year ago

the filepath1 i am getting is : Vendor.xls

Drone Over a year ago

i dont have microsoft access in my computer is thats the problem??

Drone Over a year ago

i am getting this "provider=Microsoft.jet.oledb.4.0;data source=Vendor.xls;extended properties='Excel 8.0;HDR=YES;'" on excelConnectionString..

marc_s Over a year ago

@Drone: maybe you need to pass the fully qualified path and file name - not just the file name alone....

|

AaronLS · Accepted Answer · 2012-11-12 06:55:55Z

1

Make sure the account your web app is running under has permissions on the file.

Also, since you are not using a path, and just a filename, it might not be finding the file where it is at. Where is the file located relative to your web application?

Does System.IO.File.ReadAllBytes(filepath1) succeeed or fail?

Since you are dealing with a file upload:

The user is uploading from their computer through the browser, and the file is transferred to your server in the post response. See: http://blog.divergencehosting.com/2009/03/12/upload-read-parse-file-aspnet/

Even if you had the full path, you wouldn't be able to access the file on the user's computer from your server. The browser sends you a copy of the file in uploadControl.PostedFile.InputStream; where uploadControl is the name of your asp:FileUpload control.

You would then need to save this stream to a file location on the server, and provide this full path to the connection string.

If you used something like EPPlus to read the file instead of the OleDbConnection, you could use the in-memory stream instead of saving the file first.

edited Nov 12, 2012 at 6:55

answered Nov 12, 2012 at 6:35

AaronLS

38.6k24 gold badges151 silver badges217 bronze badges

12 Comments

Drone Over a year ago

System.IO.File.ReadAllBytes(filepath1) failed

Drone Over a year ago

yes i just figured it out that i need the whole path name.. can u tell me how to get the full path in fileuploder control? i am only getting filename

AaronLS Over a year ago

Try setting filename1 to @"D:\Vendor.xls" then if that is where it is located. I doubt that's where your web app is so, the web app would have no idea that it just so happens that Vendor.xls isn't in the web app directory, but actually in the root of D:

AaronLS Over a year ago

You wouldn't use a filepath in a file uplaoder control. You know I had a sneaking suspicion this was what you were trying to do, but wanted to get past the location issue first.

AaronLS Over a year ago

With a file upload, you will not have a path to the file. The user is uploading from their computer through the browser, and the file is transferred to your server in the post response. See: blog.divergencehosting.com/2009/03/12/…

|

Moha Dehghan · Accepted Answer · 2012-11-10 11:04:48Z

1

Your excel file is open in the Microsoft Excel. Close the Excel window and everything will be fine.

answered Nov 10, 2012 at 11:04

Moha Dehghan

18.5k3 gold badges60 silver badges74 bronze badges

2 Comments

Drone Over a year ago

my excel window is closed ... i don't have any opened excel file

Moha Dehghan Over a year ago

@Drone Well, this was the case for me a hundred times.

Collectives™ on Stack Overflow

I am not able to read from Excel file and store into database using asp.net in c#?

3 Answers 3

10 Comments

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related