Getting data from two tables in a repeater

[phoeno29]

Hi, I am new to asp.net but have learnt a lot through the tutorials and random searching here and elsewhere. Have many years expereince with classic ASP but this task I am working on is sending me around the bend ! I want to have a nested repeater (this bit I understand fine) but am unsure how to make my data from two tables in the one database join together. I have a table called boatcats which list a few different categoies of boat and I have a second table called 'boats' that list all of the boats and their details. The two tables share boatcats.ID and boats.catid I doubt my code is heading in the right direction and I am unsure how to link the two queries. I am using VB not C# and should I be doing this sort of thing at code level (which I am used to in asp classic or with Visual Web Developer 2005) Code so far is.... Sub Page_Load(sender as Object, e as EventArgs) If Not Page.IsPostBack then BindData() 'Only bind the data on the first page load End If End Sub Sub BindData() dim dbconn,strq,dbcomm,dbread dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("boats.mdb")) dbconn.Open() strq = "Select * from boatcats" dbcomm=New OleDbCommand(strq,dbconn) dbread=dbcomm.ExecuteReader() boatcats.DataSource=dbread boatcats.DataBind() dbread.Close() dbconn.Close() End Sub Sub BindData2() dim dbconn2,strq2,dbcomm2,dbread2,boats2 dbconn2=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("boats.mdb")) dbconn2.Open() strq2 = "Select * from boats" dbcomm2=New OleDbCommand(strq2,dbconn2) dbread2=dbcomm2.ExecuteReader() boats.DataSource=dbread2 boats.DataBind() dbread2.Close() dbconn2.Close() end sub I havn't added the asp:repeater section, this I have no problems with. Any help appreciated. Martin

[daniel__c]

Depends on what you want to do with the boatcats data i guess, but i will assume this is what you want in the repeater: Boat Category Boat Name Boat Name Boat Category Boat Name ... then you wouldn't need to have a nested repeater, you could do it in one repeater and just do a join in you database query: "Select * from boats join boatcats on boatcats.id on boats.boatcats.id order by boatcats.name, boats.name" (returning all fields from boats and boatcats) and order on the category name then the boat name. OR "Select boats.*, boatcats.title [boatCategoryTitle] from boats join boatcats on boatcats.id on boats.boatcats.id order by boatcats.name, boats.name" (returning all fields from boats and boatcat title) and order on the category name then the boat name. That way you can just put in two placeholders within the and on the placeholder have visible="<%# ShowCategory(Container.DataItem) %>" (for the header placeholder) then visible="<%# !ShowCategory(Container.DataItem) %>" (for the boat section placeholder). Then put a function ShowCategory(object dataItem) which works out whether this is the same category as the last, if not store the category name and show the header. Have another function inside that displays the name of the category. Then display whatever you want in the body section.... Does that make sense?? Is that what you are trying to do??

[phoeno29]

Thanks Daniel for the reply. I did think of the join in the query but brushed past it as I was keen to see what .net had to offer. Ultimately the page is to be searched on, and all boats regardless of matching the criteria are to be displayed. Those that match the criteria are to have an image in colour of the boat and the non search matching boats to be greyed out (A black and white pic of the same boat) I did the whole exercise in under 2 hours in classic asp but am getting no where fast in .net !

[daniel__c]

You could probably use a nested repeater if you wanted to bring the data back in a dataset and provided information about the relationship between the two tables, but if you just need to display like above then this would be the better option. Good luck.