Data not populated into datatable

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

0	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

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

0	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

0	Yes it worked Regards, Sudha Sudhak, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts