Web Wiz - Solar Powered Eco Hosting

  New Posts New Posts RSS Feed - Needs Help With Nested DataList
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Needs Help With Nested DataList

 Post Reply Post Reply
Author
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: Needs Help With Nested DataList
    Posted: 29 November 2005 at 1:32am

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

Back to Top
thataintchicken View Drop Down
Groupie
Groupie
Avatar

Joined: 19 January 2006
Location: United States
Status: Offline
Points: 49
Post Options Post Options   Thanks (0) Thanks(0)   Quote thataintchicken Quote  Post ReplyReply Direct Link To This Post Posted: 05 February 2006 at 12:06am
Check out http://www.datagridgirl.com; they have all kinds of information about datagrids.

After a little research, I found a walkthough on datagrid creation here: http://www.123aspx.com/redir.aspx?res=27973

Hope this helps =)
"I not only use all of the brains I have, but all I can borrow."
    --Woodrow Wilson, 28th President of the United States
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.01
Copyright ©2001-2018 Web Wiz Ltd.


Become a Fan on Facebook Follow us on Twitter Connect with us on LinkedIn Community Forums Web Wiz Blogs Web Wiz News
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Policy

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 unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

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