Getting error: 'Member not found', what does this mean? 2

[radiance]

Hello.

I have a button that the user selects to export the data to excel. I have 2 problems:

1) I am getting a major error on the export of the dataset to excel (once the user clicks 'Export')
Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

2) When I just put the connectionString name in from my webconfig file, I get an error, but when i put in the full connection string in, this error does not result:
"Format of the initialization string does not conform to specification starting at index 0."

Here is the sub (please excuse the length): I believe the error is a result of my dataset (of which I am using parameters).
================================================================================================
Protected Sub exportbyProgramButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles exportbyProgramButton.Click
Try
Using connection As New SqlConnection("Server=corp290 ;Initial Catalog=testArena;User ID=simm; Password=$simeon$")
'Using connection As New SqlConnection("LiveCMSConnect")
connection.Open()
Dim selectPrograms As String = "select * from Exploitation_view where ID = @ID"

Dim adapter As SqlDataAdapter = New SqlDataAdapter

Dim selectCMD As SqlCommand = New SqlCommand(selectPrograms, connection)
adapter.SelectCommand = selectCMD

'add parameters
selectCMD.Parameters.Add("@ID", SqlDbType.Int).Value = programList.SelectedValue

Dim ds As DataSet = New DataSet
adapter.Fill(ds, "Harbour_View")

'begin to specify excel worksheet details

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oCells As Excel.Range
Dim strFileName = "ViewByProgram.xls"

'start excel and get application object
oExcel = CreateObject("Excel.Application")

'get a workbook
oBook = oExcel.Workbooks.Add
oSheet = oBook.ActiveSheet(1)
oBook.ActiveSheet(1).Name = "Program Listing"
oCells = oSheet.Cells

'define styles
With oSheet.Range("A1", "H1")
.Font.Bold = True
.Font.FontStyle.Equals("Arial")
End With

'Add the table headers
oSheet.Range("A1").Value = "Record ID"
oSheet.Range("B1").Value = "Program"
oSheet.Range("C1").Value = "Platform Type"
oSheet.Range("D1").Value = "Rights"
oSheet.Range("E1").Value = "OptionType"
oSheet.Range("F1").Value = "Comments"
oSheet.Range("G1").Value = "Estimated Cost Range"
oSheet.Range("H1").Value = "Date Option expires"


Dim iRows, intColumnValue As Integer
Dim xyz = ds.Tables("Harbour_View")

For iRows = 2 To xyz.rows.Count - 1
For intColumnValue = 0 To xyz.Columns.Count - 1
oCells(iRows + 1, intColumnValue + 1).value = xyz.Rows(iRows).ItemArray(intColumnValue).ToString
Next
Next

oExcel.Visible = False
oExcel.DisplayAlerts = False
oBook.SaveAs(strFileName)
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

outputPanel.Visible = True
outputPanel.Text = "<br>Thank you. Your selected data based on 'Program Name' has been saved as TestSatelite.xls"
ExportbyDates.Visible = False
startDate.Text = ""
endDate.Text = ""

connection.Close()

End Using

Catch ex As Exception
outputPanel.Visible = True
outputPanel.Text = "Error:<br>" & ex.Message.ToString
End Try
End Sub

 

[adamroof]

1. PLEASE DONT POST PASSWORDS! although may not be accessible via outside world, you never know who can find out what.

2. Ive seen longer examples. But you can wrap the code in

 code here

tags

3.

