Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Listing Stores Under Certain Areas
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Listing Stores Under Certain Areas
 Post Reply Post Reply Page  123>
Author
  Topic Search Topic Search  Topic Options Topic Options
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Topic: Listing Stores Under Certain Areas
    Posted: 18 October 2004 at 1:32pm
I have a web page where I have stores that are under certain areas. I would like for the area to show up only one time.

For example,

I would like for it to look something like this:

Western Area

SpaStore1
SpaStore2

Eastern Area

SpaStore3

The results on my web page currently look something like:
Western Area

SpaStore1

Western Area

SpaStore2

Eastern Area

SpaStore3


I am not happy with the current results on my web page. I want one area for corresponding records to show up only once. Please look at the below code. Please look at the itemtemplate.


<asp:datalist ID="dtlSpa" runat="server" Width="400">


          <separatortemplate>  <br>


          </separatortemplate>


          <itemtemplate><%# Container.DataItem("AreaName")%>


<p>


             <table border=1 bordercolor="#ccccff" cellpadding=0 cellspacing="0" class="Table" width="400">


               <tr> 
                 <td bgcolor="#ccccff" class="TableHeader"><font face="Arial, Helvetica, sans-serif" color="#FFFFFF" size=4><a href='<%# DataBinder.Eval (Container.DataItem, "SpaStoreID", "SpaStoreInfo.aspx?ID={0}") %>'>


                   <%# Databinder.Eval(Container.DataItem, "SpaStoreName") %>


                   </a></font></td>

               </tr>

               <tr>


                 <td class="TableHeader2"> <p>


                     <%# Databinder.Eval(Container.DataItem, "Address") %>


                     <br>


                     <%# Databinder.Eval(Container.DataItem, "City") %> ,


                     <%# Databinder.Eval(Container.DataItem, "State") %>


                     &nbsp;


                     <%# Databinder.Eval(Container.DataItem, "Zip") %>


                     <br>


                     Phone:                       <%# Databinder.Eval(Container.DataItem, "Phone") %>


                     <br>


<%# IIF(FieldValueIsMissing(Databinder.Eval(Container.DataItem, "TollPhone")), "", "Toll Free Phone:&nbsp;" & Databinder.Eval(Container.DataItem, "TollPhone"))%>

<p>

<%# IIF(FieldValueIsMissing(Databinder.Eval(Container.DataItem, "WebSite")), "", "<a href='" & DataBinder.Eval (Container.DataItem, "WebSite") & "'>Go To Web Site</a>")%>


        <p> <%# IIF(Databinder.Eval(Container.DataItem, "ShowEmail")= True, "", "<a href='mailto:" & DataBinder.Eval (Container.DataItem, "Email") & "'>Email</a>")%>





                   <p> <a href='<%# DataBinder.Eval (Container.DataItem, "SpaStoreID", "SpaStoreInfo.aspx?ID={0}") %>'>


                     Get More Information About


                     <%# Databinder.Eval(Container.DataItem, "SpaStoreName") %>


                     </a>


                   <p> </td>


               </tr>


             </table>


          </itemtemplate>


        </asp:datalist>


      </form></td>


    <td></td>


  </tr>


  <tr>


    <td height="18"></td>


    <td></td>


  </tr>


</table>





Edited by Misty
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post Posted: 18 October 2004 at 1:41pm
Do a google for nested datalists.
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Posted: 18 October 2004 at 3:36pm

I found a good example of a nested datalist at http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=n est. I experimented with the code by just having two tables (Area and City). It worked fine.

However, it is complicated because I have 2 tables that are joined together. I was wondering if someone could please help me to figure out how to do my current SQL Statement with http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=n est.

My current SQL Statement is:

              'Start SQL statement
              strSQL = "Select * From SpaStore, Area"
              strSQL = strSQL & " where SpaStore.Area = Area.AreaID"
               strSQL = strSQL & " and Area.State = '" & strChosenState & "'"
              strSQL= strSQL & " and ListingType IN ('Featured','National')"
              strSQL = strSQL & " and Approve=0"
    strSQL = strSQL & " Order By SpaStoreName"

I need to be able to keep the above sql statement. I know that the code will have to be modified to work.

 



Edited by Misty
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 19 October 2004 at 10:29am
Without know all your field names it will be difficult. Basically you need one select where you get your areaas, do a join on the area for the relation but before another select with all detail info...
Blog | MPG Tracker
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Posted: 19 October 2004 at 12:53pm

