Many-to-many relationships

Hi

Does anyone no of any good resources regarding many-to-many relationships? More specifically on displaying data from many-to-many tables in ASP.

Struggling to find anything but i may just be over complicating things!

thanks

it’s so straight-forward, i think we can cover it right here in this thread

CREATE TABLE one
( id  INTEGER NOT NULL PRIMARY KEY
, foo VARCHAR(9) NOT NULL
);
CREATE TABLE two
( id  INTEGER NOT NULL PRIMARY KEY
, bar VARCHAR(37) NOT NULL
);
CREATE TABLE one_two
( one_id INTEGER NOT NULL
, two_id INTEGER NOT NULL 
, FOREIGN KEY ( one_id ) REFERENCES one ( id )
, FOREIGN KEY ( two_id ) REFERENCES two ( id )
, PRIMARY KEY ( one_id, two_id )
, INDEX buckle_my_shoe ( two_id, one_id )
, startdate DATETIME NOT NULL
);

have a go at writing a query to get all the bars for a given foo, say foo=‘todd’

Thanks

I already have my tables setup, was just having problems getting this to display on my asp page

okay, sorry, forget i said anything :slight_smile:

hehe no problem

I probably should have waited till i got home from work before posting the question anyway. Will add my code later, so it probably makes more sense what im asking!

Here is a simplified version of what i want to achieve.
Not sure if im on the right lines or not!

I have 3 tables in a many-to-many relationship

titles
titleactor
actors

All im trying to do with the code below is list all Titles with the coresponding actors but get the error
NullReferenceException was unhandled by user code
Object reference not set to an instance of an object.

Any ideas? Thanks in advance


protected void Page_Load(object sender, EventArgs e)
    {
        //define ado.net objects
        string connectionString = ConfigurationManager.ConnectionStrings["movies"].ConnectionString;
        string selectSQL = "SELECT title, title_id FROM titles";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(selectSQL, con);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataSet dsDvds = new DataSet();

        try
        {
            con.Open();
            adapter.Fill(dsDvds, "titles");

            //this command is still linked to the data adapter
            cmd.CommandText = "SELECT title_id, act_id FROM titleactor";
            adapter.Fill(dsDvds, "titleactor");

            //this command is still linked to the data adapter
            cmd.CommandText = "SELECT act_id, act_name FROM titleactor";
            adapter.Fill(dsDvds, "actors");
        }
        catch (Exception err)
        {
            lblList.Text = "Error reading list of dvds. ";
            lblList.Text = err.Message;
        }
        finally
        {
            con.Close();
        }

        //Define Relationships

        DataRelation Actors_TitleActor = new DataRelation("Actors_TitleActor",
                    dsDvds.Tables["actors"].Columns["act_id"],
                    dsDvds.Tables["titleactor"].Columns["act_id"]);

        DataRelation Titles_TitleActor = new DataRelation("Titles_TitleActor",
                    dsDvds.Tables["titles"].Columns["title_id"],
                    dsDvds.Tables["titleactor"].Columns["title_id"]);

        //add to dataset
        dsDvds.Relations.Add(Actors_TitleActor);
        dsDvds.Relations.Add(Titles_TitleActor);


        foreach (DataRow rowTitle in dsDvds.Tables["titles"].Rows)
        {
            lblList.Text += "<br /><b>" + rowTitle["title"];
            lblList.Text += " " + rowTitle["year"] + "</b><br />";

            foreach (DataRow rowTitleActor in rowTitle.GetChildRows(Titles_TitleActor))
            {
                foreach (DataRow rowActor in rowTitleActor.GetParentRows(Actors_TitleActor))
                {
                    lblList.Text += "&nbsp;&nbsp";
                    lblList.Text += rowTitle["actor"] + "br />";
                }
            }
        }



    }
}


am i reading this correctly? you’re ~not~ using a single join query, but instead you’re getting data separately by looping through the related table?

yes thats correct.

Would it be better to use a join query?

First time ive attempted this, so was following a method i read in a book!

absotively!!!

run this in your query window (i.e. outside of asp) –

SELECT titles.title
     , actors.act_name
  FROM titles
INNER
  JOIN titleactor
    ON titleactor.title_id = titles.title_id
INNER
  JOIN actors
    ON actors.act_id = titleactor.act_id
ORDER
    BY titles.title
     , actors.act_name

see if you can figure out how you would take the results set and produce the listing you wanted