Using connection As New SqlConnection(...

whats that? ive personally never seen that, maybe valid, but should you dim as SqlConnection?

Dim connection As New SqlConnection(...

4. Is this a windows form?

5. 'Using connection As New SqlConnection("LiveCMSConnect")
if this is the one from your config, should it not be

'Using connection As New SqlConnection(ConfigurationSettings.AppSettings("LiveCMSConnect"))

id better wait to see if this is windows form before i attempt to answer the others...some of my thoughts may not apply if it is.

 

[radiance]

it's a web application. sorry.

 

[adamroof]

web forms i dont think you can extend the oExcel = CreateObject("Excel.Application") part due to security limitations of the browser, and get it working the way you are. IF YOU CAN DO THAT...im copying your code!

Id say you should build your data into a datagrid of sorts and export the results via a rendercontrol...

heres a working export, maybe you can get what you need outta this example. Its old, and could use some cleanup.

<asp:ImageButton ID="exportBtn" runat="server" ImageUrl="img/action/excel.gif" ToolTip="Export Results"
                                    OnClick="ExportDG" />
<asp:DataGrid ID="dgReport" runat="server" AutoGenerateColumns="true" CellPadding="2"
    CellSpacing="1" Width="100%" border="0" CssClass="dg" AllowPaging="true" PageSize="50"
    OnPageIndexChanged="dg_Page">
    <PagerStyle CssClass="dgPage" Mode="NumericPages" Position="TopAndBottom" HorizontalAlign="right" />
    <HeaderStyle CssClass="dgHead" />
    <AlternatingItemStyle BackColor="Linen" />
    <ItemStyle BackColor="White" />                                    
</asp:DataGrid>

    Sub ExportDG(ByVal sender As Object, ByVal e As ImageClickEventArgs)
        'Have to disable sorting while the object is being exported to Excel
        dgReport.AllowSorting = False
        dgReport.AllowPaging = False
        dgReport.Width = 100%
        dgReport.GridLines = 1
        dgReport.ShowHeader = False
        dgReport.HeaderStyle.BackColor = System.Drawing.Color.White
        dgReport.ItemStyle.BackColor = System.Drawing.Color.White
        'Rebind the data to be loaded and set back to standard sorting
        showReport(ViewState("repID"))

        Dim strFileName As String = ViewState("repTitle") & ".xls"
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=""" & strFileName & """")
        Response.ContentEncoding = System.Text.Encoding.UTF7
        Response.Charset = ""
        EnableViewState = False
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHTMLTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

        dgReport.RenderControl(oHTMLTextWriter)
        Response.Write(oStringWriter.ToString())
        Response.End()
    End Sub

    Sub showReport(ByVal repID As Integer)
        Dim sqlCon As New SqlConnection(ConnectionString)
        Dim objComm As New SqlCommand("sp_Reports", sqlCon)
        objComm.CommandType = CommandType.StoredProcedure
        objComm.Parameters.Add("@repID", SqlDbType.Int, 4).Value = repID

        Dim da As New SqlDataAdapter(objComm)
        Dim ds As New DataSet()
        Try
            da.Fill(ds)
        Catch ex As Exception
            Message.Text = "Report Error: " & ex.Message            
        Finally
            sqlCon.Close()
        End Try
        If Message.Text = "" Then
            DataGrid1.Dispose()
            DataGrid1.DataBind()
            dgReport.DataSource = ds
            dgReport.DataBind()
            dgReport.Caption = "<div align=left>" & ViewState("repTitle") & "<br>Report Returned <b>" & ds.Tables(0).Rows.Count & "</b> Records</div><div align=right><b>Page " & dgReport.CurrentPageIndex + 1 & " of " & dgReport.PageCount & "</b></div>"
            dgReport.CaptionAlign = TableCaptionAlign.Left
            ddlFilter.Visible = False
            tdSearch.Visible = False
            printBtn.Visible = True
            exportBtn.Visible = True
            lbAddReport.Visible = False
            GoToTop()
        Else
            GoToTop()
        End If

    End Sub

 

[jmeckley]

the [tt]using()[/tt] block is a shortcut for closing/disposing objects. this way you don't explicitly need to call [tt]Close()[/tt] and [tt]Dispose()[/tt] and set object to null.

Which version(s) of Excel are you using? if it's Excel 2003 or greater you can use this tool:

http://www.carlosag.net/Tools/ExcelXmlWriter/Default.aspx

I use this to generate excel workbooks. It's much faster than opening a db connection and you don't need to create Excel objects (which requires Excel).

you could also use a handler (ashx), bind the data to a new gridview object, clear all content/headers, output gridview markup and set content headers for XLS.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[radiance]

thank you.

i am going to try everything mentioned.

I have done the excel export successfully for the web, but just can't get this example.

 

[radiance]

i made quite a few changes (used an example from microsoft to steer me in the right direction), however, I am still getting "Data source name not found and no default driver specified" or sometimes i get the wrong arguments error on the export button click.

although my sub calls the connection string...

===new sub====

 Protected Sub exportbyProgramButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles exportbyProgramButton.Click

        Try

            'dim ado objects
            Dim oCmd As New ADODB.Command
            Dim rst As New ADODB.Recordset
            'Dim mySqlConnection As SqlConnection
            Dim objPara As ADODB.Parameter


            'dim excel objects
            Dim xlApp As Object 'excel application
            Dim xlWb As Object 'workbook
            Dim xlWs As Object 'worksheet
            'Dim strFileName = "ViewByProgram123.xls"

            Dim recArray  ' this should be variant

            Dim fldCount As Integer
            Dim recCount As Long
            Dim iCol As Integer
            Dim iRow As Integer

            'open database with method argument

            Dim oCnt As String = ConfigurationManager.ConnectionStrings("LiveCMSConnect").ConnectionString

            'open connection to the database
            Dim conn As New SqlConnection(oCnt)

            'open the recordset
            rst.Open("SELECT * FROM ExploitationView2 where ProgramName LIKE  'ProgramName'", oCnt)

            'add command parameters
            objPara = oCmd.CreateParameter("ProgramName", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInputOutput)


            conn.Open()
            'assign values to ADO objects
            'rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient

            'create an instance of excel and add a workbook

            xlApp = CreateObject("Excel.application")
            xlWb = xlApp.Workbooks.add
            xlWs = xlWb.Worksheets("Sheet1")
            Dim strFileName = "ViewByProgram_03092007.xls"
            xlApp.SaveAs(strFileName)

            'display excel or give user control
            xlApp.visible = False
            xlApp.UserControl = False

            'Add the table headers

            xlWs.Range("A1").Value = "Record ID"
            xlWs.Range("B1").Value = "Program"
            xlWs.Range("B1").ColumnWidth = 40
            xlWs.Range("C1").Value = "Platform Type"
            xlWs.Range("D1").Value = "Rights"
            xlWs.Range("E1").Value = "OptionType"
            xlWs.Range("F1").Value = "Estimated Cost Range"
            xlWs.Range("G1").Value = "Date Option expires"

            'copy field names to the first row of the worksheet
            fldCount = rst.Fields.Count
            For iCol = 2 To fldCount
                xlWs.Cells(1, iCol).value = rst.Fields(iCol - 1).Name
            Next

            'check the version of Excel (I got this from Microsoft's website)
            If Val(Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)) > 8 Then
                'excel 2000 or 2002: use copy from recordset
                'copy the recordset starting in the cell noted above iCol=2

                xlWs.Cells(2, 1).CopyFromRecordset(rst)
            Else
                'excel 97 or earlier, use getRows the copy array

                'copy recordset to array
                recArray = rst.GetRows
                recCount = UBound(recArray, 2) + 1 '+ 1 since 0 based array

                For iCol = 2 To fldCount - 1

                    For iRow = 0 To recCount - 1
                        'be careful of date fields
                        If IsDate(recArray(iCol, iRow)) Then
                            recArray(iCol, iRow) = Format(recArray(iCol, iRow))
                            'take care of ole object fields or array fields
                        ElseIf IsArray(recArray(iCol, iRow)) Then
                            recArray(iCol, iRow) = "Array Field"
                        End If
                    Next iRow 'next record
                Next iCol 'next field

                'transpose and copy the array to the worksheet
                'Dim TransposeDim() this requires a different function
                'xlWs.cells(3, 1).Resize(recCount, fldCount).Value = TransposeDim(recArray)

            End If

            'auto fit the column widths

            xlApp.Selection.CurrentRegion.Columns.AutoFit()
            xlApp.Selection.CurrentRegion.Rows.AutoFit()


            'close ado objects
            conn.Close()
            'rst.Close()
            rst = Nothing

            'release excel references

            xlWs = Nothing
            xlWb = Nothing
            xlApp = Nothing

        Catch ex As Exception
            'read the errors

            outputPanel.Visible = True
            outputPanel.Text = "Error:<br>" & ex.Message.ToString

        End Try


    End Sub

==end of sub====

 

[radiance]

thanks guys for your help...

I revised my code and the export works successfully! Thank you!