none
DataAdapter.Fill fails on the first view if do FillSchema for a view RRS feed

  • Question

  • With Dataset constraints enabled, I fill the dataadapter with the schema and data from a few tables and then several views. The first view always fails because of constraints (see msg below). It does not matter which view I put first. I can also load ALL my tables and then the views and the first view still always fails to fill but the remaining views always fill just fine. This seems rather odd to me. I have checked and double checked there are no missing values or FK issues and I can execute the views just fine manually or with constraints turned off. If I do NOT do a FillSchema for the views, but rather just for the tables, then everything loads just fine. So maybe the moral is to never FillSchema for views but still it seems odd only the first one fails and none of the subsequent! The message I always get is "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
    Thursday, June 4, 2009 4:41 PM

Answers

  • I think the answer is the following. With FillSchema it will create ALL of the constraints. When it creates all of them there is some specific error that it encounters, likely because it does not understand the shape.

    But when you only call adapter.Fill, it does not create the whole schema only critical pieces.
    http://msdn.microsoft.com/en-us/library/377a8x4t.aspx

          The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created. The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

    Fill Schema is documented here:
    http://msdn.microsoft.com/en-us/library/229sz0y5.aspx

    A FillSchema operation adds a DataTable to the destination DataSet. It then adds columns to the DataColumnCollection of the DataTable, and configures the following DataColumn properties if they exist at the data source:

    FillSchema also configures the PrimaryKey and Constraints properties according to the following rules:

    • If one or more primary key columns are returned by the SelectCommand, they are used as the primary key columns for the DataTable.

    • If no primary key columns are returned but unique columns are, the unique columns are used as the primary key if, and only if, all the unique columns are nonnullable. If any of the columns are nullable, a UniqueConstraint is added to the ConstraintCollection, but the PrimaryKey property is not set.

    • If both primary key columns and unique columns are returned, the primary key columns are used as the primary key columns for the DataTable.

    One possible work around would be to do the following:
    I would call FillSchema and pass in the DataSet not each individual DataTable instead.

    But hey if its working with out calling FillSchema, I would just not call it.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 5, 2009 9:31 PM

All replies

  • Hey CodeSlinger,

    Which version of VS are you using? if you are using VS 2008, you can use the TableManagerAdapter, this would likely fill things properly.

    When you say a view, do you mean on the db server there is a view, and this is brought over and created a DataTable?

    What is the schema of the view? What are the relationships that it has? Does it have multiple keys? Do some of these keys allow null? DataSet will not allow a key to be null, that could be the issue. I need more information on the shape of the schema.

    Thanks
    Chris Robinson
    Program manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 5, 2009 1:46 AM
  • Hi Chris,

    I'm using VS2008 but amnot using typed datasets so don't think I can use TableManagerAdapter.

    I have several views on SQL Server 2008 and they display just fine there.

    Th views are just various types of joins on my tables all of which have FK constraints enabled and cascading turned on for all relationships. I can "fill" them and their schema into the ado.net dataset just fine with constraints enforced. It is only the first view that fails, nothing else.

    I am not sure what you mean by a SQL view having relationships, keys and schema but I do not have any foreign keys or references that have any null data in any of my tables.

    When I execute the DataAdapter.Fill method I give it a "select * from xxx" where xxx is the name of my table or view. My current workaround is to not call the DataAdapter.FillSchema for the views but just for the tables. That works but I'm puzzled why I cannot do a FillSchema for just the first view, no matter which is first, and then all the rest of the views work. I FillSchema and Fill the data for all the tables first and then do the views.

    Thanks, Dave

    Friday, June 5, 2009 3:03 AM
  • Hey Dave,

    So the what you are doing is calling FillSchema first to fill a DataTable, then you call Fill and the data is populated in for one view. What kind of schema is it trying to fill into the one view and failing? Can you paste a dump of what the schema is? Is this view in the database, is it updatable? Can you update that view that fails to FillSchema initially? Do you call adapter.Update with the data that was filled from the first view? If it doesn't need to be updated you could just set the readonly  property on the DataColumn of all the tables if they can't be edited. All of the other schema you are filling may be unnecessary.

    Thanks,
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 5, 2009 4:14 PM
  • No, I called FillSchema then Fill for the first view, no matter which view was first, and the Fill ALWAYS FAILED! Honestly, I had no reason to call FillSchema for a view at all but was just calling it for all the database tables and views that I was loading. What I do now - I do not call FillSchema for any of the views, I call it only for the tables. That works but I was wondering why the former technique failed so oddly. Do database views even have schema? I have no idea of what a view schema is or how to dump it. To me a vies is typically some T-SQL with a join between two tables.  As I said, I can put ANY of a dozen views first in the list of what I was filling into the dataset and the first Fill always failed and all the rest did not iff I did a FillSchema for the view first. So it is not a matter of a particular view causing the problem. Just odd the first one always fails and the others always work.
    Friday, June 5, 2009 4:28 PM
  • I think the answer is the following. With FillSchema it will create ALL of the constraints. When it creates all of them there is some specific error that it encounters, likely because it does not understand the shape.

    But when you only call adapter.Fill, it does not create the whole schema only critical pieces.
    http://msdn.microsoft.com/en-us/library/377a8x4t.aspx

          The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created. The Fill operation then adds the rows to destination DataTable objects in the DataSet, creating the DataTable objects if they do not already exist. When creating DataTable objects, the Fill operation normally creates only column name metadata. However, if the MissingSchemaAction property is set to AddWithKey, appropriate primary keys and constraints are also created.

    Fill Schema is documented here:
    http://msdn.microsoft.com/en-us/library/229sz0y5.aspx

    A FillSchema operation adds a DataTable to the destination DataSet. It then adds columns to the DataColumnCollection of the DataTable, and configures the following DataColumn properties if they exist at the data source:

    FillSchema also configures the PrimaryKey and Constraints properties according to the following rules:

    • If one or more primary key columns are returned by the SelectCommand, they are used as the primary key columns for the DataTable.

    • If no primary key columns are returned but unique columns are, the unique columns are used as the primary key if, and only if, all the unique columns are nonnullable. If any of the columns are nullable, a UniqueConstraint is added to the ConstraintCollection, but the PrimaryKey property is not set.

    • If both primary key columns and unique columns are returned, the primary key columns are used as the primary key columns for the DataTable.

    One possible work around would be to do the following:
    I would call FillSchema and pass in the DataSet not each individual DataTable instead.

    But hey if its working with out calling FillSchema, I would just not call it.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 5, 2009 9:31 PM