OleDb function only works once

[sambos]

Hi there,

I have the following function which loads fine during the form load event:

void loadExcelDataSet(DataSet^ dataSet1)
{
String^ sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\test.xlsx;Extended Properties=\"Excel 12.0 XML;HDR=YES\""; 

for(int i = 1; i<21; i++)
{
DataTable^ dt= gcnew DataTable();
OleDbConnection^ conn= gcnew OleDbConnection(sConnectionString);

OleDbCommand^ com= gcnew OleDbCommand();
com->Connection = conn;
com->CommandText = "select * from ["Table" + i.ToString() + "$]";
OleDbDataAdapter^ adapt = gcnew OleDbDataAdapter();
adapt->SelectCommand = com;
adapt->Fill(dt);

dataSet1->Tables->Add(dt);
dataSet1->Tables[i-1]->TableName = "Table" + i.ToString() + ";

conn->Close();
}
}

The above code works fine. All I required upon the program loading is the Excel data to be placed into a dataset.

However, part of my program requires this dataset to be updated given that the Excel file is updated. When I call the above function again (after it has been called during the form load event), the Excel data isn't added.

I've done some additional tests. For example, I've cleared the dataset just before the above function is called a second time only to fine that the dataset remains empty.

I've also stoped the above function being called during the form load event, but called it later on in the program to find that it works and the updated Excel data is placed into the dataset.

This leads me to believe that the above code has some sort of an issue.

Does anyone have some ideas?

Cheers,