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>
Topic Options
Post Options
Thanks(0)
