Hi, thanks for your reply.
My code is pretty simple with no bulks inserts - I insert one row at a time.
After ~17,000 rows the whole worksheet Tbl1 just disapears.
This is how the code looks:

using (OleDbConnection mapConnection = new
OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data
Source=c:\\book1.xslx;Extended Properties=Excel 12.0;"))
{
OleDbCommand mapCommand = new OleDbCommand();
mapCommand.CommandText = @"
create table [Tbl1] (
[col1] numeric,
[col2] char(20),
[col3] char(20),
[col4] char(20),
[col5] char(20),
[col6] char(20),
[col7] char(20))";
mapCommand.ExecuteNonQuery();

mapCommand.CommandText = "insert into [Tbl1] ([col1], [col2], [col3],
[col4], [col5], [col6], [col7]) values (?, ?, ?, ?, ?, ?, ?)";

OleDbParameterCollection parameters = mapCommand.Parameters;
parameters.Add("[col1]", OleDbType.Numeric);
parameters.Add("[col2]", OleDbType.VarChar);
parameters.Add("[col3]", OleDbType.VarChar);
parameters.Add("[col4]", OleDbType.VarChar);
parameters.Add("[col5]", OleDbType.VarChar);
parameters.Add("[col6]", OleDbType.VarChar);
parameters.Add("[col7]", OleDbType.VarChar);

// values is a list of X rows
foreach (object value in values)
{
mapCommand.Parameters[0].Value = num1;
mapCommand.Parameters[1].Value = str1;
mapCommand.Parameters[2].Value = str2;
mapCommand.Parameters[3].Value = str3;
mapCommand.Parameters[4].Value = str4;
mapCommand.Parameters[5].Value = str5;
mapCommand.Parameters[6].Value = str6;
}
mapCommand.ExecuteNonQuery();
}

With excel 2003 and the jet provider, more rows were inserted to the table
above.

Any ideas?


"joel" wrote:


I read a fgew webpages and put my thoughts below. To give a better
answer I would need to see what methods you are using to connect and to
get the data. Some websites say to go back to the Jet engine instead
of using ADO. Others say that ADO has problems reading larger amount of
data if you attempt to read too much data at one time. So if you are
using a query with ADO you probably have to read data in smaller blocks.
I believve there is a block size property that you can use with ADO. I
would have to do some research. So the solution would be to create a
loop where you would get a record set (which is limited), move data to
the worksheet, then get more data until you reach the end of the data.


Read this URL

'Excel, the Office 12 ACE Provider, and performance - Dougbert on SSIS
- Dougbert.com' (http://tinyurl.com/yekzy2h)


I suspect like you said it a memory and and indexing issue.

I did some reading on the web and found this webpage

'Methods for transferring data to Excel from Visual Basic'
(http://support.microsoft.com/default.aspx/kb/247412)

You may need to replace this line

from
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind &
";"

to
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind &
";"

The 12.0 is the version with Office 2007. I'm not sure but I suspect
to use office 2003 you would just change the 12.0 to 11.0. Maybe use
the Jet Engine instead of ACE?



It seems the jet engine is limited to around 64K. so the answer is
probably switch fro Jet engine to ACE. See this URL

'Using oledb microsoft.ace provider to transfer data from .net to excel
2007 VB.NET' (http://tinyurl.com/y8ddmwg)

I'm not sure how you are reading the data but you SQL statement may be
exceeeding the 64K limit. If you are doing a query and trying to read
all the data at once this could be the problem. Instead of doing a
query read one line at a time.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176159

Microsoft Office Help

.