Click here to Skip to main content
Click here to Skip to main content

Fill any TableAdapter Using Any of Your Select Methods

, 12 May 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Avoid the pesky table adapter load error failed to enable constraints.

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:

SchemaWarning.png

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, 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; //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.)

Adaptr.FillProductNames(tbl); //No Errors. Yes!

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 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 TableAdapters 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

  • Published: 5-11-2009.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

rickwright2000
Web Developer
United States United States
Currently a Systems Administrator and Programmer for a large county government agency. I am very impressed with the capabilities of the C# and Managed C++ language(My primary coding languages).
Perl, PHP and ASP.Net are definitely high on my appreciation list.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 390397413-Nov-12 18:31 
GeneralSchema changes Pinmemberthookerov30-Oct-09 10:03 
Your approach did work for me. However, i later discovered the apparent CAUSE of the problem in my case:
 
i made some alterations to the source table on the database, which involved removing a column. i was smart enough to go back to my DataSet designer and re-run the Configure wizard on the DataTable object. In theory, refreshing all columns the table recognized and would in turn propagate to TableAdapter queries.
 
The DataTable updated with new columns i'd added. What i didn't see was that it DIDN'T remove the column i deleted! And it wasn't a simple matter of deleting the column from the DataTable in the designer. Since i didn't have a lot of custom queries built on it, i deleted the DataTable and re-built it.
QuestionHmmm. Pinmemberstixoffire17-May-09 19:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 12 May 2009
Article Copyright 2009 by rickwright2000
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid