This message was edited by chip050555 at 2005-10-18 13:37:53

---

I am trying to read data from an Excel spreadsheet into a dataset so that I can get the sum of a column. I keep getting a "Value cannot be Null. Parameter Name: Dataset." What am I doing wrong?

Option Strict On
Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Private MyXLEmeraldDS As New DataSet

    Private MyXLEmeraldConn As New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; & data source = \\Srv\SMT\T22210023EmeraldX_CMP.xls; & Extended Properties = Excel 8.0")

Private MyXLSQL As String = "SELECT * FROM [Component2$]"    'R5C13:R54C13

    Private MyXLEmeraldComm As New OleDbDataAdapter(MyXLSQL, MyXLEmeraldConn)

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            MyXLEmeraldComm.Fill(MyXLEmeraldDS, "[Component2$]")

        Catch x As Exception
            MessageBox.Show(x.Message)
        End Try
        MyXLEmeraldConn.Close()
      End Sub

Any Suggestions would be nice.

hmmm, Might try giving the dataset a name when call 'New' for it...

I have not used the table mapping part of the fill which is somehow related to your problem. I suspect if you left the table name out it would work. Is there really a table with that name 'Component2$' in the ADO recordset the query gets for the excel sheet? Also, I am sure you need to obmit the square brakets on your table string in the fill function, this is not part of the SQL and it is taking those as literal. Hope some of that fixes your issue.

~rlc

: hmmm, Might try giving the dataset a name when call 'New' for it...
:
: I have not used the table mapping part of the fill which is somehow related to your problem. I suspect if you left the table name out it would work. Is there really a table with that name 'Component2$' in the ADO recordset the query gets for the excel sheet? Also, I am sure you need to obmit the square brakets on your table string in the fill function, this is not part of the SQL and it is taking those as literal. Hope some of that fixes your issue.
:
: ~rlc
:
rlc

I got the spreadsheet to load into a dataset and datatable. I now have to total a column that has no name other than F13 in the datagrid view. How can I specify the column I want to total. The spreadsheet may contain from a few rows of data to thousands of rows.

I plan an adding with a for/next statement till I get to (MyXLDT.Rows.Count - 1) is reached.