SQL query to Excel file

W

wreckingcru

I'm trying to output a SQL query that is constructed thru my VB.net GUI
into an excel file.

Here is the code I'm using:

'Sqlstmt is the SQL query statement
'Conn is the SQL Connection object

cmd = New SqlCommand(Sqlstmt, Conn)
datareader = cmd.ExecuteReader()
'datareader is the SQLdatareader object

Dim objexcel As Excel.Application
Dim objwkb As Workbook
Dim objwksht As Worksheet

objexcel = New Excel.Application
objwkb = objexcel.Workbooks.Add
objwksht = objwkb.ActiveSheet()

Dim cols = datareader.FieldCount()


objwksht.Range(objwksht.Cells(1, 1), objwksht.Cells(5,
cols)).CopyFromRecordset(datareader)

The line above is causing an exception, i.e., I never receive any data
in the excel sheet. I put this in a try statement to catch the
exception and it is this:

System.InvalidCastException: No such interface supported
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.CopyFromRecordset(Object Data, Object MaxRows, Object
MaxColumns)
at WindowsApplication1.Form1.btGenReport_Click(Object sender,
EventArgs e) in C:.....\Form1.vb:line 416


I'm a complete newbie at .NET (Unix C/C++ programming background).

Can anyone advise about what I'm doing wrong? I've sort of "learnt" the
code from others, so the Range command is kinda sketchy to me.....

What other alternative ways would there be to take the "datareader"
data and put it in an excel file (I'm trying to create a report based
on the data from a SQL table)?

Thanks!
 
A

aaron.kempf

do you know what you're doing wrong?

excel is a SPREADSHEET and a DISEASE

keep your data in a database
Access has MUCH better datagrids than any of this .NET crap.

Use Access. Uninstall Excel and .NET.

Preferable Access Data Projects if you're using SQL Server.
if you're not using SQL Server then start

-Aaron
 
W

wreckingcru

That's really an opinion and not a solution.
I think Excel is awesome.


Anyway, this is not my discretion. I HAVE to work with a SQL database
(remote) - that's what been handed down to me. I have the connection
string and UID/Pass to connect to it and based on the GUI input, I'm
constructing the query.

I'd like to debug my problem - not hear about what's wrong with
Microsoft.


do you know what you're doing wrong?

excel is a SPREADSHEET and a DISEASE

keep your data in a database
Access has MUCH better datagrids than any of this .NET crap.

Use Access. Uninstall Excel and .NET.

Preferable Access Data Projects if you're using SQL Server.
if you're not using SQL Server then start

-Aaron



I'm trying to output a SQL query that is constructed thru my VB.net GUI
into an excel file.

Here is the code I'm using:

'Sqlstmt is the SQL query statement
'Conn is the SQL Connection object

cmd = New SqlCommand(Sqlstmt, Conn)
datareader = cmd.ExecuteReader()
'datareader is the SQLdatareader object

Dim objexcel As Excel.Application
Dim objwkb As Workbook
Dim objwksht As Worksheet

objexcel = New Excel.Application
objwkb = objexcel.Workbooks.Add
objwksht = objwkb.ActiveSheet()

Dim cols = datareader.FieldCount()


objwksht.Range(objwksht.Cells(1, 1), objwksht.Cells(5,
cols)).CopyFromRecordset(datareader)

The line above is causing an exception, i.e., I never receive any data
in the excel sheet. I put this in a try statement to catch the
exception and it is this:

System.InvalidCastException: No such interface supported
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.CopyFromRecordset(Object Data, Object MaxRows, Object
MaxColumns)
at WindowsApplication1.Form1.btGenReport_Click(Object sender,
EventArgs e) in C:.....\Form1.vb:line 416


I'm a complete newbie at .NET (Unix C/C++ programming background).

Can anyone advise about what I'm doing wrong? I've sort of "learnt" the
code from others, so the Range command is kinda sketchy to me.....

What other alternative ways would there be to take the "datareader"
data and put it in an excel file (I'm trying to create a report based
on the data from a SQL table)?

Thanks!
Click to expand...
Click to expand...
 
P

Paul Clement

