Repeater dilema

[veronica86]

I want to user a repeater to display the results in my dataset. I am joining 2 tables. ex)

table 1
******
NAME CITY
Fred ElPaso


table 2
******
NAME LANGUAGES_SPOKEN
Fred German
Fred Latin
Fred English

So my dataset looks like this after joining the 2 tables on NAME:

Fred ElPaso German
Fred ElPaso Latin
Fred ElPaso English

I want my repeater to display the results like this:

Fred ElPaso

German
Latin
English

I am binding labels to the results, but I can't figure out how to display only the first row in the dataset, so that I only display the NAME and CITY once!

Any ideas would be greatly appreciated! Thank you

 

[kulrom]

Use SQL DISTINCT function for the purpose ... it will make to not duplicate the records with same value .... Cheers

 

[veronica86]

Thanks for the reply. I don't believe Distinct is the answer because I'm doing a join with 2 tables. All the rows returned in the dataset will be unique because of the 'LANGUAGES_SPOKEN' column. Back to the drawing board

 

[kulrom]

If that is connected with datagrid sorry i can't be of help ... otherwise, the only way to distinct selected records from "DATABASE" is DISTINCT Function.

I guess you want to remove duplicated items from datagrid after it's fetched from DB ... i hope some of the other members will help you out with this ... actually i think recently we had a thread about the same issue ... try to search around


Cheers

 

[Paszt]

I would take a different approach and not join the two tables.
Keep the two tables seperate, but in the same dataSet. Then add a relation to the dataset. Hopefully you have a primary key that would be used to relate the two tables. If not use the Name column but know that isn't a good solution.
Anyway, with the relation setup, use nested repeaters. The first repeater will list table1 info and the second, nested repeater will show the info in table2 for the related name.
Set the dataSource of the first repeater (parentRepeater in the example below) to table1 in the codeBehind. The relation is also added in the codebehind and I called it 'myRelation'.

[COLOR=Blue]<[/COLOR][COLOR=Sienna]asp:Repeater[/COLOR] [COLOR=Red]id[/COLOR][COLOR=Blue]="parentRepeater"[/COLOR] [COLOR=Red]runat[/COLOR][COLOR=Blue]="server">
    <[/COLOR][COLOR=Sienna]ItemTemplate[/COLOR][COLOR=Blue]>[/COLOR]
        <%# DataBinder.Eval(Container.DataItem,"NAME") %>&nbsp
        <%# DataBinder.Eval(Container.DataItem,"CITY") %>
        [COLOR=Blue]<[/COLOR][COLOR=Sienna]br[/COLOR][COLOR=Blue]>[/COLOR]
        [COLOR=Blue]<[/COLOR][COLOR=Sienna]asp:repeater[/COLOR] [COLOR=Red]id[/COLOR][COLOR=Blue]="childRepeater"[/COLOR] [COLOR=Red]runat[/COLOR][COLOR=Blue]="server"[/COLOR] [COLOR=Red]datasource[/COLOR][COLOR=Blue]='[/COLOR]<%# Container.DataItem.Row.GetChildRows("myRelation") %>[COLOR=Blue]'>
            <[/COLOR][COLOR=Sienna]itemtemplate[/COLOR][COLOR=Blue]>[/COLOR]
                &nbsp&nbsp&nbsp&nbsp
                <%# Container.DataItem("LANGUAGES_SPOKEN") %>
                [COLOR=Blue]<[/COLOR][COLOR=Sienna]br[/COLOR][COLOR=Blue]>
            </[/COLOR][COLOR=Sienna]itemtemplate[/COLOR][COLOR=Blue]>
        </[/COLOR][COLOR=Sienna]asp:repeater[/COLOR][COLOR=Blue]>
        <[/COLOR][COLOR=Sienna]br[/COLOR][COLOR=Blue]>
    </[/COLOR][COLOR=Sienna]ItemTemplate[/COLOR][COLOR=Blue]>
</[/COLOR][COLOR=Sienna]asp:Repeater[/COLOR][COLOR=Blue]>[/COLOR]

I included the nonbreaking spaces (&nbsp) to indent the languages, but a better alternative would be to use tables.

 

[veronica86]

Re:

Thanks for the reply, I like your approach and have tried all day to get something working. I realized today that I am really talking about displaying the parent/child records in a repeater(s) or datagrid. It seems that this is a very fundamental thing to do, but, somehow I'm messing it up!!@

I am creating the dataset and assigning it to a function that just executes the SQL select and joins my 2 tables. I know my example isn't a good design, the primary and foreign key in the 2 tables is actually a GUID, I just put NAME for simplicity (or so I thought! ha)

In all the examples on the internet they talk about first creating a DataAdapter, then filling the Data Tables in the Dataset. This is confusing to me.

I have a Dataset that is a join of 2 tables. Shoudn't I be able to get the tables out of that dataset and create the Data Relation? When I try this, I get Object Null Reference error because my table name can't be found in the dataset.

If I have a filled dataset, I'd think I should be able to assign my repeater and nested repeater from this 1 dataset.

Thanks for the reply though, I think I'm close to figuring it out, just the fact that code I'm filling the dataset from, didn't use a DataAdapter, which makes the examples not work for me

 

[veronica86]

Ok, my last response I still went on about a table join, and your advice was to not join the 2 tables. How can I do this? Don't I still need to run the SQL to get all the rows from both tables where the primary and foreign key match? Isn't that the join?

"I would take a different approach and not join the two tables.
Keep the two tables seperate, but in the same dataSet."

 

[Paszt]

A 'Join' is part of an SQL statement which combines two tables based on two fields supplied. The SQL statement that would combine your two tables is:

SELECT Table1.NAME, Table1.CITY, Table2.LANGUAGES_SPOKEN
FROM Table1 INNER JOIN Table2 ON Table1.TABLE1ID = Table2.TABLE1ID;

This would usually require a relationship setup in the databse (In MS Access for sure).

To do as I've suggested, you need to load the two tables into the same dataSet then add the relation. Yes, you'll need to run two SQL statements. Something like:
SELECT * FROM Table1
SELECT * FROM Table2

just the fact that code I'm filling the dataset from, didn't use a DataAdapter

How are you filling the dataSet? What type of dataBase?

 

[veronica86]

You were right! The solution that we took was pretty much exactly as you provided. What happened was my application was getting a DataSet, which was filled from (2) DB2 tables. Since the code that filled the DataSet did not use a DataAdapter object, (and all of the examples on the internet show how to fill a Dataset using a DataAdapter), I couldn't figure out how to Define the 2 tables and create the relationship within that DataSet.

Right or wrong, another developer suggested to just create another DataSet and loop through it and fill this 2nd DataSet. With this 2nd DataSet, I created the 2 tables and created the Data Relation just as you described.

In my opinion, creating the 2nd DataSet seemed strange, but hey..it worked, so I can't complain too much. Thanks for your help!

 

[veronica86]

I meant to say I looped through the first DataSet, which had not Data Tables or Data Relations defined, and filled the 2nd DataSet, which did have 2 Data Tables and a Data Relation defined.

 

[Paszt]

Can you post the code that fills the first dataset?
To hold data, a dataSet needs a dataTable as a dataSet alone cannot hold data.
There is no need for two dataSets and perhaps we can help you in creating more effecient code.