Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error

Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error

Post by Wally Highsmit » Mon, 12 May 2003 00:19:24



The MSDN documentation states:
You can use the Fill method multiple times on the same DataTable. If a
primary key exists, incoming rows are merged with matching rows that already
exist. If no primary key exists, incoming rows are appended to the
DataTable.

However; I am not finding this to be the case.  Whenever I call
.Fill(strongly_typed_dataset) multiple times against a DataTable with a
primary key defined I get the following error:

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

Am I doing something wrong, is the documentation incorrect, or is this
problem a bug?

thanks,

wally

 
 
 

Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error

Post by Scot Rose [MSF » Tue, 13 May 2003 22:59:05


Do you have a small project that demonstrates the problem that you can send to me to take a look at?

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com  or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support

This posting is provided AS IS, with no warranties, and confers no rights.

--------------------


>Subject: Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error
>Date: Sat, 10 May 2003 11:19:24 -0400
>Lines: 26
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106

>Newsgroups: microsoft.public.dotnet.framework.adonet
>NNTP-Posting-Host: mail.knowlagent.com 66.255.64.2
>Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:50824
>X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

>The MSDN documentation states:
>You can use the Fill method multiple times on the same DataTable. If a
>primary key exists, incoming rows are merged with matching rows that already
>exist. If no primary key exists, incoming rows are appended to the
>DataTable.

>However; I am not finding this to be the case.  Whenever I call
>.Fill(strongly_typed_dataset) multiple times against a DataTable with a
>primary key defined I get the following error:

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

>Am I doing something wrong, is the documentation incorrect, or is this
>problem a bug?

>thanks,

>wally


 
 
 

Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error

Post by Al » Mon, 26 May 2003 05:48:08


I'm getting the same error.  Here is sample code using the Northwind db.  I
also noticed that when I use a datatable that is not a member of a dataset,
then I do not get the error, but I get unexpected results.  The first Fill
works fine and loads the CompanyName, but the second Fill adds the Address
as expected, but nulls the CompanyName column.  It's mystifying.

Thanks, Al

Private Sub LoadData()
    Dim ds As New DataSet()
    Dim cmd As New SqlClient.SqlCommand("Select CustomerID, CompanyName FROM
Customers")

    cmd.CommandType = CommandType.Text
    cmd.Connection = Me.SqlConnection1
    cmd.Connection.Open()

    Me.SqlDataAdapter1.SelectCommand = cmd
    Me.SqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey
    Me.SqlDataAdapter1.Fill(ds)

    cmd.CommandText = "Select CustomerID, Address FROM Customers"
    cmd.CommandType = CommandType.Text
    cmd.Connection = Me.SqlConnection1

    ' This second Fill fails with error:
    ' Failed to enable constraints. One or more rows contain values
violating non-null, unique, or foreign-key constraints.
    Me.SqlDataAdapter1.Fill(ds.Tables(0))
    cmd.Connection.Close()
    Me.DataGrid1.DataSource = ds.Tables(0)

End Sub



Quote:> Do you have a small project that demonstrates the problem that you can

send to me to take a look at?
Quote:

> Want to know more? Check out the MSDN Library at http://msdn.microsoft.com

or the Microsoft Knowledge Base at http://support.microsoft.com

> Scot Rose, MCSD
> Microsoft Visual Basic Developer Support

> This posting is provided "AS IS", with no warranties, and confers no
rights.

> --------------------

> >Subject: Calling DataAdapter.Fill Multiple Times on Strongly Typed

DataSet Causes Constraint Error
> >Date: Sat, 10 May 2003 11:19:24 -0400
> >Lines: 26
> >X-Priority: 3
> >X-MSMail-Priority: Normal
> >X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106

> >Newsgroups: microsoft.public.dotnet.framework.adonet
> >NNTP-Posting-Host: mail.knowlagent.com 66.255.64.2
> >Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
> >Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:50824
> >X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

> >The MSDN documentation states:
> >You can use the Fill method multiple times on the same DataTable. If a
> >primary key exists, incoming rows are merged with matching rows that
already
> >exist. If no primary key exists, incoming rows are appended to the
> >DataTable.

> >However; I am not finding this to be the case.  Whenever I call
> >.Fill(strongly_typed_dataset) multiple times against a DataTable with a
> >primary key defined I get the following error:

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

> >Am I doing something wrong, is the documentation incorrect, or is this
> >problem a bug?

> >thanks,

> >wally

 
 
 

Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error

Post by Conrad Fr » Sat, 31 May 2003 07:15:13


Wally you might want to trap the FillError Event. That way you can be
sure its not somthing you overlooked.

Conrad


> The MSDN documentation states:
> You can use the Fill method multiple times on the same DataTable. If a
> primary key exists, incoming rows are merged with matching rows that already
> exist. If no primary key exists, incoming rows are appended to the
> DataTable.

> However; I am not finding this to be the case.  Whenever I call
> .Fill(strongly_typed_dataset) multiple times against a DataTable with a
> primary key defined I get the following error:

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

> Am I doing something wrong, is the documentation incorrect, or is this
> problem a bug?

> thanks,

> wally

 
 
 

Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error

Post by Scot Rose [MSF » Wed, 04 Jun 2003 03:33:55


You are NOT going to believe this.... The Constraint error was being caused because the CompanyName field in your example is a Required field (You would have to include
required fields in your queries) See the example code below... Beyond that, the Fill doesn't 'merge' the data in the way you are expecting, It requires that all of the fields in the
original query be present in subsequent querries, what you are probably needing is the Dataset.Merge method...  I went around and around with this for several hours trying to get
the Primary Key field to not cause problems only to realize that wasn't the field that was giving the error.

        Dim SQLAdapt As New SqlClient.SqlDataAdapter()
        Dim ds As New DataSet()
        Dim DS2 As New DataSet()
        Dim cmd As New SqlClient.SqlCommand("Select CustomerID, CompanyName, ContactName FROM Customers")

        cmd.CommandType = CommandType.Text
        cmd.Connection = MyConn

        SQLAdapt.SelectCommand = cmd

        SQLAdapt.Fill(ds, "Customer")

        Dim myColArray(1) As DataColumn
        myColArray(0) = ds.Tables("Customer").Columns("CustomerID")
        myColArray(0).Unique = True
        ds.Tables("Customer").PrimaryKey = myColArray

        cmd.CommandText = "Select CustomerID, CompanyName, region FROM Customers"
        cmd.CommandType = CommandType.Text
        SQLAdapt.SelectCommand = cmd
        ' This second Fill fails with error:
        ' Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

        SQLAdapt.Fill(DS2, "Customer")

        ds.Merge(DS2.Tables("Customer"))
        DataGrid1.DataSource = ds.Tables("Customer")

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com  or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support

This posting is provided AS IS, with no warranties, and confers no rights.

--------------------

>From: "Al" <Al C.>

>Subject: Re: Calling DataAdapter.Fill Multiple Times on Strongly Typed DataSet Causes Constraint Error
>Date: Sat, 24 May 2003 16:48:08 -0400
>Lines: 101
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106

>Newsgroups: microsoft.public.dotnet.framework.adonet
>NNTP-Posting-Host: 209.227.1.32
>Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:51946
>X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

>I'm getting the same error.  Here is sample code using the Northwind db.  I
>also noticed that when I use a datatable that is not a member of a dataset,
>then I do not get the error, but I get unexpected results.  The first Fill
>works fine and loads the CompanyName, but the second Fill adds the Address
>as expected, but nulls the CompanyName column.  It's mystifying.

>Thanks, Al

>Private Sub LoadData()
>    Dim ds As New DataSet()
>    Dim cmd As New SqlClient.SqlCommand("Select CustomerID, CompanyName FROM
>Customers")

>    cmd.CommandType = CommandType.Text
>    cmd.Connection = Me.SqlConnection1
>    cmd.Connection.Open()

>    Me.SqlDataAdapter1.SelectCommand = cmd
>    Me.SqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey
>    Me.SqlDataAdapter1.Fill(ds)

>    cmd.CommandText = "Select CustomerID, Address FROM Customers"
>    cmd.CommandType = CommandType.Text
>    cmd.Connection = Me.SqlConnection1

>    ' This second Fill fails with error:
>    ' Failed to enable constraints. One or more rows contain values
>violating non-null, unique, or foreign-key constraints.
>    Me.SqlDataAdapter1.Fill(ds.Tables(0))
>    cmd.Connection.Close()
>    Me.DataGrid1.DataSource = ds.Tables(0)

>End Sub



>> Do you have a small project that demonstrates the problem that you can
>send to me to take a look at?

>> Want to know more? Check out the MSDN Library at http://msdn.microsoft.com
>or the Microsoft Knowledge Base at http://support.microsoft.com

>> Scot Rose, MCSD
>> Microsoft Visual Basic Developer Support

>> This posting is provided "AS IS", with no warranties, and confers no
>rights.

>> --------------------

>> >Subject: Calling DataAdapter.Fill Multiple Times on Strongly Typed
>DataSet Causes Constraint Error
>> >Date: Sat, 10 May 2003 11:19:24 -0400
>> >Lines: 26
>> >X-Priority: 3
>> >X-MSMail-Priority: Normal
>> >X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106

>> >Newsgroups: microsoft.public.dotnet.framework.adonet
>> >NNTP-Posting-Host: mail.knowlagent.com 66.255.64.2
>> >Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
>> >Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:50824
>> >X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

>> >The MSDN documentation states:
>> >You can use the Fill method multiple times on the same DataTable. If a
>> >primary key exists, incoming rows are merged with matching rows that
>already
>> >exist. If no primary key exists, incoming rows are appended to the
>> >DataTable.

>> >However; I am not finding this to be the case.  Whenever I call
>> >.Fill(strongly_typed_dataset) multiple times against a DataTable with a
>> >primary key defined I get the following error:

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

>> >Am I doing something wrong, is the documentation incorrect, or is this
>> >problem a bug?

>> >thanks,

>> >wally

 
 
 

1. dataset constraints causing error after calling fill on multiple tables

ok, just found out that the order of my select statements is important as
the dataset apparently checks constraints as each table is filled --
therefore, it seems as though you have to order your queries (in the batch)
from the most -> least independent tables.
1) is this conclusion correct?
2) is there a way to change this behavior so that I can fill all tables (in
any order) and then enable constraint checking after all tables are filled?

_howard


2. NEW PDA SOFTWARE WEB SITE!

3. Strongly typed dataset - can't find strongly typed row type

4. Memory management for by .NET Application

5. Unique Constraint in Strongly Typed Dataset

6. Oracle DBA/Sys. Adm - MRP bckgrd, 10.5 Perm in San Jose, CA - Recruiter

7. Filling a Strongly Typed Dataset?

8. What's So Special about Specialization?

9. Filling strongly typed datasets with Microsoft.ApplicationBlocks.Data

10. filling strongly typed dataset using SELECT with diff. column names

11. DataAdapter.Fill() example for multiple fills.

12. How to fill my Typed DataSet with DataAdapter?

13. Strongly Typed DataSets with Multiple Tables