1 Replies - 801 Views - Last Post: 12 February 2017 - 05:23 PM Rate Topic: -----

#1 Sweg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 12-February 17

Help Needed Displaying Data from Joined SQL Server Tables

Posted 12 February 2017 - 09:08 AM

Hi, here is the requirement which I am working on:
  • I need to create an ASPX web forms page which displays a number of local group details (Soccer, Golf, etc.)
  • Each local group section will contain a number of photo albums / collections
  • Each photo album / collection will contain a number of photos


Here are my SQL Server tables:

CREATE TABLE [dbo].[Groups] (
    [Group_Id]   INT            IDENTITY (1, 1) NOT NULL,
    [Group_Name] NVARCHAR (50)  NULL,
    [Group_Desc] NVARCHAR (MAX) NULL,
    [Group_Type] NVARCHAR (50)  NULL,
    CONSTRAINT [Groups.Group_Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Group_Id] ASC)
);

CREATE TABLE [dbo].[Photo_Collection] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Group_Id] INT            NOT NULL,
    [Name]     NVARCHAR (250) NULL,
    CONSTRAINT [Photo_Collection.Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [Photo_Collection.to.Groups] FOREIGN KEY ([Group_Id]) REFERENCES [dbo].[Groups] ([Group_Id])
);

CREATE TABLE [dbo].[Photo_Collection_Images] (
    [Photo_Collection_Id] INT NOT NULL,
    [Group_Image_Id]      INT NOT NULL,
    CONSTRAINT [Photo_Collection_Images.to.Photo_Collection] FOREIGN KEY ([Photo_Collection_Id]) REFERENCES [dbo].[Photo_Collection] ([Id]),
    CONSTRAINT [Photo_Collection_Images.to.Group_Images] FOREIGN KEY ([Group_Image_Id]) REFERENCES [dbo].[Group_Images] ([ID])
);

CREATE TABLE [dbo].[Group_Images] (
    [ID]        INT            IDENTITY (1, 1) NOT NULL,
    [Group_Id]  INT            NOT NULL,
    [filename]  VARCHAR (250)  NULL,
    [imageDesc] NVARCHAR (250) NULL,
    CONSTRAINT [Group_Images.ID.Primary Key] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [Group_Images.to.Groups] FOREIGN KEY ([Group_Id]) REFERENCES [dbo].[Groups] ([Group_Id])
);



Below is what I am trying to do:

  • Group_Name of each group in the Groups table
  • The Name of each Photo_Collection associated with each group
  • Retrieve the filename (column name) for each image associated with each photo collection


And I am trying to do this with using the following code:

   protected void bindStuff()
    {
        SqlConnection connR;
        string connectionStringR = ConfigurationManager.ConnectionStrings[
            "FYPConnectionString1"].ConnectionString;
        connR = new SqlConnection(connectionStringR);
        SqlDataAdapter sda = new SqlDataAdapter(@"DECLARE @Group_Id INT; DECLARE @Photo_Collection_Id INT; DECLARE @Group_Image_Id INT; SELECT @Group_Id = Group_Id, Group_Name FROM Groups SELECT @Photo_Collection_Id = Id, Name FROM Photo_Collection WHERE Group_Id = @Group_Id SELECT @Group_Image_Id = Group_Image_Id FROM Photo_Collection_Images WHERE Photo_Collection_Id = @Photo_Collection_Id SELECT Id, filename, imageDesc FROM Group_Images WHERE ID = @Group_Image_Id;", connR);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        repStuff.DataSource = dt;
        repStuff.DataBind();
    }



But I am getting this error:
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Can you guys please tell me what is wrong with this statement, and what do I need to change within it to achieve my requirement?

Thanks in advance.

Before I tried the above code, this is what I was working with:

ASPX:

<asp:Repeater ID="repGroupPhotoGallery" runat="server" OnItemDataBound="repGroupPhotoGallery_ItemDataBound">
                            <ItemTemplate>
                                <div class="col-md-3">
                                    <div class="panel panel-default">
                                        <div class="panel-heading">
                                            <h3 class="panel-title">
                                                <%# Eval("Group_Name") %>
                                            </h3>
                                        </div>
                                        <!--panel-heading-->
                                        <div class="panel-body">
                                            <div class="col-md-4 text-center">
                                                <div class="thumbnail">
                                                    <asp:DataList ID="dlImages" runat="server" RepeatDirection="Horizontal" RepeatColumns="3" CellPadding="5">
                                                        <ItemTemplate>
                                                            <div class="caption">
                                                                <h3><%# Eval("pc_name") %><br />
                                                                </h3>
                                                                <a id="imageLink" href='<%# Eval("si_filename","/Group_Images/{0}") %>' title='<%#Eval("si_description") %>' rel="lightbox[Brussels]">
                                                                    <asp:Image ID="Image1" ImageUrl='<%# Bind("si_filename", "~/Group_Images/{0}") %>' runat="server" Width="112" Height="84" />
                                                                </a>
                                                            </div>
                                                        </ItemTemplate>
                                                    </asp:DataList>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    <!--panel-default-->
                                </div>
                                <!--col-md-3-->
                            </ItemTemplate>
                        </asp:Repeater>


Code Behind:

    private void BindRepeater()
    {
        string constr = ConfigurationManager.ConnectionStrings["FYPConnectionString1"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                //ORDER BY DATE
                cmd.CommandText = "select * from Groups WHERE Group_Type ='Group'";
                cmd.Connection = con;
                con.Open();
                repGroupPhotoGallery.DataSource = cmd.ExecuteReader();
                repGroupPhotoGallery.DataBind();
                con.Close();
            }
        }
    }
    protected void repGroupPhotoGallery_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        DataList datalist = e.Item.FindControl("dlImages") as DataList;

        //find the correct group id of the item
        string student_Id = DataBinder.Eval(e.Item.DataItem, "Group_Id").ToString();
        SqlDataAdapter sda = new SqlDataAdapter("select pc_name = pc.Name, s_Id = si.Group_Id, si_filename = si.filename, si_description = si.imageDesc from Groups as s inner join dbo.group_images as si on s.Group_Id = si.group_id inner join dbo.photo_collection_images pci on pci.group_image_id = si.Group_Id inner join dbo.photo_collection as pc on pc.id = pci.photo_collection_id where s.Group_Id =" + student_Id, con);
        DataTable dt = new DataTable();
        sda.Fill(dt);

        //bind data to the nested datalist with the Group_Id in the where clause of the query
        datalist.DataSource = dt;
        datalist.DataBind();
    }



This code displayed all existing groups, all images associated with those groups, but the incorrect Photo Collection names. (I.e. the Photo Collection names appearing above each image were not linked within the tables to these images.)

Is This A Good Question/Topic? 0
  • +

Replies To: Help Needed Displaying Data from Joined SQL Server Tables

#2 Martyr2  Icon User is online

  • Programming Theoretician
  • member icon

Reputation: 5074
  • View blog
  • Posts: 13,697
  • Joined: 18-April 07

Re: Help Needed Displaying Data from Joined SQL Server Tables

Posted 12 February 2017 - 05:23 PM

Ok, so lets take a look at one section of your query and see what is wrong here...

DECLARE @Group_Id INT; 
DECLARE @Photo_Collection_Id INT; 
DECLARE @Group_Image_Id INT; 
SELECT @Group_Id = Group_Id, Group_Name FROM Groups



You see here how you have a set of variables and then for your select you have one column going to the variable and the other which is not? Group_name is being retrieved in the select while the other one, Group_Id is going to a variable. You can't mix this in the same operation. It is all one or the other. Let's try doing a fix..

DECLARE @Group_Id INT; 
DECLARE @Photo_Collection_Id INT; 
DECLARE @Group_Image_Id INT; 
DECLARE @Group_name VARCHAR(30)
SELECT @Group_Id = Group_Id, @Group_name = Group_Name FROM Groups




Ok, now both columns are going into variables that you can then use. Otherwise you can do your normal select for just the columns depending on what you want to achieve next. :)
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1