¤ I'm trying to output a SQL query that is constructed thru my VB.net GUI
¤ into an excel file.
¤
¤ Here is the code I'm using:
¤
¤ 'Sqlstmt is the SQL query statement
¤ 'Conn is the SQL Connection object
¤
¤ cmd = New SqlCommand(Sqlstmt, Conn)
¤ datareader = cmd.ExecuteReader()
¤ 'datareader is the SQLdatareader object
¤
¤ Dim objexcel As Excel.Application
¤ Dim objwkb As Workbook
¤ Dim objwksht As Worksheet
¤
¤ objexcel = New Excel.Application
¤ objwkb = objexcel.Workbooks.Add
¤ objwksht = objwkb.ActiveSheet()
¤
¤ Dim cols = datareader.FieldCount()
¤
¤
¤ objwksht.Range(objwksht.Cells(1, 1), objwksht.Cells(5,
¤ cols)).CopyFromRecordset(datareader)
¤
¤ The line above is causing an exception, i.e., I never receive any data
¤ in the excel sheet. I put this in a try statement to catch the
¤ exception and it is this:
¤
¤ System.InvalidCastException: No such interface supported
¤ at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
¤ BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
¤ msgData)
¤ at Excel.Range.CopyFromRecordset(Object Data, Object MaxRows, Object
¤ MaxColumns)
¤ at WindowsApplication1.Form1.btGenReport_Click(Object sender,
¤ EventArgs e) in C:.....\Form1.vb:line 416
¤

CopyFromRecordset only supports ADO and DAO Recordset objects. It does not support ADO.NET.

You could try doing this using straight SQL:

Dim ExcelConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\Test
Files\ExcelWB.xls" & ";" & _
"Extended Properties=Excel
8.0;")

ExcelConnection.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders] FROM [Orders]
IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];",
ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
A

aaron.kempf

you might think that Excel is AWESOME but it's not a database reporting
tool.

does it even support parameters on sprocs?

lol

Excel is for babies.. run away while you still can

I'm sick and tired of dealing with SpreadMarts.

Excel shouldn't be used for reporting.. it doesn't have 1/10th of the
funcitonality that it needs to be a serious reporting platform



That's really an opinion and not a solution.
I think Excel is awesome.


Anyway, this is not my discretion. I HAVE to work with a SQL database
(remote) - that's what been handed down to me. I have the connection
string and UID/Pass to connect to it and based on the GUI input, I'm
constructing the query.

I'd like to debug my problem - not hear about what's wrong with
Microsoft.


do you know what you're doing wrong?

excel is a SPREADSHEET and a DISEASE

keep your data in a database
Access has MUCH better datagrids than any of this .NET crap.

Use Access. Uninstall Excel and .NET.

Preferable Access Data Projects if you're using SQL Server.
if you're not using SQL Server then start

-Aaron



I'm trying to output a SQL query that is constructed thru my VB.net GUI
into an excel file.

Here is the code I'm using:

'Sqlstmt is the SQL query statement
'Conn is the SQL Connection object

cmd = New SqlCommand(Sqlstmt, Conn)
datareader = cmd.ExecuteReader()
'datareader is the SQLdatareader object

Dim objexcel As Excel.Application
Dim objwkb As Workbook
Dim objwksht As Worksheet

objexcel = New Excel.Application
objwkb = objexcel.Workbooks.Add
objwksht = objwkb.ActiveSheet()

Dim cols = datareader.FieldCount()


objwksht.Range(objwksht.Cells(1, 1), objwksht.Cells(5,
cols)).CopyFromRecordset(datareader)

The line above is causing an exception, i.e., I never receive any data
in the excel sheet. I put this in a try statement to catch the
exception and it is this:

System.InvalidCastException: No such interface supported
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.CopyFromRecordset(Object Data, Object MaxRows, Object
MaxColumns)
at WindowsApplication1.Form1.btGenReport_Click(Object sender,
EventArgs e) in C:.....\Form1.vb:line 416


I'm a complete newbie at .NET (Unix C/C++ programming background).

Can anyone advise about what I'm doing wrong? I've sort of "learnt" the
code from others, so the Range command is kinda sketchy to me.....

What other alternative ways would there be to take the "datareader"
data and put it in an excel file (I'm trying to create a report based
on the data from a SQL table)?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
A

aaron.kempf

dude you kids are just flat out wrong.

Excel _DOESNT SUPPORT DATABASES_
Excel ISNT A REPORTING PLATFORM

keep your data in a database and spit on anyone that uses excel

See

http://www.kjmsolutions.com/datasetarray.htm
I'm trying to output a SQL query that is constructed thru my VB.net GUI
into an excel file.

Here is the code I'm using:

'Sqlstmt is the SQL query statement
'Conn is the SQL Connection object

cmd = New SqlCommand(Sqlstmt, Conn)
datareader = cmd.ExecuteReader()
'datareader is the SQLdatareader object

Dim objexcel As Excel.Application
Dim objwkb As Workbook
Dim objwksht As Worksheet

objexcel = New Excel.Application
objwkb = objexcel.Workbooks.Add
objwksht = objwkb.ActiveSheet()

Dim cols = datareader.FieldCount()


