none
Help needed from SQL guru RRS feed

  • Question

  •  

      I have three tables - 1. Entries table [EntryID, CatID, Entrydetails] are the fields in it. No duplicates in entryid and catid

    2. Category table [CatID, Name] are the fields in it. No duplicates allowed

    3. Attributes table [AttributeID, CatID, Attname] are the fields in it. 1 category can have multiple attributes so duplicates are allowed on catid

     

     

    Now I want nested results in xml so that I can group Entries like this :

     

     Name (from Category table)

     Attname (from Attributes table)

     EntryID and Entrydetails (from Entries table)

     

     

    So multiple entries within multiple Attnames within single Category Name

     

    I want to know how to make nested relation between these three tables. I wrote this code but I am getting 'table doesn't have unique entries' error

     

    public class SqlConnect
        {
            public DataSet ReturnDataSet(String dbConnectString, string table1, string table2, string table3) {
                DataSet dsTables = new DataSet();
                SqlConnection dataConn = new SqlConnection(dbConnectString);
                SqlDataAdapter ConfigAdapter = new SqlDataAdapter(table1, dataConn);
                SqlDataAdapter EntryAdapter = new SqlDataAdapter(table2, dataConn);
                SqlDataAdapter AttributeAdapter = new SqlDataAdapter(table3, dataConn);
               
                ConfigAdapter.Fill(dsTables, "Category");
                EntryAdapter.Fill(dsTables, "Entries");
                AttributeAdapter.Fill(dsTables, "Attributes");
               
                DataRelation rel = new DataRelation("CatEntry", dsTables.Tables["Entries"].Columns["CatID"],
                dsTables.Tables["Entries"].Columns["CatID"]);

                DataRelation rel1 = new DataRelation("CatEntry1", dsTables.Tables["Entries"].Columns["CatID"],
                dsTables.Tables["Attributes"].Columns["CatID"]);
               
                rel.Nested = true;
                dsTables.Relations.Add(rel1);
               
                return(dsTables);
            }
        }

        public void Page_Load(Object Src, EventArgs E)
        {
            string SqlConnStr = "Server=test;uid=test;pwd=test;" + "database=test";
            string table1 = @"SELECT top 100 CATID, NAME FROM Category WHERE CatID = '12' ";
            string table2 = "SELECT top 100 CATID, ENTRYID, Entrydetails FROM Entries WHERE CatID = '12'";
            string table3 = "SELECT top 100 CATID, Attname FROM Attributes WHERE CatID = '12'";
            SqlConnect Sqlconn = new SqlConnect();
            DataSet ds = Sqlconn.ReturnDataSet(SqlConnStr, table1, table2, table3);
           
            DataSet ds1 = Sqlconn.ReturnDataSet(SqlConnStr, table1, table2, table3);
            XmlDataDocument inputDoc = new XmlDataDocument(ds);
           
            XslCompiledTransform xsltProcessor = new XslCompiledTransform();
                  
            
            xsltProcessor.Load("StyleSheet.xsl");
            StringWriter stringWriter = new StringWriter();
            xsltProcessor.Transform(inputDoc, null, stringWriter);
           
            content.InnerHtml = stringWriter.ToString();
            xml.Text = ds.GetXml();
            }


    I really need immediate help on this.

     

    Thanks in advance !

    Wednesday, September 19, 2007 8:48 AM

All replies

  • I think this might be a problem

     

    Code Snippet

    DataRelation rel = new DataRelation(

    "CatEntry",

    dsTables.Tables["Entries"].Columns["CatID"],
    dsTables.Tables["Entries"].Columns["CatID"]);

     

    DataRelation rel1 = new DataRelation(

    "CatEntry1",

    dsTables.Tables["Entries"].Columns["CatID"],
    dsTables.Tables["Attributes"].Columns["CatID"]);

     

     

    I don't think you want to join the entries table to itself or to the attribues table.  Did you want something more like this?

     

    Code Snippet

    DataRelation rel = new DataRelation(

    "CatEntry",

    dsTables.Tables["Category"].Columns["CatID"],
    dsTables.Tables["Entries"].Columns["CatID"]);

     

    DataRelation rel1 = new DataRelation(

    "CatEntry1",

    dsTables.Tables["Category"].Columns["CatID"],
    dsTables.Tables["Attributes"].Columns["CatID"]);

     

     

    Wednesday, September 19, 2007 3:29 PM
  • Thanks alot!!

     

    That worked but the nesting is not done properly. How do I do nesting ? Rightnow I am doing like :

     

    rel.Nested = true;
               // rel1.Nested = true;
                dsTables.Relations.Add(rel);
                dsTables.Relations.Add(rel1);

     

    but it puts entries under Category and attributes are inserted separately under Category. I want Category>Multiple Attributes>Relevant entries in Attributes

     

    Thanks in advance

    Wednesday, September 19, 2007 5:48 PM