Click here to Skip to main content
14,639,661 members
Rate this:
Please Sign up or sign in to vote.
See more:
1-I am having a strange problem.

Requirement: read a list of excel files on a folder and store the content on a dataset.

What I have written:
**Button click:**

protected void btnUploadExcelFiles_Click(object sender, EventArgs e)
{
            string[] strFiles = Directory.GetFiles(strPath);
            foreach (string strFile in strFiles)
            {
                ClsExcelUpload objExcelUpload = new ClsExcelUpload();
                string szConnectionString1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + strFile + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
                objExcelUpload.ReadExcel(szConnectionString1, strFile);

            }
}



**function ReadExcel:**

public string ReadExcel(string szConnectionString, string strFile)
        {
            OleDbConnection objConn = new OleDbConnection();
            objConn.ConnectionString = szConnectionString;
            objConn.Open();

            OleDbDataAdapter DAobjBasicInfo = new OleDbDataAdapter();
            DAobjBasicInfo = new OleDbDataAdapter("select * from [Sheet1$]", objConn);
            DataTable dt1 = new DataTable();
            DAobjBasicInfo.Fill(dt1);
            objConn.Close();
            objConn.Dispose();
            return "";
        }

**Problem:**
The szConnectionString gets updated with the new filename set through the loop but when the OleDbDataAdapter opens the objConn, it looks for the Excel file which is already deleted. and the datatable everytime gets the first file data everytime.

This is the strangest thing I have ever seen as I am not able to identify the area of problem why the oledbconnection is not getting updated.

What I have tried:

The entire functions are written above.
Posted
Updated 10-Jul-20 11:33am
v2
Comments
j snooze 10-Jul-20 17:41pm
   
Dumb question on my part as I'm not sure I understand your question. you say the file is deleted, but it gets the first file data everytime? so which is it? The file is deleted or it gets the first file data?

Also one thing I wondered is how your connection string knows where the file is, unless you are running your exe in the folder location of all the excel files. Pretty sure the connection string needs a full path and it appears you are only providing a file name. You could do

string szConnectionString1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Path.Combine(strPath,strFile) + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
PIEBALDconsult 10-Jul-20 17:44pm
   
I see a few bad things, but nothing which seems like a problem. Are you sure the files don't contain the same data?
arunavachowdhury 10-Jul-20 18:01pm
   
Ok. I would try to explain in more detail.
I have one FileUpload control which allows multiple files to select and upload.
a command button which copies the posted file through FileUpload to a specified location.
Upto here everything works as expected.
Now I have the code written on the same command button to read the excel file which is copied.
The first file reads correctly.
while reading the second file it finds the same data.

Now to test it further. I have moved the file read code to a different command button. Now I did 2 tests.
Test 1. Upload 2 files using file upload
Click on command button 1 to copy the posted files to spefified location
click on Command button 2 to loop through the files on copied folder and read the
files.
Result: Same issue, reading second file it gets the same data.
Test 2:
Run the application. Do not select and upload any file. Manually copy the same to
files to the specified location.
click the command button 2 to read the files.
File reads correctly.

its very clear there is nothing wrong with the function to read files. When File Upload is working there must be some conflict in memory management. I wonder whats my alternative to the requirement.
PIEBALDconsult 10-Jul-20 18:13pm
   
"there must be some conflict in memory management" -- Doubtful.
Maybe show other parts of the application.
Garth J Lancaster 11-Jul-20 1:58am
   
I notice in here
            foreach (string strFile in strFiles)
            {
                ClsExcelUpload objExcelUpload = new ClsExcelUpload();
                string szConnectionString1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + strFile + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
                objExcelUpload.ReadExcel(szConnectionString1, strFile);

            }

you don't dispose of objExcelUpload - would this for example
            foreach (string strFile in strFiles)
            {
                ClsExcelUpload objExcelUpload = new ClsExcelUpload();
                string szConnectionString1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + strFile + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
                objExcelUpload.ReadExcel(szConnectionString1, strFile);
                // Presumably something else happens here 
                objExcelUpload = null;
                GC.Collect();
            }
make a difference ?

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




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