Avoid Repetition of primary tables's record

Avoid Repetition of primary tables's record

• Julesy

  Omar, are you sure that I was generate the results in the pattern that I want and will not repeat a main category again and again
  
  thanks very much!!!!

  
  
  

• BTee

  If you only want to have a single instance of the primary table then adding the DISTINCT keyword after the first SELECT should work (if im interpreting your request correctly, but i think ive had too much coffee today :))
  
  Try something like:
  SELECT DISTINCT    DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                        '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
  FROM         DirectoryMainCat INNER JOIN
                        DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
  WHERE DirectoryLinks.Hitcount > 0
  ORDER BY DirectoryLinks.HitCount DESC
  
  Im not sure whether this is what you are asking but i hope it helps
  
  
  
  

• NZ_RGB

  hello,omar.
  thanks for your reply for the second time.
  i am sending the code for binding
  binding code is as simple as follows
  -------------------------------------
  this.DataList1.DataSource=ds.Tables[0].DefaultView;
  this.DataList1.DataBind();
  
  -------------------------------------
  

  Html code for the DataList is as follows:
  -------------------------------------
  <asp:datalist id="DataList1" runat="server">
         <HeaderTemplate>
          <asp:Label id="Label4" runat="server" ForeColor="#0000C0" Font-Italic="True">Popular Sites</asp:Label>
         </HeaderTemplate>
         <ItemTemplate>
          <TABLE id="Table2" cellSpacing="0" cellPadding="0" width="100%" border="0">
           <TR>
            <TD>
             <P>
              <asp:Label id=Label1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.CategoryName") %>' Font-Bold="True">
              </asp:Label></P>
             <P>
              <asp:Label id=Label2 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.url") %>'>
              </asp:Label></P>
            </TD>
           </TR>
           <TR>
            <TD>
             <asp:Label id=Label3 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.title") %>'>
             </asp:Label></TD>
           </TR>
           <TR>
            <TD>
             <asp:Label id=Label5 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Description") %>'>
             </asp:Label></TD>
           </TR>
          </TABLE>
         </ItemTemplate>
        </asp:datalist>
  --------------------------------------------
  and I write the query again.
  ------------------------------------------
  SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                        '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
  FROM         DirectoryMainCat INNER JOIN
                        DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
  WHERE DirectoryLinks.Hitcount > 0
  ORDER BY DirectoryLinks.HitCount DESC
  --------------------------------------------
  waiting for your reply.......
  thanks again.

  
  
  

• akortz

  I am still not sure what is your quesion. where is you primary key repetition in the actual Parent (main) table or do you have in your database or  in the data View of the DataList
  
  If its In your database and its .mdb then you have to change your Primary key "index" property to "Yes (No Duplicates)".
  
  If its in DataList then ofcourse it depends on how did you do query on your database and how did you creat relations.
  
  
  
  regards,
  
  

• dummy&amp;#35;1

  hello,omar.
  thanks for your reply for the second time.
  i am sending the code for binding
  binding code is as simple as follows
  -------------------------------------
  this.DataList1.DataSource=ds.Tables[0].DefaultView;
  this.DataList1.DataBind();
  
  -------------------------------------
  

  Html code for the DataList is as follows:
  -------------------------------------
  <asp:datalist id="DataList1" runat="server">
         <HeaderTemplate>
          <asp:Label id="Label4" runat="server" ForeColor="#0000C0" Font-Italic="True">Popular Sites</asp:Label>
         </HeaderTemplate>
         <ItemTemplate>
          <TABLE id="Table2" cellSpacing="0" cellPadding="0" width="100%" border="0">
           <TR>
            <TD>
             <P>
              <asp:Label id=Label1 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.CategoryName") %>' Font-Bold="True">
              </asp:Label></P>
             <P>
              <asp:Label id=Label2 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.url") %>'>
              </asp:Label></P>
            </TD>
           </TR>
           <TR>
            <TD>
             <asp:Label id=Label3 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.title") %>'>
             </asp:Label></TD>
           </TR>
           <TR>
            <TD>
             <asp:Label id=Label5 runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Description") %>'>
             </asp:Label></TD>
           </TR>
          </TABLE>
         </ItemTemplate>
        </asp:datalist>
  --------------------------------------------
  and I write the query again.
  ------------------------------------------
  SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                        '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
  FROM         DirectoryMainCat INNER JOIN
                        DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
  WHERE DirectoryLinks.Hitcount > 0
  ORDER BY DirectoryLinks.HitCount DESC
  --------------------------------------------
  waiting for your reply.......
  thanks again.

  
  
  

