Needs Help With Nested DataList

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I am having a problem with a nested data list that has several different categories. Please see http://www.explorencmountains.com/County.aspx?CountyID=4. This web page won't show the listings becuase there are two listings under the Real Estate category. I never get this error message when there's just one listing under a category. For example, see http://www.explorencmountains.com/County.aspx?CountyID=6. 
I found out that it's not the advertiserID that has a problem because when I deleted one of the listings that was under the same subcategory (subcategoryid), it worked fine. 

Here's the error message that I get when there are two or more listings under a category: Exception Details: System.ArgumentException: These columns don't currently have unique values.

Source Error: 

Line 310:
Line 311:
Line 312:   	ds.Relations.Add("myrelation", ds.Tables("AdvertiserSubCategory").Columns("SubCategoryID"), ds.Tables("SubCategories").Columns("SubCategoryID"))
I've tried to figure out what's wrong. I am puzzled. Here's my code for the nested datalist that's significiant:

'--------------------------------------------- 
' name: BindRealEstateList() 
'--------------------------------------------- 
Sub BindRealestateList() 
    Dim strConnect as string 
 Dim MyConn As New SQLConnection
 Dim MySQL As String 
 Dim MySQL2 As String 
 Dim strCountyID As String

   'Get incoming querystring values 
             strCountyID = request.params("CountyID")

    mySQL = "Select * From Advertiser, AdvertiserSubCategory, SubCategories, County, City" 
 mySQL = mySQL & " where Advertiser.AdvertiserID = AdvertiserSubCategory.AdvertiserID"
 mySQL = mySQL & " and AdvertiserSubCategory.SubCategoryID = SubCategories.SubCategoryID"
 mySQL = mySQL & " and Advertiser.CountyID=County.CountyID"
    mySQL = mySQL & " and Advertiser.CityID = City.CityID"
    mySQL = mySQL & " and Advertiser.CountyID = '" & strCountyID & "'"
    mySQL = mySQL & " and Advertiser.CategoryID = 8"  
 mySQL = mySQL & " and Approve =1"
 mySQL = mySQL & " Order By ListingType, BusinessName"

 'Get connection string from Web.Config
    strConnect = ConfigurationSettings.AppSettings("ConnectionString")
    MyConn = New SQLConnection(strConnect)

 Dim ds as DataSet=New DataSet()

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

    mySQL2 = "Select SubCategories.SubCategoryID, SubCategory From SubCategories, AdvertiserSubCategory, Advertiser, City, County" 
    mySQL2 = mySQL2 & " where Advertiser.AdvertiserID = AdvertiserSubCategory.AdvertiserID"
    mySQL2 = mySQL2 & " and SubCategories.SubCategoryID = AdvertiserSubCategory.SubCategoryID"  
    mySQL2 = mySQL2 & " and Advertiser.CityID = City.CityID"
    mySQL2 = mySQL2 & " and Advertiser.CountyID=County.CountyID"
    mySQL2 = mySQL2 & " and Advertiser.CountyID = '" & strCountyID & "'"
 mySQL2 = mySQL2 & " and Advertiser.CategoryID = 8"
 mySQL2 = mySQL2 & " and Approve =1"
 mySQL2 = mySQL2 & " Group By SubCategories.SubCategoryID, SubCategory, ListingType"
 mySQL2 = mySQL2 & " Order By SubCategory,ListingType "
    Dim cmd2 As SqlDataAdapter = New SqlDataAdapter(MySQL2,MyConn)
    cmd2.Fill(ds, "SubCategories")

    ds.Relations.Add("myrelation", ds.Tables("AdvertiserSubCategory").Columns("SubCategoryID"), ds.Tables("SubCategories").Columns("SubCategoryID"))

 dlRealEstate.Datasource=ds.Tables("AdvertiserSubCategory").DefaultView
 DataBind()

End Sub

Please take a look at http://www.spastoredirectory.com/state.aspx?state=ga. There are two listings under Northeastern Georgia on this web page. But there are not that many tables that had to be joined. Here's the code for this nested datalist:

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