[RESOLVED] Master/Detail Relation problems

[Beast777]

I am trying to figure out bindingsources with master detail relationships. I have a database with a table of personnel. I have another database on the same server with claims by those personnel. I create a dataset and add both tables and then add a datarelation. I have tried it two different ways (one is commented out) and both give me the same error: This constraint cannot be enabled as not all values have corresponding parent values.

The form has 2 dataviews on it. Here is the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication2
{
    public partial class frm1 : Form
    {
        //private DataGridView masterDataGridView = new DataGridView();
        private BindingSource masterBindingSource = new BindingSource();
        //private DataGridView detailsDataGridView = new DataGridView();
        private BindingSource detailsBindingSource = new BindingSource();

        public frm1()
        {
            InitializeComponent();
        }

        private void frm1_Load(object sender, EventArgs e)
        {
            // Bind the DataGridView controls to the BindingSource
            // components and load the data from the database.
            masterDataGridView.DataSource = masterBindingSource;
            detailsDataGridView.DataSource = detailsBindingSource;
            GetData();

            // Resize the master DataGridView columns to fit the newly loaded data.
            masterDataGridView.AutoResizeColumns();

            // Configure the details DataGridView so that its columns automatically
            // adjust their widths when the data changes.
            detailsDataGridView.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;
        }
        
        private void GetData()
        {
            try
            {
                // Connection strings. 
               
                String connectionString =
                    "Data Source=SCE-SCE-DSVR02D;Initial Catalog=TrainingManagementDB;Integrated Security=True";
                String connectionString2 =
                    "Data Source=SCE-SCE-DSVR02D;Initial Catalog=DocCtrl;Integrated Security=True";
        
                SqlConnection connection = new SqlConnection(connectionString);
                SqlConnection connection2 = new SqlConnection(connectionString2);
                // Create a DataSet.
                DataSet data = new DataSet();
                data.Locale = System.Globalization.CultureInfo.InvariantCulture;

                // Add data from the Pers table to the DataSet.
                SqlDataAdapter masterDataAdapter = new
                    SqlDataAdapter("SELECT Last_Name, Service_Number, First_Name, Initials, Rank, Status, COS_In, MOC, Squadron, Gender, Element FROM PersData ORDER BY Last_Name ", connection);
                masterDataAdapter.Fill(data, "PersData");

                // Add data from the Claims table to the DataSet.
                SqlDataAdapter detailsDataAdapter = new
                    SqlDataAdapter("select * from Claims", connection2);
                detailsDataAdapter.Fill(data, "Claims");

                // Establish a relationship between the two tables.
                DataRelation relation = new DataRelation("CustomersOrders",
                    data.Tables["PersData"].Columns["Service_Number"],
                    data.Tables["Claims"].Columns["SN"]);
                data.Relations.Add(relation);


                //DataColumn primarykey = data.Tables["PersData"].Columns["Service_Number"];
                //DataColumn foreignkey = data.Tables["Claims"].Columns["SN"];
                //DataRelation relation = data.Relations.Add(primarykey, foreignkey);

                // Bind the master data connector to the Persdata table.
                masterBindingSource.DataSource = data;
                masterBindingSource.DataMember = "PersData";

                // Bind the details data connector to the master data connector,
                // using the DataRelation name to filter the information in the 
                // details table based on the current row in the master table. 
                detailsBindingSource.DataSource = masterBindingSource;
                detailsBindingSource.DataMember = "CustomersOrders";
            }
            catch (SqlException)
            {
                MessageBox.Show("To run this example, replace the value of the " +
                    "connectionString variable with a connection string that is " +
                    "valid for your system.");
            }
        }

    }
}

[BillBoeBaggins]

My first thought is either A)your data types are not compatible or b)you have nulls in one side of your link either SN or Service_Number contains a null.

[Beast777]

Thats what I thought too, I checked and they are both 11 nvarchar (no nulls).

[BillBoeBaggins]

http://www.thescripts.com/forum/thread315369.html

[Beast777]

I just copied the Claims table into the other database and ran this query and got no returns

Select SN from Claims where (NOT EXISTS(Select Service_Number from Persdata))

[jmcilhinney]

The obvious answer is that the child table contains records with no corresponding parent record in the other table. How would you go about finding such a record? Wouldn't you just loop through the child DataTable and then look for a corresponding record in the parent DataTable? Just don't add the DataRelation and you can fill the tables and then look.

[Beast777]

Thats what I thought and why I wrote the select above. It returned no records so, I don;t think there is any extra records. Any other ideas?

[Beast777]

Fixed it. there was bad records but I needed to revise my sql to this:

select * from Claims where not exists (Select * from Persdata where Persdata.Service_Number = Claims.SN)

[BillBoeBaggins]

Cool. I like happy endings.