Data not populated into datatable

Posted by Sudhak under Regular Expressions on 9/20/2009 | Views : 1865 | Status : [Member] | Replies : 8
Hi

I am trying to get data from excel sheet using oledbconnection , but I am unable to get the data into datatable.
The following is the code.

string strXMLToSave = string.Empty;
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\ExcelDBPractice\ExcelDBPractice\bin\Debug\ExcelDBPractice.xlsx;
Extended Properties='Excel 12.0'";
OleDbConnection MyOLEConnection = new OleDbConnection(strConn);
OleDbCommand MySQLCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", MyOLEConnection);
OleDbDataAdapter da = new OleDbDataAdapter(MySQLCommand);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];

the data is not populated into datatable

Regards,
Sudha




Responses

Posted by: Vuyiswamb on: 9/20/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Day Sudhak

but how can you execute the command if you do not open the Connection ? OPen the Connection before you will the dataset and Close it when you are done. another Piece of advice is that when dealing with Databases , always trap your code for the unkown and when you are done , in your Finally block always close the Connection.

Thank you for posting at Dotnetfunda

Vuyiswa Maseko



Thank you for posting at Dotnetfunda
[Administrator]

Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 9/21/2009 [Member] [Moderator] Silver

Up
0
Down
Hi Sudhak--

Check my post for more details:
http://lakhangarg.blogspot.com/search/label/Excel%20Sheet%20Import%2FExport

Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeks on: 9/22/2009 [Member] Bronze

Up
0
Down
Can you pleaseprovide the details related to your error??? So I can help you to solve the problem

Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudhak on: 9/23/2009 [Member] Starter

Up
0
Down
Hi everyone

Thanks for all your effort

I have modified the connection string as below

string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\SudhaPractice\ExcelPracticeCalculationsCS\ExcelPracticeCalculationsCS\bin\Debug\ExcelPracticeCalculationsCS.xlsx;Extended Properties='Excel 12.0'";


Regards,
Sudha


Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 9/24/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Did it work after that ?

Thank you for posting at Dotnetfunda
[Administrator]

Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeks on: 9/24/2009 [Member] Bronze

Up
0
Down
You can use the connection string
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

----- Where ------
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

And please make sure that your dataset is having any table.


Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeks on: 9/24/2009 [Member] Bronze

Up
0
Down
And please let us know if it solve the problem

Thanks

Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sudhak on: 9/25/2009 [Member] Starter

Up
0
Down
Yes it worked

Regards,
Sudha


Sudhak, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response