|
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 - 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 - 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 - 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
|