• jheske

  Omar Kamal, first of all thanks for your reply.
  There is nothing wroing in my tables.
  i am using SQL Server 2000.
  My main table is 'DirectorymainCat' and Child table is 'DirectoryLinks'
  'CategoryId' is the Primary Key in main table and also being used as foreign key in 'DirectoryLinks' table
  When I run the query
  
  SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                        '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
  FROM         DirectoryMainCat INNER JOIN
                        DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
  WHERE DirectoryLinks.Hitcount > 0
  ORDER BY DirectoryLinks.HitCount DESC
  
  the results that are coming are absolutely right.
  
  Now my problem is how I avoid the repetition of a record that is coming from primary table (DirectoryMainCat) when it has one than one child records in the child table (DirectoryLinks).
  
  Certaily, I have to control it when binding DataList so that if a primary record(primary table's record) has more than one related records, the primary record should display only once and all it's child records should display under it and then the next primary record and it's child records and so on.
  I don't know how to do it.
  please reply soon,
  thanks

  
  
  

• Mike Melzer

  thanks to all who replied to my query.
  but i have not yet got the solution to my problem.
  I have to run this query
  
  SELECT DISTINCT    DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                        '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
  FROM         DirectoryMainCat INNER JOIN
                        DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
  WHERE DirectoryLinks.Hitcount > 0
  ORDER BY DirectoryLinks.HitCount DESC
  
  and the result it produces should be controled through code.
  which should loook like:
  
  Tools & Equipment

  http://www.mobmasti.com
   
  MobMasti
  Mobile Portal
   
  Manufactures

  http://www.manu.com
   
  it's me manufactur
  desc manufactur
   
  http://www.allofuse.com
   
  all stff
  desc

  Rental Companies

  http://www.rentit.com
   
  Rent It
  rent it desc
   
  Buy & Sell

  http://www.mobmasti2.com
   
  MT
  desc

  http://www.itc.com
   
  B and S dec 6
  this is b and s on dec 6

  
  Employment

  http://www.mobmasti.com/
   
  MobMasti
  Mobile Portal
   
   
   
  
  
  not like this:
  Tools & Equipment

  http://www.mobmasti.com
   
  MobMasti
  Mobile Portal
   
  Manufactures

  http://www.manu.com
   
  Manufacturer
  desc manufactur
   
  Rental Companies

  http://www.rentit.com
   
  Rent It
  rent it desc
   
  Buy & Sell

  http://www.mobmasti2.com
   
  MT
  desc
   
  Manufactures

  http://www.allofuse.com
   
  all stff
  desc
   
  Buy & Sell

  http:/
   
  B and S dec 6
  this is b and s on dec 6
   
  Employment

  http://www.mobmasti.com/
   
  MobMasti
  Mobile Portal
   
   
    where text in bold is main category name(it is coming from DirectoryMainCat table).
  and rest of the data(under each main category) is coming from DirectoryLinks table(child table).
  please reply soon!!!
  thanks
  

  
  
  

• lbeguin

  Dear Aamir Iqbal,
  
  Its not all about how you do query but actually u have to see also that how to bind the query with your control.
  
  Binding have two ways one is "Tabuler return binding" and other is "Scalar(single) Value binding". One returns table and the other a single value.  I dont know what control u have for binding ur this qurey and how are u binding it
  
  please, send the code which works as binding for your control. Then we can see where is the actual problem.
  
  Regards,
  
  

• Shawn O

  You Try and then tell me :)
  
  Anyways, I found alote comfort with this new version.
  
  

