I want to fill a defined named range in an Excel file with a matrix of values from a stored procedure in Oracle. Is this possible?
Today from our ASP.NET client we ask an oracle database for resultset and then rearrange and work with the resultset and print the final resultset to defined named ranges in an Excel file with the OLEDB provider. We repeatingly execute a SQL statement like "INSERT INTO [DATA_RANGE1] VALUES ('var1','var2','var3');" and for each sql statement a new row is inserted in excel file in the defined named range "DATA_RANGE1". This works fine, but is VERY time consuming!
It would be nice writing all insert statements in one call to the excel file instead of repeatingly call the excel file.
If I try to use sql statements separated with ";", like "INSERT INTO [DATA_RANGE1] VALUES ('var1','var2','var3'); INSERT INTO [DATA_RANGE1] VALUES ('var1','var2','var3');" in one query from the ASP.NET client I get System.Data.Oledb.OledbException "Characters found after end of SQL statement".
Today the connectionstring look like
_connectionExcel = NewSystem.Data.OleDb.OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source="+ "'" & strFullPathAndName & "';Extended Properties=Excel 12.0 Macro;")
for Excel 2010 files and
_connectionExcel = NewSystem.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ "'" & strFullPathAndName & "';Extended Properties=Excel 8.0 Macro;")
for Excel 2003 files.
Is there another method to solve this?
I asked about this in an Excel forum and they advised me to use a stored procedure, but I am not sure about how to do this, and if it is possible?