> On 16 Aug 2006 12:12:15 -0700, "wreckingcru" <pranavka***@gmail.com> wrote:
>
> ¤ 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)

> 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!

> See
>
> http://www.kjmsolutions.com/datasetarray.htm
>
> wreckingcru wrote:
> > 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!