objwksht.Range(objwksht.Cells(1, 1), objwksht.Cells(5,
cols)).CopyFromRecordset(datareader)

The line above is causing an exception, i.e., I never receive any data
in the excel sheet. I put this in a try statement to catch the
exception and it is this:

System.InvalidCastException: No such interface supported
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Excel.Range.CopyFromRecordset(Object Data, Object MaxRows, Object
MaxColumns)
at WindowsApplication1.Form1.btGenReport_Click(Object sender,
EventArgs e) in C:.....\Form1.vb:line 416


I'm a complete newbie at .NET (Unix C/C++ programming background).

Can anyone advise about what I'm doing wrong? I've sort of "learnt" the
code from others, so the Range command is kinda sketchy to me.....

What other alternative ways would there be to take the "datareader"
data and put it in an excel file (I'm trying to create a report based
on the data from a SQL table)?

Thanks!
Click to expand...
Click to expand...
 
P

Paul Clement

¤ dude you kids are just flat out wrong.
¤
¤ Excel _DOESNT SUPPORT DATABASES_

Yes it does.

¤ Excel ISNT A REPORTING PLATFORM

Sure it is.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
A

aaron.kempf

it doesn't even support parameters for stored procedures

i mean-- come on

excel is a disease!

spit on excel users!

-Aaron
ADP Nationalist
 
P

Pritcham

aaron

I usually avoid answering to diatribes like yours but couldn't resist
this time.

Firstly, you're saying that Excel isn't a reporting tool - do you know
what the OP actually wants to do with the data once it's in Excel?
Nope. So how is your comment even relevant (never mind useful). If all
you're going to do is to rant about not using Excel (when you don't
even know why it's being used) then why not just ignore the thread and
move on to something in which you CAN (or will) help.

Having seen your reply to this question I took a quick look at some of
your other replies to other posts, and to be honest, with a few
exceptions where you do appear to offer some assistance, they're all
the same (i.e. about how bad Excel is and how great databases are).

I think you need to take a step back to be honest - it's obvious from
most of your posts that you dislike Excel (to say the least) but have
you ever heard of the phrase "use the right tools for the job"? You
can, for example, use a hammer to get a screw into a bit of wood, but
the correct tool to use would be a screwdriver - just because you can
use something to do a job it doesn't automatically make it the right
tool.

There are many instances where databases are the right choice, and
there are other instances where xl/spreadsheets are the right choice
(and before you launch into *yet another* diatribe, yes, I am
conversant and use both depending on the need).

I currently have a need to do model a complex sales/stock forecast
which I *could* do in Access or SQL server at a push (not because of my
lack of experience but because, due to the complexity of the
calculations and the need to use a number of variables on a line by
line basis, it simply makes sense to use Excel).

No doubt you'll come out with something telling me how useless Excel/my
approach is (feel free - you're opinion really doesn't matter to me)
but before you do you should bear in mind that I use Access (where
appropriate), Excel (Where appropriate), SQL Server (where
appropriate), 'classic' VB (where appropriate), VB/C#.Net (Where
appropriate), PHP (where appropriate) etc etc etc - i.e. I use
whichever tool is appropriate for the specific task at hand and
therefore don't just go around slamming any tool I don't use in
preference for one I do.

Anyway, I really think you should consider whether you've got anything
helpful to say in reply to a question someone's posted before putting
your usual (Excel is so bad...) posts up - after all, if a question has
been posed it's because someone needs help, not because they're looking
for approval from you as to their choice of tool).

Martin
 
C

Cor Ligthert [MVP]

Pritcham,

Primary, why did you connect this message to a message from Paul?

Secondly why are you telling that Aaron message is diatribes, it adds
nothing.
His message tells enough?

Something about spreadsheets
http://en.wikipedia.org/wiki/Spreadsheet

Just my thoughts reading your reply

Cor

"Pritcham" <[email protected]> schreef in bericht
aaron

I usually avoid answering to diatribes like yours but couldn't resist
this time.

Firstly, you're saying that Excel isn't a reporting tool - do you know
what the OP actually wants to do with the data once it's in Excel?
Nope. So how is your comment even relevant (never mind useful). If all
you're going to do is to rant about not using Excel (when you don't
even know why it's being used) then why not just ignore the thread and
move on to something in which you CAN (or will) help.

Having seen your reply to this question I took a quick look at some of
your other replies to other posts, and to be honest, with a few
exceptions where you do appear to offer some assistance, they're all
the same (i.e. about how bad Excel is and how great databases are).

