Exporting Dataset to Excel

Question

Greetings all,

I have the following bit of code that I have used in the past to export a DataSet generated from the Stored Procedure 'HISTORICAL_COSTS' to Excel.

        Dim c As Long = 1
        For Each dc As DataColumn In Me.WOCostDataSet. & _
        HISTORICAL_COSTS.Columns
            .Cells(1, c).Value = dc.ColumnName.ToString
            .Cells(1, c).Font.Bold = True
            c += 1
        Next

        Dim i As Long = 2
        For Each dr As DataRow In Me.WOCostDataSet.HISTORICAL_COSTS.Rows
            c = 1
            For Each dc As DataColumn In Me.WOCostDataSet. & _
            HISTORICAL_COSTS.Columns
                .Cells(i, c).Value = dr.Item(dc.ColumnName).ToString
                c += 1
            Next
            i += 1
        Next

I am trying to re-use this code an different but similar application, but, I am running into an issue. The previous use of this code was used on a static table in our dBase generated by the Stored Procedure. And while this basically remains the same for the new application, the requirements now require the stored procedure to have an input parameter to be entered by the user (through VB.net) prior to execution. For a little back-story, you can follow that completed process here - Injecting a Parameter into Stored Procedure.

The application itself does in fact return a fully populated dataset, and I'd like our users to have the ability to export that generated dataset to Excel. So, I set up your prototypical 'EXPORT ME' button to do start the dirty work.

Upon raising the event; Excel opens and only my column names are being reiterated throughout the sheet. But, and here is the problem, the cells representing the row data - are blank.

I have come to the conclusion (I do admit that I may be wrong in this assumption) that the rows are not being populated due to the fact that the Stored Procedure needs an input parameter to do it's thing, and without that parameter there isn't any data to return for each row. Basically meaning that my code just won't work for what I am trying to do.

If I am right in my assumptions, any ideas as to how I might get that parameter into my code above so that the rows will be properly generated.

If I am wrong, well, any input on what be wrong with my logic or the code itself would be greatly appreciated.

Thanks,

Jasoomian

Answer 1

You conclusion is definitely wrong -- once the dataset is populated the parameter is no longer a factor. I don't see anything obviously wrong with your code and, as you said, it worked elsewhere. I would start by setting a breakpoint and making sure that the dataset contains rows as you expect.

Answer 2

To answer your question, I think we need to see the code you're using to put the data onto the Excel worksheet. As Jasoomian says, I think the procedure you've given is correct.

Answer 3

Stan,

Here is the code that generates the dataset:

Try
        Dim FBConn As FbConnection
        Dim MyConnectionString As String

        MyConnectionString = "datasource=" _
                        & MyServer & ";database=" _
                        & TextBox4.Text & ";user id=SYSDBA;password=" _
                        & MyPassword & ";initial catalog=;Charset=NONE"

        FBConn = New FbConnection(MyConnectionString)
        Dim FBCmd As New FbCommand("HISTORICAL_COSTS", FBConn)

        FBCmd.CommandType = CommandType.StoredProcedure
        FBCmd.Parameters.Add("@I_PN", FbDbType.VarChar, 40)
        FBCmd.Parameters("@I_PN").Value = TextBox1.Text.ToUpper

        Dim FBadapter As New FbDataAdapter(FBCmd)
        Dim dsResult As New DataSet
        FBadapter.Fill(dsResult)

        Me.HISTORICAL_COSTSDataGridView.DataSource = dsResult.Tables(0)

        Dim RecordCount As Integer
        RecordCount = Me.HISTORICAL_COSTSDataGridView.RowCount
        Label4.Text = RecordCount

    Catch ex As System.Exception
        System.Windows.Forms.MessageBox.Show _
        ("There was an error in generating the DataStream, " & _
        "please check the system credentials and try again.  " & _
        "If the problem persists, please contact your friendly " & _
        "local IT department.")
    End Try

Answer 4

Stackoverflow kindly suggests that I offer a bounty to anyone answering my question,but, since I don't have enough REP to create a sufficient bounty - does my ever-encompassing gratitude garner any coding love?

A few quick updates:

I tested my application by altering the Stored Procedure to inject the results into a new separate table, and then run my excel export against that table - and it works fine. But, since many people will be using the application at the same time, this is not a viable solution.

So, I am back to believing that there is an issue with this instance of the dataset needing a parameter to run correctly for the export.

Happy to answer any and all questions to the best of my ability.