Michael,

Let me give you the code that I have. I still want to use the following sql statement:         strSQL = "Select * From SpaStore, Area"
              strSQL = strSQL & " where SpaStore.Area = Area.AreaID"
               strSQL = strSQL & " and Area.State = '" & strChosenState & "'"
              strSQL= strSQL & " and ListingType IN ('Featured','National')"
              strSQL = strSQL & " and Approve=0"
    strSQL = strSQL & " Order By SpaStoreName"

 

Here's my code:

Sub Page_Load(Source as Object, E as EventArgs)
Dim strConn as string = My connection details

Dim strChosenState as String
 
 'Get incoming querystring values
    strChosenState = request.params("State")
 
 Dim MySQL as string = "Select * from Area where State = '" & strChosenState & "'" - this would work when I just had select * from Area" But I must have this and everything else in the sql statement that I have to get the results that I need. I got the following error message:

This constraint cannot be enabled as not all values have corresponding parent values when I added that particular where statement. 


 Dim MyConn as New SQLConnection(strConn)
 Dim ds as DataSet=New DataSet()
 
             

 Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
 Cmd.Fill(ds,"Area")

    Dim cmd2 As SqlDataAdapter = New SqlDataAdapter("select * from SpaStore where ListingType IN ('Featured','National') and Approve=0 Order By SpaStoreName", MyConn)
    cmd2.Fill(ds, "SpaStore") - I tested this. It worked when I took the where statement away from the first sql statement above.

    ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("Area"))

 dlArea.Datasource=ds.Tables("Area").DefaultView
 DataBind()
End Sub

</script>
</head>
<body>
<asp:DataList runat="server"
 Id="dlArea">
 
 <HeaderTemplate>
  
 </HeaderTemplate>
 <ItemTemplate><table><tr><td>& amp; amp; amp; lt;font color="red" font size=4><%# Container.DataItem("AreaName")%>
   </font></td></tr></table> ; ; ; ; <p>
   <asp:DataList runat="server"
    Id="ChildDataList"
    GridLines="None"
    Bordercolor="black"
    cellpadding="3"
    cellspacing="0"
    Headerstyle-BackColor="#8080C0"
    Headerstyle-Font-Name="Arial"
    Headerstyle-Font-Size="8"
    Font-Name="Arial"
    Font-Size="8"
    datasource='<%# Container.DataItem.Row.GetChildRows("myrelation") %>'
    RepeatColumns="3">
    <ItemTemplate><p><p&a mp;a mp;a mp;g t;<table>
    <tr><td> <%# Container.DataItem("SpaStoreName") %></td></tr>
    <tr><td><%# Container.DataItem("Address") %></td></tr>
                 <tr><td><%# Container.DataItem("City") %>, <%# Container.DataItem("State")%> <%# Container.DataItem("Zip") %> </td></tr>
    <p></p>
    </ItemTemplate>
   </ASP:DataList>
 </ItemTemplate>
</ASP:DataList>
</table>
</body>
</html>



Edited by Misty
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 19 October 2004 at 2:05pm

Your SQL Query will look slightly different but as long as you have the same results that should not matter or not? You will need to sql queries one to get the Areas and the other to get the detail.

One SQL would be something like:
mySQL = "Select * from SpaStore INNER JOIN Area ON SpaStore.AreaID = Area.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 0 ORDER by SpaStore.SpaStoreName"

Then the other would be something like

mySQL2 = "Select * from Area where State = '" & strChosenState "'"

Then you just set the relation like

ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"))

 

Done it like that countless times and always worked.

 
Blog | MPG Tracker
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Posted: 19 October 2004 at 2:32pm

Michael,

Thank you very much for helping me with the sql statements!

There is another thing I need to work on in this web page. I don't want areas that have no records under them to show up. I am not sure how to do this in ASP.Net.

For example,

the web page may look something like:

Denver Area

Store1

Western Colorado

I don't want Western Colorado to show up unless there is at least one store that is in the area. My code is pretty much the same. The SQL Statements have changed. Does anyone know how I might be able to accomplish this?

Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 19 October 2004 at 4:12pm

Create the same join in the 2nd sql query as you have in the first query, joining them left so it will only return results where there is a spa and not NULL areas.
Blog | MPG Tracker
Back to Top
 Post Reply Post Reply Page  123>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.