I think you need to take a step back to be honest - it's obvious from
most of your posts that you dislike Excel (to say the least) but have
you ever heard of the phrase "use the right tools for the job"? You
can, for example, use a hammer to get a screw into a bit of wood, but
the correct tool to use would be a screwdriver - just because you can
use something to do a job it doesn't automatically make it the right
tool.

There are many instances where databases are the right choice, and
there are other instances where xl/spreadsheets are the right choice
(and before you launch into *yet another* diatribe, yes, I am
conversant and use both depending on the need).

I currently have a need to do model a complex sales/stock forecast
which I *could* do in Access or SQL server at a push (not because of my
lack of experience but because, due to the complexity of the
calculations and the need to use a number of variables on a line by
line basis, it simply makes sense to use Excel).

No doubt you'll come out with something telling me how useless Excel/my
approach is (feel free - you're opinion really doesn't matter to me)
but before you do you should bear in mind that I use Access (where
appropriate), Excel (Where appropriate), SQL Server (where
appropriate), 'classic' VB (where appropriate), VB/C#.Net (Where
appropriate), PHP (where appropriate) etc etc etc - i.e. I use
whichever tool is appropriate for the specific task at hand and
therefore don't just go around slamming any tool I don't use in
preference for one I do.

Anyway, I really think you should consider whether you've got anything
helpful to say in reply to a question someone's posted before putting
your usual (Excel is so bad...) posts up - after all, if a question has
been posed it's because someone needs help, not because they're looking
for approval from you as to their choice of tool).

Martin
 
P

Pritcham

Cor

Firstly, my apologies, this reply was meant for Aaron, not Paul.

Secondly, I was trying to suggest that he (Aaron) post something useful
as opposed to slamming the use of Excel (having said that, I didn't
post anything of use to the OP either so again, I apologise - just got
wound up reading Aaron's reply I suppose).
 
R

Rob Panosh

Aaron,

What are you trying to prove! If you don't have anything good to say
it would be better to say nothing at all. This is a support forum not
a "Soap Box".

Rob
 
A

aaron.kempf

it NEVER makes sense to use Excel for reporting.

keep your calculations in one place instead of a hundred different
copies.

it's simpler; easier; higher-performance and it's easier to manage.
I mean-- do you really want to loop through 100 copies of the same XLS
to change a single formula?

if you use sprocs instead of excel you can use custom functions; you
can use custom centralized business logic.

Excel is for babies.

SPIT on people that use it for anything.


-Aaron
 
A

aaron.kempf

use the right tools for the job?

EX-FRIGGIN-ACTLY

excel is NEVER the right tool for ANY job.

it is a disease.

people that use Excel -- for anything-- should be fired on the spot for
ineptitude.

-Aaron
 
A

aaron.kempf

yes I know what he wants to do.

he wants to have an unmanageable MESS to create job security.

you can't hold your company hostage my developing 'solutions' (gag)
with Excel that are too complex for the task at hand.

keep one copy of the same calculations; and it's a lot easier to
manage.
decentralizing spreadsheets are a MAJOR problem facing IT departments
everywhere.

it's just a question of whether you kids have THE BALLS to do something
about it.

Use Crystal Reports or Reporting Services.
or Eat Shit.

-Aaron
 
A

aaron.kempf

this isn't a support forum.

this isn't a soap box.

i'm here to tell you that the idea of creatign a dozen spreadsheets to
create a simple report?
it's just not necessary.

if you MUST pull data out of a database into a spreadsheet; then you
should be using Analysis Services and PivotTables.

but my webbased pivotTables are more powerful; i mean-- if multiple
people VIEW the same report at the same time; there isn't an issue.

having a different copy of the report in 100 different places; where
every single end user can change a single function?

it's just not an enterprise level solution.

Excel is a speedbump; drive around it.

-Aaron
 
C

Cor Ligthert [MVP]

LOL and that for all your messages in this thread


<[email protected]> schreef in bericht
this isn't a support forum.

this isn't a soap box.

i'm here to tell you that the idea of creatign a dozen spreadsheets to
create a simple report?
it's just not necessary.

if you MUST pull data out of a database into a spreadsheet; then you
should be using Analysis Services and PivotTables.

but my webbased pivotTables are more powerful; i mean-- if multiple
people VIEW the same report at the same time; there isn't an issue.

having a different copy of the report in 100 different places; where
every single end user can change a single function?

it's just not an enterprise level solution.

Excel is a speedbump; drive around it.

-Aaron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error using Excel Automation on Exported Spreadsheet 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
SQL not importing records from ACCDB 2
setting object to excel application 6
Use VB.Net to write to Save Excel File 0
Format Export to Excel error 8
Posting a dataset to a SQL table using VBA 0
Export SQL 1

Top