Re: Write data to Excel VB.Net [2005] (Resolved)
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.
Code:
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.
Re: data from the dataset
Post split to separate new thread. ;)
Re: data from the dataset
That's because you're passing a blank table to the Export method. This line:
vb Code:
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.
Re: data from the dataset
I can make it work by adding a second dataset (da2 instead of da).
Code:
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?
Re: data from the dataset
Because you already have it populated with a different table.
Re: data from the dataset
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?
Re: data from the dataset
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:
Code:
'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
Re: data from the dataset
Quote:
Originally Posted by 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:
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 :)
vb Code:
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
Re: data from the dataset
Thanks that worked perfectly.
Re: Write data to Excel VB.Net [2005] (Resolved)
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:
vb Code:
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.
Re: Write data to Excel VB.Net [2005] (Resolved)
Quote:
Originally Posted by jmcilhinney
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.
Quote:
Originally Posted by jmcilhinney
Also, don't use the IsNothing function at all.
Why should i not be using the "IsNothing" function?
Re: Write data to Excel VB.Net [2005] (Resolved)
Quote:
Originally Posted by ProphetBeal
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:
Quote:
Namespace: Microsoft.VisualBasic
Module: Information
Assembly: Visual Basic Runtime Library (in Microsoft.VisualBasic.dll)