table.fill

Feb 17, 2010 at 9:23 PM

I am very eager to start using ExtremeML; however, anytime I try to fill the table in Excel with data from a dataset, it says that Value cannot be null. Parameter name: table. Can someone fill me in on how to populate the fill method with a dataset from SQL Server? Thanks!

Coordinator
Feb 18, 2010 at 8:32 AM

Here's a tutorial that shows how to fill a table from a DataSet:

http://www.extrememl.com/index.php/knowledge-center/tutorials/working-with-excel-tables/populating-a-table-from-a-data-source

Feb 18, 2010 at 3:21 PM
TimCoulter wrote:

Here's a tutorial that shows how to fill a table from a DataSet:

http://www.extrememl.com/index.php/knowledge-center/tutorials/working-with-excel-tables/populating-a-table-from-a-data-source

 Thanks for the speedy response. I did take a look at this tut but I am still having issues. Either the table will be empty when I run the script or it will produce an error that the value cannot be null. Do you have any suggestions?

Coordinator
Feb 18, 2010 at 4:19 PM

The problem is clearly either in the code or the template. The template simply needs to contain an empty table (with one blank row) with columns that correspond to some or all of the columns in your DataTable. It is essential that the table is named to match the name used in your code to reference it. This is a likely candidate for the null reference exception that you are seeing.

Assuming you are using the simple column mapping, the DataTable columns need to be in the same sequence as the columns in the template. If you are using named column mappings, the DataTable columns can be in any sequence, but the mappings must correspond to the template table column names.

It would be helpful if you could post a sample of your code so that I can tell you whether there is obviously sonething wrong with it.

Feb 18, 2010 at 5:26 PM
            Thanks for the info. I looked and I saw I confirmed that I have the table name as DriverID. 
 
	   SqlDataAdapter adapter;
            SqlConnection conn;
            DataSet dataSet = new DataSet();

            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnectionString"].ToString();
            conn = new SqlConnection(connectionString);
            adapter = new SqlDataAdapter("Select DriverID from Driver", conn);
            adapter.Fill(dataSet);
            
            using (var package = ExtremeML.Packaging.SpreadsheetDocumentWrapper.Open(PATH))
            {                
                var table = package.WorkbookPart.GetTablePart("DriverID").Table;
                table.Fill(dataSet, "Driver");
            }
Coordinator
Feb 18, 2010 at 6:27 PM

It would probably be easiest to debug this by stepping through your code. At the very least, we need to know whether the exception is being thrown on the call to GetTablePart() or Fill().

If the TableWrapper is correctly retrieved using GetTablePart() and the DataSet does actually contain a table named "Driver" there is no reason why this should fail.

Perhaps you could post a stack trace.

Feb 18, 2010 at 11:55 PM

Here is what I got from the stack..

 	ExtremeML.DLL!ExtremeML.Spreadsheet.TableWrapper.Fill(System.Data.DataTable table = null) Line 151	C#
>	ExtremeML.DLL!ExtremeML.Spreadsheet.TableWrapper.Fill(System.Data.DataSet dataSet = {System.Data.DataSet}, string table = "Driver") Line 132 + 0x2e bytes	C#
 	WaltsWebBibleApp.DLL!WaltsWebBibleApp.WebForm3.sub() Line 129 + 0x13 bytes	C#
 	WaltsWebBibleApp.DLL!WaltsWebBibleApp.WebForm3.Button2_Click(object sender = {Text = "Button"}, System.EventArgs e = {System.EventArgs}) Line 136 + 0x8 bytes	C#


 

Coordinator
Feb 19, 2010 at 7:19 AM
Edited Feb 19, 2010 at 7:20 AM

This exception is being thrown because the specified DataTable does not exist in the passed DataSet.

You can prove this by inserting the following line before the call to TableWrapper.Fill():

  System.Diagnostics.Debug.Assert(dataSet.Tables["Driver"] != null);

By default, she DataSet is created using names that match the database content from which it derives, so I suspect you may have mistaken your table name. The following code will examine your DataSet and list the DataTables within:

  foreach (DataTable table in dataSet.Tables)
  {
 	System.Diagnostics.Debug.WriteLine(table.TableName);
  }
Feb 19, 2010 at 2:08 PM
TimCoulter wrote:

This exception is being thrown because the specified DataTable does not exist in the passed DataSet.

You can prove this by inserting the following line before the call to TableWrapper.Fill():

  System.Diagnostics.Debug.Assert(dataSet.Tables["Driver"] != null);

By default, she DataSet is created using names that match the database content from which it derives, so I suspect you may have mistaken your table name. The following code will examine your DataSet and list the DataTables within:

  foreach (DataTable table in dataSet.Tables)
  {
 	System.Diagnostics.Debug.WriteLine(table.TableName);
  }

 Thanks. The debugging really helped me narrow down the problem. I guess it would help if I declare a table variable that I actually use it in the code. :-P Works great now! Thanks!