First, let's get an overview of the TableAdapter. You know what one is and how to use it, or you wouldn't even be here. 

• We know the TableAdapter is not a .NET framework object. (It is a VS Designer created object, so don't try browse for a TableAdapter .NET object.)
• Inherits From System.ComponentModel.Component NOT DataAdapter.
• Encapsulates a DataAdapter, i.e., SQLDataAdapter, etc.
• Encapsulates a SQLConnection
• Encapsulates a SQL CommandCollection

private global::System.Data.SqlClient.SqlDataAdapter _adapter;
private global::System.Data.SqlClient.SqlConnection _connection;
private global::System.Data.SqlClient.SqlCommand[] _commandCollection;

Let's use a sample TableAdapter named ProductsTableAdapter which belongs to the venerable NorthwindDataSet we have already created. (Create it now, I'll wait.)

When you attempt to call one of your custom Fill Methods created in your ProductsTableAdapter that does not include every DataColumn found in the default Select Method's DataTable, you end up with this interesting dialog warning message.

This warning does not translate to much until you try to invoke your custom GetProductNames() method that only returns a list of product names.

When you invoke this method using the Configure and Preview provided by Sir Wizard, everything looks great.  All the product datatable columns show up empty except the product name.  Great! This is exactly what we are looking for. (Actually, I think most of us expected a list of product names, with no other columns of data.)

Well, we can live with it for now. Let's just fill this thing with data so we can at least use the product names list

Most of us do something like this:

NorthwindDataSetTableAdapters.ProductsTableAdapter Adaptr = new ProductsTableAdapter(); NorthwindDataSet.ProductsDataTable tbl =

new NorthwindDataSet.ProductsDataTable(); 

Just for good measure, we clear any table constraints that may prevent our table from filling.

tbl.Constraints.Clear(); 

Now we invoke the fill method:

tbl=Adaptr.GetProductNames();  //FILL the table and herein lies the problem. 

The Dreaded Error:

Server Error in '/REDB' Application.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

What in the world just happened?  We know it worked in Preview Data. (You are going to scream when I state the obvious, but first, a brief analysis:)

Did we clobber our tbl object with the object returned by the GetProductNames() call? Nope, 'cause that call failed in the Fill method call. Let's review the call stack.

Line 9506:            this.Adapter.SelectCommand = this.CommandCollection[1];
Line 9507:            NorthwindDataSet.ProductsDataTable dataTable = new NorthwindDataSet.ProductsDataTable();
Line 9508:            this.Adapter.Fill(dataTable);
Line 9509:            return dataTable;
Line 9510:        

We never made it to the return line. Also notice the CommandCollection called the second item in its Collection (More barely related information on that later).

Our tbl object was created earlier as a new ProductsDataTable. I did this on purpose. Normally we would declare a new object and assign to it all in one line of code: ProductsDataTable tbl = Adaptr.GetProductNames();

VB: Dim tbl AS ProductsDataTable = Adaptr.GetProductNames()

In either case, the call would have failed.

Since we have our tbl object instantiated as a new ProductsDataTable (the only way we can start working with it), Lets fix the most obvious issues: 

columns = tbl.Columns; //Get reference to the Columns Collection

foreach (DataColumn dc in columns) {dc.AllowDBNull = true;}

This is the only thing you need to do, because we will now pass in our tbl object to the FILL a datatable method, not the GET a datatable method. ( I warned you would hate the simplicity of it.)  

Adptr.FillProductNames(tbl);

Console.Write("Row Count is " tbl.Rows.Count.ToString());

Response.Write("Row Count is " tbl.Rows.Count.ToString());

A great article on Dynamic SQL for TableAdapters is on CodeProject