Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
1
Reply
Joined tables not displaying as required
Damien Sullivan
Feb 12 2017 11:25 AM
193
Reply
Hi,
Using the below tables, I am trying to retrieve the following:
All Group_Name's from the Groups table
All Name's from the Photo_Collection table which are linked to a Group (Group_Id)
All filename's from Group_Images which are linked to a Photo_Collection (Group_Image_Id).
I want to basically display Photo Albums for each Group using the above.
I have tried 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_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
= 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 get this error:
"
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
"
Can someone please help, and tell me what I need to change in this code?
Here is my SQL Server table code:
CREATE TABLE [dbo].[Groups] (
[Group_Id] INT IDENTITY (1, 1) NOT NULL,
[Group_Name] NVARCHAR (50) NULL,
[Group_Desc] NVARCHAR (MAX) NULL,
CONSTRAINT [Groups.Group_Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Group_Id] ASC)
);
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])
);
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])
);
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
1
)
Next Recommended Forum
How to retrive data from two tables not having common column
To Store More then 8000 in Varchar for dynamic Query