Listing Stores Under Certain Areas
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: ASP.NET Discussion
Forum Description: Discussion and chat on ASP.NET related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=12222
Printed Date: 29 March 2026 at 4:24am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Listing Stores Under Certain Areas
Posted By: Misty
Subject: Listing Stores Under Certain Areas
Date 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") %>
<%# Databinder.Eval(Container.DataItem, "Zip") %>
<br>
Phone: <%# Databinder.Eval(Container.DataItem, "Phone") %>
<br>
<%# IIF(FieldValueIsMissing(Databinder.Eval(Container.DataItem, "TollPhone")), "", "Toll Free Phone: " & 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>
|
Replies:
Posted By: Mart
Date Posted: 18 October 2004 at 1:41pm
|
Do a google for nested datalists.
|
Posted By: Misty
Date 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=nest - 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=nest - 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.
|
Posted By: michael
Date 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...
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date 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>
|
Posted By: michael
Date 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.
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date 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?
|
Posted By: michael
Date 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.
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date Posted: 19 October 2004 at 6:08pm
|
Michael,
I think I am almost there. But I am getting the following error message:
These columns don't currently have unique values.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: These columns don't currently have unique values.
Source Error:
Line 25: cmd2.Fill(ds, "Area")
Line 26:
Line 27: ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"))
Line 28:
Line 29: dlArea.Datasource=ds.Tables("Area").DefaultView |
Here's my second sql statement:
Dim mySQL2 as string = "Select * from Area INNER JOIN SpaStore ON Area.AreaID = SpaStore.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 0 ORDER by AreaName"
Dim cmd2 As SqlDataAdapter = New SqlDataAdapter(MySQL2,MyConn)
cmd2.Fill(ds, "Area")
I am puzzled why it won't work. It will work when I just have mySQL2 = "Select * from Area where State = '" & strChosenState "'", it will work. But I get all of the areas for that states even if there are no spa stores under that area. Do you have any idea on how I can fix this problem? Thank You!
|
Posted By: michael
Date Posted: 19 October 2004 at 6:19pm
First off, don't do a Select *, that is bad practice, just select the fields for table area you need...
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date Posted: 19 October 2004 at 7:07pm
|
When I selected AreaName from the above sql statement, it said something about not being able to accept null values. I will only get this error message if there is more than one store under one area. The error message says: These columns don't currently have unique values. This is puzzling. It still would not work when I remove the primary key from the AreaID in Area table. I must have a typo. It could be because the Area table is the master table. Does anyone have any ideas on how to fix this problem?
Look at the below code that is causing the problem:
Dim mySQL2 as string = "Select * from Area where Area.State = '" & strChosenState & "' ORDER by AreaName"
Dim cmd2 As SqlDataAdapter = New SqlDataAdapter(MySQL2,MyConn)
cmd2.Fill(ds, "Area")
ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"))
|
Posted By: michael
Date Posted: 19 October 2004 at 11:09pm
|
OK, I did not look through all the source we have been playing around with.
The below pseudo code works
Dim MySQL As String = "Select AreaID, Area from Area where State = '" & strChosenState & "'"
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 Join Area ON SpaStore.AreaID = Area.AreaID where State = '" & strChosenState & "'", MyConn)
cmd2.Fill(ds, "SpaStore")
ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"), True)
dlArea.DataSource = ds.Tables("Area").DefaultView
DataBind()
|
The problem you had that the parent row does not have corresponding parent values is because you chose SpaStore that are in the strChosenAreaID but you chose ALL Areas, that will cause a conflict, you need to set the same filter on the spaStore table. I put above code in a page and verified it does work well.
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: michael
Date Posted: 19 October 2004 at 11:10pm
Forgot to mention, you changed a few column names like AreaName to Area or so. Make sure you reflect that...
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date Posted: 20 October 2004 at 1:07am
|
AreaName is actually right. I accidentally called the AreaID in the SpaStore Table Area at one time which was not a good idea.
Let me tell you some information about my tables that are relevant:
Area: AreaID, AreaName, State
SpaStore: SpaStoreID, SpaStoreName, AreaID, State, Approve, ListingType, etc.
I used the above code that you gave me. I had to modify it a little because State in the second SQL Statement had to be SpaStore.State. I didn't get any error messages. But I am getting all of the areas even if there are no stores under a certain area. I only want areas that have stores under them to be displayed. I've tried some experiments by changing some of the code, but I have been unsuccessful.
Here's my code:
Dim MySQL As String = "Select AreaID, AreaName from Area where State = '" & strChosenState & "' Order By AreaName" - I think that we need to modify this sql statement. This sql statement chooses all of the areas that are under a certain state. For example, (GA) Georgia might have the following areas: Atlanta Area, Middle Georgia, South Georgia, and Northeast Georgia. All of the areas would be displayed with this sql statement. Let's say that I only have one store under North Georgia and three stores under Atlanta Area. I would like for only North Georgia and Atlanta Area to be displayed as areas on the web page.
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 Join Area ON SpaStore.AreaID = Area.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 0 ORDER by SpaStoreName", MyConn)
cmd2.Fill(ds, "SpaStore")
ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"), True)
dlArea.DataSource = ds.Tables("Area").DefaultView
DataBind()
|
Posted By: michael
Date Posted: 20 October 2004 at 9:49am
To only get the Areas that have SpaStores you need to do a join on the SpaStore Table like I posted some time ago, I had just simplified it above.
Dim MySQL As String = "Select Area.AreaID, Area.Area from Area Join SpaStore ON Area.AreaID = SpaStore.AreaID where Area.State = '" & strChosenState & "'"
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date Posted: 20 October 2004 at 2:43pm
I did what a join on the Area table. I tried this before. I tried the code that you posted again. It didn't work. I got the following error message: Exception Details: System.ArgumentException: These columns don't currently have unique values.
Source Error:
Line 43: cmd2.Fill(ds, "Area")
Line 44:
Line 45: ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"))
Line 46:
Line 47: dlArea.Datasource=ds.Tables("Area").DefaultView | again. There may be a typo somewhere that we both didn't notice. Are there any other ideas on how to work around this problem?
|
Posted By: michael
Date Posted: 20 October 2004 at 2:51pm
Sorry man but it almost appears your db has a problem, if you set ds.Tables("Area") as your parent Relation and get an error that the columns currently don't have unique values, it means there is AreaID more then once. Or you did a Select * again which may pull the wrong Area in duplicates. Why don't you just post the code you have at this point. I mean I did that 1000 times and always worked so I am sure it is something very simple we are overlooking. If you could also write out both datasets into an xml file with ds.WriteXML("c:...") so I have your data to play with. I will PM you my email addy if you want to mail the xml to me
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: Misty
Date Posted: 02 November 2004 at 3:19pm
|
I finally solved this problem by changing this sql statement to:
Dim mySQL2 as string = "Select Area.AreaID, AreaName from Area Inner Join SpaStore ON Area.AreaID = SpaStore.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 1 Group By Area.AreaID, AreaName Order by AreaName"
I had to group by Area.AreaID for it to work.
Here's my code in case anyone needs to create a complicated nested datalist like this in the future:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<%@ Page Language="VB" Debug="true" %>
<html>
<head>
<title>Nested DataLists</title>
<script language="VB" runat="server">
'----------------------------------------------------------- ------
' name: FieldValueIsMissing(objValueToCheck As Object) As Boolean
'----------------------------------------------------------- ------
Function FieldValueIsMissing(objValueToCheck As Object) As Boolean
if objValueToCheck is DBNull.Value Then
return True
else
return False
end if
End Function
Sub Page_Load(Source as Object, E as EventArgs)
Dim strConn as string = "server=servername;uid=username;pwd=password;database=databa sename"
Dim strChosenState as String
'Get incoming querystring values
strChosenState = request.params("State")
Dim MySQL as string = "Select * from SpaStore INNER JOIN Area ON SpaStore.AreaID = Area.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 1 ORDER by SpaStoreName"
Dim MyConn as New SQLConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"SpaStore")
Dim mySQL2 as string = "Select Area.AreaID, AreaName from Area Inner Join SpaStore ON Area.AreaID = SpaStore.AreaID where Area.State = '" & strChosenState & "' AND ListingType IN ('Featured','National') AND Approve = 1 Group By Area.AreaID, AreaName Order by AreaName"
Dim cmd2 As SqlDataAdapter = New SqlDataAdapter(MySQL2,MyConn)
cmd2.Fill(ds, "Area")
ds.Relations.Add("myrelation", ds.Tables("Area").Columns("AreaID"), ds.Tables("SpaStore").Columns("AreaID"))
dlArea.Datasource=ds.Tables("Area").DefaultView
DataBind()
End Sub
</script>
</head>
<body>
<asp:DataList runat="server"
Id="dlArea">
<HeaderTemplate>
</HeaderTemplate>
<ItemTemplate><table><tr><td>& 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&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>
|
|