Failed Constraint??

[TURKINATOR]

Hi guys,

I've been having one hell of a night with a particular item. The code is as follows.

    Public Function getClientsDeptCodes(ByVal user As String, ByVal password As String) As DataTable
        Dim dt As New DataTable

        command.CommandText = "SELECT Clients.ClientID, ClientName, DeptCode FROM Clients " & _
        "LEFT JOIN TollDepartmentCodes ON (Clients.ClientID = TollDepartmentCodes.ClientID);"
        command.CommandType = CommandType.Text
        command.Connection = connect.getConnection

        Try
            connect.connectionOpen(user, password, "DB")
            reader = command.ExecuteReader
            dt.Load(reader)
            connect.connectionClose()
        Catch ex As MySql.Data.MySqlClient.MySqlException
            errorsender.messageOut("clients", ex.Number, "getClientsDeptCodes", ex.Message)
            connect.connectionClose()
        End Try

        Return dt
    End Function

Error on: dt.Load(reader)
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

The table is as below

CREATE TABLE IF NOT EXISTS Clients
(
ClientID VARCHAR(4) NOT NULL UNIQUE,
ClientName VARCHAR(40) NOT NULL,

PRIMARY KEY (ClientID)
);

CREATE TABLE IF NOT EXISTS TollDepartmentCodes
(
DeptCode VARCHAR(4) NOT NULL UNIQUE,
ClientID VARCHAR(4) NOT NULL,

PRIMARY KEY (DeptCode)
FOREIGN KEY (ClientID) REFERENCES Clients (ClientID)
);

Item used here.

        cboClient.DataSource = clients.getClients(frmLogin.txtUserName.Text, frmLogin.txtPassword.Text)
        cboClient.DisplayMember = "ClientName"
        cboClient.ValueMember = "ClientID"
        cboClient.SelectedIndex = -1
        cboClient.Text = ""

When I run the query through MySQL command line, the query works fine. But VB.Net does not like it for some reason... Any help would be great. If you need more info I can provide. Thanks.

[TURKINATOR]

OK, I got it working, but I dont understand why I should DROP the PRIMARY KEY (DeptCode). If anyone could explain this for me leased I will know in future. Thanks.

[jmcilhinney]

The error message tells you what the problem is:

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

So, instead of calling Load, populate the DataTable one row at a time and then it will fail on a specific row and you'll know exactly where the issue is.