none
These columns don't currently have unique values. Error with Junction Table in Dataset RRS feed

  • Question

  • I have a many to many relationship that I broke into one to many with a junction table:

    PerfMeasures(PMID, Title, Details)
    JunctPM_Analyst(PMID, AnalystID)   -->This table uses a composite key as primary key
    Analyst(AnalystID, FirstName, LastName, Phone)

    Now, I am trying to build a datset for a nested repeater that will associate eat PerfMeasure with an Analyst with the following code:

            protected void Page_Load(object sender, EventArgs e)
    
            {
    
                conn.Open();
    
    
    
                SqlDataAdapter adpPopulate;
    
    
    
                DataSet dsPrintData = new DataSet("PrintData");
    
    
    
                DataTable dtPerfMeasures = dsPrintData.Tables.Add("PerfMeasures");
    
                DataTable dtAnalyst = dsPrintData.Tables.Add("Analyst");
    
                DataTable dtJunctPM_Analyst = dsPrintData.Tables.Add("JunctPM_Analyst");
    
                            
    
                //Fill dtPerfMeasures
    
                adpPopulate = new SqlDataAdapter("SELECT PMID as PMID, PMorder, WorkloadType, PerfWorkload, PerfWorkloadURL, Indicator, Target, TargetDetails, Definition, DataSource, Tools, Strategies, FO_ObtainSSN, RO_ObtainCounts " +
    
                                                 "FROM PerfMeasures", conn);
    
                adpPopulate.Fill(dsPrintData, "dtPerfMeasures");
    
                adpPopulate.Dispose();
    
    
    
                //Fill dtAnalyst
    
                adpPopulate = new SqlDataAdapter("SELECT AnalystID as AnalystID, Area + ' ' + FirstName + ' ' + LastName + ' ' + Component + ',  ' + Phone AS AnalystInfo FROM Analyst", conn);
    
                adpPopulate.Fill(dsPrintData, "dtAnalyst");
    
                adpPopulate.Dispose();
    
    
    
                //Fill dtJunctPM_Analyst
    
                adpPopulate = new SqlDataAdapter("SELECT PMID, AnalystID FROM JunctPM_Analyst", conn);
    
                adpPopulate.Fill(dsPrintData, "dtJunctPM_Analyst");
    
                adpPopulate.Dispose();
    
    
    
                DataColumn [ ] printKey = new DataColumn[2];
    
                printKey[0] = dsPrintData.Tables["JunctPM_Analyst"].Columns["PMID"];
    
                printKey[1] = dsPrintData.Tables["JunctPM_Analyst"].Columns["AnalystID"];
    
                dsPrintData.Tables["dtJunctPM_Analyst"].PrimaryKey = printKey;
    
    
    
                dsPrintData.Relations.Add("Junct_Perf", dsPrintData.Tables["dtJunctPM_Analyst"].Columns["PMID"], dsPrintData.Tables["dtPerfMeasures"].Columns["PMID"]);
    
                dsPrintData.Relations[0].Nested = true;
    
    
    
                dsPrintData.Relations.Add("Junct_Analyst", dsPrintData.Tables["dtJunctPM_Analyst"].Columns["AnalystID"], dsPrintData.Tables["dtAnalyst"].Columns["AnalystID"]);
    
                dsPrintData.Relations[1].Nested = true;
    
    
    
                rptPrintAll.DataSource = dsPrintData.Tables["dtPerfMeasures"];
    
                rptPrintAll.DataBind();
    
    
    
                conn.Close();
    
            }
    
    

    The JunctPM_Analyst table has duplicate values because each the two columns in that table serve as a composite key, so, yes, there are duplicate values, but this shouldn't be a problem because these two columns together make the key.

    How can I set my primary key to recognize this so I don't get this error anymore?  Examples and help is greatly appreciated; I've been struggling with this and am not very experienced with dataset and creating relations with them.

    Thank you in advance for the help!
    • Edited by OchaOcha Wednesday, July 29, 2009 5:45 PM
    Wednesday, July 29, 2009 5:45 PM

Answers

  • Thank you for the help.  I figured out the problem: the tables were being referenced wrong.  I had to go from one table to my junction table then from that to another.  Took a while, but got it sorted out.

    Thank you, though.  I appreciate the pointers.
    Sunday, August 16, 2009 5:25 AM

All replies

  • Anyone have any ideas? Surely there must be some good examples out there somewhere.
    Thursday, July 30, 2009 1:52 PM
  • What I would do is first understand the schema that is automatically filled into the DataTables. Also when you create the new relations are you also creating the constraints as well? Perhaps write out the schema using WriteXml with the option to includeSchema. Then look to see how many tables there are and if this matches up to your database design in the first place.

    Another thing I see is when you call fill you pass in the dataSet and the table name. But these table names don't match up to the table names that you have created.

    What I would do is explicity create all the tables, columns in those tables DataRelations and Constraints. Then I would call fill.

    Let me know if any of this helps.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, July 31, 2009 12:49 AM
  • Thank you for the help.  I figured out the problem: the tables were being referenced wrong.  I had to go from one table to my junction table then from that to another.  Took a while, but got it sorted out.

    Thank you, though.  I appreciate the pointers.
    Sunday, August 16, 2009 5:25 AM