Introduction
Here, we will see how to fill any TableAdapter
DataTable
and avoid the dreaded:
Server Error in '/REDB' Application.
Failed to enable constraints. One or more rows contain values
violating non-null, unique, or foreign-key constraints.
Background
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 to 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;
Using the code
Create a web project. Name it. Save it. Create a DataSet
referencing the venerable Northwind database. Allow the DataSet
to be saved in the App_Code folder.
Create a ProductsTableAdapter
using the Products table. Let's use a sample TableAdapter
named ProductsTableAdapter
which belongs to the venerable NorthwindDataSet
we have already created. (Create it now, I'll wait.)
Create your custom Get
and Fill
methods. Name one GetProductNames
and the other FillProductNames
.
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();
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, because 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 object.
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), let's fix the most obvious issues:
columns = tbl.Columns;
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.)
Adaptr.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 TableAdapter
s is available on CodeProject.
Points of Interest
tbl.Constraints.Clear()
is not the solution to the problem. AllowDBNull
is the actual solution in every case. It took me a few seconds to realize, I almost always returned a DataTable
, and never tried to pass in a DataTable
to fill. That's when I realized the usefulness of the fill DataTable
custom methods.
You can always try to change the schema or create separate TableAdapter
s to produce a schema consistent with your data, but why bother? This method works very well.
The first Select query you create is the schema basis for every other query. It becomes the default query and its method is decorated with the DataObjectMethodAttribute
and Select
is set to true
. Now, all other queries must use this default schema. This is why you get the warning dialog as shown above.
Notice the check mark next to the SQL. This is the first and default Select query in the TableAdapter
. The second query becomes CommandCollection[1]
and so on.
Thank you, Yyu've been a wonderful audience.
RickIsWright - www.rickiswright.com.
History