RE: Write Excel File with Two DataTables

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hello Wonko,

Thanks for using Microsoft Newsgroup Support Service, this is Colbert Zhou
[MSFT] and I will be working on this issue with you.

With a quick test of your scenario in my side, I cannot reproduce the
error. The following is my codes, and I will give some explanations later,

OleDbConnection con = new
OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=D:\\test.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES\"");
con.Open();
OleDbCommand com = new OleDbCommand();
com.Connection = con;
com.CommandText = "Select * from [Sheet1$]";

OleDbDataReader reader = com.ExecuteReader();
DataTable columnNames = reader.GetSchemaTable();
Debug.Print(columnNames.Rows[0].ItemArray[0].ToString());
Debug.Print(columnNames.Rows[1].ItemArray[0].ToString());
Debug.Print(columnNames.Rows[2].ItemArray[0].ToString());
reader.Close();

com.CommandText = "INSERT INTO [Sheet1$] values ('a','b','c')";
Int32 i = com.ExecuteNonQuery();

com.Dispose();
con.Close();

Firstly, I build my connection string, the connection object, and then the
command object. And then I get the SchemaTable by calling GetSchemaTable()
method. After that, I can print all of three column names. Then I change my
command text to insert rows into Excel database, and call ExecuteNonQuery()
method of command object. It returns 1 which indicates there is one row
inserted.

I have uploaded my test.xlsx file in my SkyDrive, so that you can download
it here to have a test with my sample in your side.

http://cid-c2e0d62e8a095a30.skydrive.live.com/self.aspx/Public/test.xlsx

Hope this sample can help to narrow down the problem in your side. If this
still does not help, would you mind providing the following information?

1. What are the Excel and your OleDB engine version?
2. What is your connection string?
3. If there is any difference between my Excel structure and yours?

I collect this information for quicker reproducing and troubleshooting this
issue. So I can provide a more effective support on this. Thanks for your
understanding and have a nice day!



Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxxxxxxxxxx

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

.

Quantcast