Re: Write data to Excel VB.Net [2005] (Resolved)

[FastEddie]

I am trying to use this code and am not able to get the data from the dataset into the datatable that I need for the export.

        Dim cnn As New SqlConnection(strCon)
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        'Try
        cmd = cnn.CreateCommand
        cmd.CommandText = "SELECT StoreId, PurchaseOrderNumber....."
        da.SelectCommand = cmd
        da.Fill(ds, "PurchaseOrders")
        dgvPoNumbers.DataSource = ds
        dgvPoNumbers.DataMember = "PurchaseOrders"

        Dim poTable As DataTable = ds.Tables.Add("OrderExport")
        Export("c:\myFile.xls", poTable)

I just keep getting a blank sheet.

[RobDog888]

Post split to separate new thread.

[jmcilhinney]

That's because you're passing a blank table to the Export method. This line:

Dim poTable As DataTable = ds.Tables.Add("OrderExport")

Creates a new DataTable named "OrderExport", adds it to your DataSet and returns a reference to it. You don't add any schema, let alone data, before calling Export.

[FastEddie]

I can make it work by adding a second dataset (da2 instead of da).

            da2.SelectCommand = cmd
            da2.Fill(ds2, "OrderExport")
            Dim poTable As DataTable = ds2.Tables("OrderExport")
            Export("c:\myFile.xls", poTable)

But why can't I use the existing dataset without having to recreate it?

[RobDog888]

Because you already have it populated with a different table.

[jmcilhinney]

Just as Rob says. Look at your first code snippet in post #1. You're populating a table named "PurchaseOrders" and then trying to export data from a table named "OrderExport". Where do you put any data into "OrderExport"? Your second code snippet DOES populate a table named "ExportTable" so, lo and behold, it contains data to export. You don't need two DataSets. One DataSet can contain as many DataTables as you like. You simply have to put some data into a table before you can get any out. Does that sound like life in general?

[FastEddie]

Ok thanks. One more question on this subjust before I mark it resolved. I want to check for records in the dataset before I do the export. Is there a way to say:

            'Psudo Code Here
            If ds (has records)  Then
                Dim poTable As DataTable = ds.Tables("OrderExport")
                Dim dtDate As Date = Today.Date
                Dim strExportName As String = _
                    "C:\mighty\mighty_" & dtDate.ToString("yyyy-MM-dd")
            Else
                Exit Sub
            End If

[ProphetBeal]

Ok thanks. One more question on this subjust before I mark it resolved. I want to check for records in the dataset before I do the export. Is there a way to say:

Here is the way i normally check to make sure a dataset i just populated has data in it. I'm some may think its overkill but its always worked for me

If Not IsNothing(ds) AndAlso ds.Tables.Count > 0 AndAlso ds.Tables("OrderExport").Rows.Count > 0 Then
          Dim poTable As DataTable = ds.Tables("OrderExport")
          Dim dtDate As Date = Today.Date
          Dim strExportName As String = _
               "C:\mighty\mighty_" & dtDate.ToString("yyyy-MM-dd")
     Else
          Exit Sub
     End If

[FastEddie]

Thanks that worked perfectly.

[jmcilhinney]

There's a problem with that code. First it checks whether there is a DataSet, then it checks whether the DataSet has DataTables, then it checks whether the "OrderExport" table contains rows. At no point does it check whether there is a DataTable named "OrderExport". I would think that with properly written code there should always be a table with that name but if you're going to go to all the trouble of doing all that checking then you need to check for the existence of that specific table too or the last condition could crash. It should be:

If ds IsNot Nothing AndAlso ds.Tables.Contains("OrderExport") AndAlso ds.Tables("OrderExport").Rows.Count > 0 Then

Note that "ds IsNot Nothing" is for 2005 only in previous versions use "Not ds Is Nothing". Please specify your version so we know. Also, don't use the IsNothing function at all.

[ProphetBeal]

At no point does it check whether there is a DataTable named "OrderExport".

Thats a very good point. Normally when I use this code I don't refer to the table by name, i just refer to the first instance.

ds.Tables(0).Rows.Count

Also, don't use the IsNothing function at all.

Why should i not be using the "IsNothing" function?

[jmcilhinney]

Why should i not be using the "IsNothing" function?

The same reason that you shouldn't use the MsgBox function. From the MSDN help topic for the IsNothing function:

Namespace: Microsoft.VisualBasic

Module: Information

Assembly: Visual Basic Runtime Library (in Microsoft.VisualBasic.dll)