• Il-Sung Lee - MSFT

  ok,but it will take me time to download the new vs2005. after downloading it, i shall try and then tell you.
  I have another problem that I request you to help me in.
  I have page where I want to show the top 10 'popular web site' based on the hits that websites receive from a web user.
  I have the same tables in this scenario.
  How will i do this this. I am using nested DataLists to do this.
  The code to do this is as below:
  

  -----------------------------
  public DataSet DirectoryPopularSites()

  {

  sqlcon=new SqlConnection();

  sqlcon.ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

  sqlcmd=new SqlCommand();

  sqlcmd.Connection=sqlcon;

  sqlcmd.CommandText="DirectoryFillCategoriesPop";

  sqlcmd.CommandType=CommandType.StoredProcedure;

  sqlcon.Open();

  sqladpt=new SqlDataAdapter();

  sqladpt.SelectCommand=sqlcmd;

  ds=new DataSet();

  sqladpt.Fill(ds,"DirectoryMainCat");

  SqlDataAdapter adpt2=new SqlDataAdapter();

  SqlCommand sqlcmd2=new SqlCommand();

  sqlcmd2.Connection=sqlcon;

  sqlcmd2.CommandText="DirectoryPopularSites";

  sqlcmd2.CommandType=CommandType.StoredProcedure;

  adpt2.SelectCommand=sqlcmd2;

  adpt2.Fill(ds,"DirectoryLinks");

  ds.Relations.Add("Relation1",ds.Tables["DirectoryMainCat"].Columns["CategoryId"],ds.Tables["DirectoryLinks"].Columns["CategoryId"]);

  sqlcmd.Dispose();

  sqlcon.Close();

  return ds;

  }
  
  -----------------------------------------
  the query for 1st stored procedure (DirectoryFillCategoriesPop)is:
  ---------------------------
  
  select top 10  CategoryId,CategoryName,Description from DirectoryMaincat
  where hits>0
  -----------------------
  and for 2nd sp is as below:
  ------------------------
  
  SELECT     DirectoryMainCat.CategoryName, DirectoryMainCat.CategoryId, DirectoryLinks.LinkId, DirectoryLinks.Title, DirectoryLinks.Url,
                        '(Hits: ' + LTRIM(STR(DirectoryLinks.HitCount)) + ')' AS total, DirectoryLinks.Description
  FROM         DirectoryMainCat INNER JOIN
                        DirectoryLinks ON DirectoryMainCat.CategoryId = DirectoryLinks.CategoryId
  WHERE DirectoryLinks.Hitcount > 0
  ORDER BY DirectoryLinks.HitCount DESC
  --------------------------------------
  
  and then I use this code to bind the DataList
  ---------------------------
  

  this.DataList1.DataSource=objD.DirectoryNewSites(Request.QueryString["indate"].ToString());

  this.DataList1.DataBind();
  ---------------------------
  the html code of nested datalists is
  -----------------------------
  <asp:DataList id="DataList1" runat="server" Width="75%" CellPadding="5">
                        <ItemTemplate>
                         <TABLE class="dottedWhiteTable" id="TableList1Contentes" cellSpacing="0" cellPadding="3"
                          width="100%" border="0">
                          <TR>
                           <TD><A class=alink href='DirectoryMain.aspx catId=<%# DataBinder.Eval(Container, "DataItem.CategoryId") %>'>
                             <asp:Label id=lblMainCat runat="server" CssClass="globalfont" Font-Bold="True" Text='<%# DataBinder.Eval(Container, "DataItem.CategoryName") %>'>
                             </asp:Label></A></TD>
                          </TR>
                          <TR>
                           <TD>
                            <asp:DataList id=childList runat="server" CellPadding="5" Width="300px" datasource='<%# ((DataRowView)Container.DataItem).Row.GetChildRows("Relation1") %>'>
                             <ItemTemplate>
                              <TABLE id="TableListItems" cellSpacing="0" cellPadding="0" width="100%" border="0">
                               <TR>
                                <TD>
                                 <a href='DirectoryHitSite.aspx linkid=<%# DataBinder.Eval(Container.DataItem, "[\"linkid\"]") %>&url=<%# DataBinder.Eval(Container.DataItem, "[\"url\"]") %>' target=blank>
                                  <asp:Label id=lblTitle runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"Title\"]") %>'>
                                  </asp:Label></a></TD>
                                <TD></TD>
                               </TR>
                               <TR>
                                <TD>
                                 <asp:Label id=lblDescription runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"Description\"]") %>'>
                                 </asp:Label></TD>
                                <TD></TD>
                               </TR>
                               <TR>
                                <TD>
                                 <asp:Label id=lblUrl runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"url\"]") %>'>
                                 </asp:Label>
                                 <asp:Label id=lblCount runat="server" CssClass="globalFont" Text='<%# DataBinder.Eval(Container.DataItem, "[\"total\"]") %>'>
                                 </asp:Label></TD>
                               </TR>
                               <TR>
                                <td>
                                 <asp:Label id="lblPopular" runat="server" Text="popular" CssClass="globalfont" Font-Bold="True"
                                  ForeColor="#ff6666"></asp:Label></td>
                               </TR>
                              </TABLE>
                             </ItemTemplate>
                            </asp:DataList></TD>
                          </TR>
                         </TABLE>
                        </ItemTemplate>
                       </asp:DataList></TD>
  ---------------------------------
  plesae help me in doing this job
  
  thanks

  
  
  

• schwabbie

  Are you using VWD 2005 or 2003 .NET If u have older Version then I would suggest download VWD Express 2005 (its free) and build your webpage in there. Its alote easier there and many tools works better.
  
  see my code I just generated as a test and it works excelent; 
  
  

  <%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestForm4DataSet.aspx.cs" Inherits="TestForm4DataSet" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Test Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:DataList ID="DataList1" runat="server" DataSourceID="AccessDataSource1"> <ItemTemplate> ID: <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br /> FullTaskName: <asp:Label ID="FullTaskNameLabel" runat="server" Text='<%# Eval("FullTaskName") %>'> </asp:Label><br /> Day: <asp:Label ID="DayLabel" runat="server" Text='<%# Eval("Day") %>'></asp:Label><br /> <br /> </ItemTemplate> </asp:DataList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/TimeTrackerUpdated.mdb" SelectCommand="SELECT Activities.ID, Tasks.FullTaskName, Activities.[Day] FROM (Activities INNER JOIN Tasks ON Activities.Task = Tasks.ID)"> </asp:AccessDataSource> </div> </form> </body> </html>

  
  
  I dont have older version any more on my this Pc. I won't help you with VS 2003 .NET .

  
  
  
• Avoid Repetition of primary tables's record