Dataset - data adapter related help please

[nzkks]

Hi, I am trying the following SQL to fetch the needed data. The query works in query analyzer in SQL Server 2005. But in data adapter in a dataset in Visual Studio 2005, I am getting the data with all other empty cells. What mistake I am doing? The expected result is only a concatenated value (~/images/logos/Somelogo.jpg)

SELECT (SELECT FilePath FROM dbo.FilePaths WHERE (FilePathID = dbo.Brands.BrandLogoImageFilePath)) + BrandLogoImageName AS BrandLogoImage FROM dbo.Brands WHERE (BrandID = @BrandID) AND (IsActive = 1)

This is SQL script for creating the tables.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FilePaths](
    [FilePathID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](30) NOT NULL,
    [FilePath] [varchar](50) NOT NULL,
 CONSTRAINT [PK_FilePaths] PRIMARY KEY CLUSTERED
(
    [FilePathID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Brands](
    [BrandID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](40) NOT NULL,
    [BrandLogoImageFilePath] [int] NOT NULL,
    [BrandLogoImageName] [varchar](40) NOT NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_Brands_IsActive]  DEFAULT ((1)),
 CONSTRAINT [PK_Brands] PRIMARY KEY NONCLUSTERED
(
    [BrandID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Brands]  WITH CHECK ADD  CONSTRAINT [FK_Brands_FilePaths_BrandLogoImageFilePath] FOREIGN KEY([BrandLogoImageFilePath])
REFERENCES [dbo].[FilePaths] ([FilePathID])
GO
ALTER TABLE [dbo].[Brands] CHECK CONSTRAINT [FK_Brands_FilePaths_BrandLogoImageFilePath]
GO
 INSERT INTO [dbo].[FilePaths]([Name],[FilePath]) VALUES (logos,~/images/logos/)
GO
INSERT INTO [dbo].[Brands] ([Name],[BrandLogoImageFilePath],[BrandLogoImageName],[IsActive]) VALUES ('SomeBrand',1,'somelogo.jpg',True)
 GO