yeah much easier thanks

Thought i was over complicating things

Everything is displaying correctly now so thanks for you help.

One more thing though. How would i go about Inserting into tables with INNER JOIN

Here is my simple SELECT statement

select titles.title, titles.year, titles.image
FROM titles
INNER JOIN genre on titles.genre_id = genre.genre_id

Just unsure of how i can insert

Thanks

INSERT operates on only one table at a time

perhaps if you have multiple tables to insert into, you should write a stored procedure for that purpose

Sorry, im just confusing myself!

I dont actually need to insert into 2 tables.

i have the following 2 tables

Titles
-title_id
-title
-year
-rating
-genre_id

Genre
-genre_id
-genre

I have set up a simple Asp page showing the data from Titles and Genre

I want to be able to edit and add new rows from a DetailsView but having a drop down list for genre instead of the genre_id.

Hope that makes sense

yes, it makes sense, but i can’t help you with the progrmming, as i don’t do asp

Ok, well we can help you with your code. What code do you have at the moment for this?

Hi Here is my code so far


<p><asp:LinkButton ID="addDvd" runat="server" Text="Add New DVD" OnClick="addDvd_Click" /></p>

<asp:GridView   
    ID="gridDvd"
    CellPadding="2"
    runat="server"
    AllowSorting="True"
    AutoGenerateColumns="False"
    DataSourceID="SqlDataSource1"
    DataKeyNames="title_id" 
    AllowPaging="True">
    <Columns>      
        <asp:BoundField DataField="title_id" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="title_id" />
        <asp:BoundField DataField="title" HeaderText="title" SortExpression="title" />
        <asp:BoundField DataField="year" HeaderText="year" SortExpression="year" />
        <asp:BoundField DataField="genre" HeaderText="genre" SortExpression="genre" />
        <asp:CommandField ShowSelectButton="True" />
    </Columns>
    <SelectedRowStyle CssClass="GridSelectedRow" />
    <HeaderStyle CssClass="GridHeader" />
</asp:GridView>

<br />
<br />
<div>
<asp:DetailsView    
    id="detDvd" 
    DataSourceID="SqlDataSource2" 
    DataKeyNames="title_id"
    runat="server"
    AutoGenerateRows="False"
    AutoGenerateDeleteButton="true"
    AutoGenerateInsertButton="true"
    AutoGenerateEditButton="true">
    <Fields>
        <asp:BoundField DataField="image" HeaderText="Image" SortExpression="image" />        
        <asp:BoundField DataField="title" HeaderText="Title" SortExpression="title" />
        <asp:BoundField DataField="year" HeaderText="Year" SortExpression="year" />
        <asp:BoundField DataField="rating" HeaderText="Rating" SortExpression="rating" />
        <asp:TemplateField HeaderText="Genre" SortExpression="genre">
            <EditItemTemplate>
                <asp:DropDownList ID="ddlGenre" runat="server" />
            </EditItemTemplate>
            <InsertItemTemplate>
                <asp:DropDownList ID="ddlGenre" runat="server"/>
            </InsertItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" ></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Fields>
</asp:DetailsView>
</div>

<asp:SqlDataSource 
    ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:moviesConnectionString %>" 
    SelectCommand="select titles.title_id, titles.title, titles.year, genre.genre
                    FROM titles INNER JOIN genre on titles.genre_id = genre.genre_id">
</asp:SqlDataSource>
<asp:SqlDataSource 
    ID="SqlDataSource2" 
    runat="server" 
    ConnectionString="<%$ ConnectionStrings:moviesConnectionString %>" 
    SelectCommand="select titles.title_id, titles.title, titles.year, titles.image, titles.rating, genre.genre
                    FROM titles INNER JOIN genre on titles.genre_id = genre.genre_id WHERE titles.title_id=@title_id">
    <SelectParameters>
        <asp:ControlParameter ControlID="gridDvd" Name="title_id" PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

</asp:Content>

thankyou

I mean your insert form on how to trying to insert the data into the tables. As that is what you need help with. right? Or did I miss something?

I can insert into the tables ok.
What i want is to have a drop down list that instead of showing the id, its shows the values from the joined table

EDIT:



<asp:DropDownList ID="ddlGenre" DataTextField='<&#37;#Eval("Title")%>' DataValueField='<%#Eval("Title_id")%>'   DataSource="SqlDatasource1" runat="server" />