Failed to enable constraints

[Bill Cart]

I have an SQL query that gets data from an MS Access database. The query
worked OK until I put a filter into. Now I get an error saying:
"Failed to enable constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints."

The query is not an actual table in the database so I don't understand how
to deal with the constraint. The query works correctly on SQL Server
Analyzer so it must be something in Visual Studio that is causing the
problem.The datatable uses this stored procedure:

CREATE PROCEDURE dbo.SpDcClients454(@LastName varchar(20))
AS SELECT Clients_1.ClientID, Clients_1.LastName, Clients_1.FirstName,
Clients_1.MI, Clients_1.Address1, Clients_1.Phone, Clients_1.BirthDay,
ClientDiningCenters_1.DCCode
FROM ACCESSDC...Clients Clients_1 INNER JOIN
ACCESSDC...ClientDiningCenters ClientDiningCenters_1
ON Clients_1.ClientID = ClientDiningCenters_1.ClientID
WHERE (Clients_1.LastName LIKE @LastName + '%') AND
(ClientDiningCenters_1.DCCode = 454)
ORDER BY Clients_1.LastName, Clients_1.FirstName
GO

this.dcVouchersDataSet.Clear();

this.dcVouchersDataSet.EnforceConstraints = false;

this.spDcClientsTableAdapter.Fill(this.dcVouchersDataSet.SpDcClients,
textBox1.Text);

this.dcVouchersDataSet.EnforceConstraints = true;

 

[Nicholas Paldino [.NET/C# MVP]]

Bill,

If the underlying data source is an Access database, then why are you
using SQL Server Analyzer to check the query?

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I have an SQL query that gets data from an MS Access database. The query
worked OK until I put a filter into.

A filter?

Now I get an error saying:

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

The query is not an actual table in the database so I don't understand how
to deal with the constraint. The query works correctly on SQL Server
Analyzer so it must be something in Visual Studio that is causing the
problem.The datatable uses this stored procedure:

I was under the impression u were ussing Access?

this.dcVouchersDataSet.EnforceConstraints = true;

What constraints u have defined?

 

[Bill Cart]

The client data is currently in an MS Access database. That program will be
rewritten next year.

I have linked that database to the SQL server.as ACCESSDC. All of the other
tables in this Visual Studio project are in an SQL database but we have to
work with the existing client data.

Before I added the table join to the query it worked OK.

 

[Bill Cart]

In digging through the code that the "Wizard" wrote I found this

[System.Diagnostics.DebuggerNonUserCodeAttribute()]
private void InitClass() {
this.columnClientID = new System.Data.DataColumn("ClientID", typeof(int),
null, System.Data.MappingType.Element);
base.Columns.Add(this.columnClientID);
this.columnLastName = new System.Data.DataColumn("LastName", typeof(string),
null, System.Data.MappingType.Element);
base.Columns.Add(this.columnLastName);
this.columnFirstName = new System.Data.DataColumn("FirstName",
typeof(string), null, System.Data.MappingType.Element);
base.Columns.Add(this.columnFirstName);
this.columnMI = new System.Data.DataColumn("MI", typeof(string), null,
System.Data.MappingType.Element);
base.Columns.Add(this.columnMI);
this.columnAddress1 = new System.Data.DataColumn("Address1", typeof(string),
null, System.Data.MappingType.Element);
base.Columns.Add(this.columnAddress1);
this.columnPhone = new System.Data.DataColumn("Phone", typeof(string), null,
System.Data.MappingType.Element);
base.Columns.Add(this.columnPhone);
this.columnBirthDay = new System.Data.DataColumn("BirthDay",
typeof(System.DateTime), null, System.Data.MappingType.Element);
base.Columns.Add(this.columnBirthDay);
this.columnDCCode = new System.Data.DataColumn("DCCode", typeof(int), null,
System.Data.MappingType.Element);
base.Columns.Add(this.columnDCCode);
this.Constraints.Add(new System.Data.UniqueConstraint("Constraint1", new
System.Data.DataColumn[] {
this.columnClientID}, false));
this.columnClientID.Unique = true;
this.columnLastName.MaxLength = 20;
this.columnFirstName.MaxLength = 10;
this.columnMI.MaxLength = 1;
this.columnAddress1.MaxLength = 25;
this.columnPhone.MaxLength = 13;

So for some reason the Wizard has added a constratint to the ClientID field
and that seems to be having a problem. I am not sure what because there are
no duplicate ClientID numbers, the Access DB field is a autonumber field so
there can't be any.

I have verified that using:
SELECT Clients_1.ClientID
FROM ACCESSDC...Clients Clients_1
GROUP BY Clients_1.ClientID
HAVING COUNT(Clients_1.ClientID) > 1

So what could the problem be???