Re: Oracle to Excel Export - Lots of records need speed

I'm not sure exactly what you mean by 'using the web datagrid writing out',
so my apologies if this turns out to be something you've already tried, but
have you tried something like this ...

Module Module1

Sub Main()

Dim strConnection As String
Dim cnn As System.Data.OleDb.OleDbConnection
Dim cmm As System.Data.OleDb.OleDbCommand
Dim records As Long

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Brendan Reynolds\My
Documents\Counties.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes';Persist Security
Info=False"
cnn = New System.Data.OleDb.OleDbConnection
cnn.ConnectionString = strConnection
cnn.Open()
cmm = New System.Data.OleDb.OleDbCommand
With cmm
.CommandText = "INSERT INTO [My***$] (County) VALUES ('A New
County')"
.CommandType = CommandType.Text
.Connection = cnn
records = .ExecuteNonQuery
End With
cnn.Close()
Console.WriteLine("Records affected: " & records.ToString)
Console.ReadLine()

End Sub

End Module

If this looks like it may be of interest, you may find the following KB
article useful. It uses 'classic' ADO and 'classic' VB, but it is not
difficult to adapt to ADO.NET and VB.NET ...
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

--
Brendan Reynolds (MVP)


"Hyperactive" <Hyperactive@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D8BC5531-778B-489F-883F-808F1BB290F4@xxxxxxxxxxxxxxxx
>I am trying to export some large queries in a VB.NET app (NOT an asp.net
>web
> app). The queries are coming from an Oracle database and can contain
> anywhere
> from 30,000 - 60,000 records that need to go into an Excel spread***.
>
> I have tried the automation with writing out rows and columns, and I have
> also tried using the web datagrid writing out. All these options are
> severely
> slow. Does anyone have any code snippets or can anyone direct me to how i
> can
> make this export fast? The export needs to be compatible with EXCEL 97 and
> ABOVE, which is making this more difficult since I cannot just export
> directly to xml format or something simple. I even tried exporting to XML
> then using automation to open the file and do a save as, but Excel trying
> to
> just open the XML file was taking forever.
>
> Any help would greatly be appreciated.


.