Subquery returned more than 1 value

The problem is that I have no idea which of the 30 queries it is attempting to run.

I modified the code but not the stored procedures.

I can look at each SELECT statement in the code.

I know about using TOP 1

I may need to rebuild the solution page by page.  I was hoping that there would be a clue in the stack trace.

I found the offending stored procedure.

This is strange. The SP executes without error.  This SP is the datasource for a gridview.

Here is the code:

USE [stockprosql]
GO
/****** Object:  StoredProcedure [dbo].[usp_getBestPicksTop]    Script Date: 12/16/2013 14:09:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      Builds the dataset for the Best Picks grid
-- =============================================
ALTER PROCEDURE [dbo].[usp_getBestPicksTop]
      -- Add the parameters for the stored procedure here
AS

DECLARE @ToDate datetime
DECLARE @FromDate datetime
DECLARE @AsOfDate datetime
DECLARE @DaysBack int
DECLARE @MarketDays int
DECLARE @ResearchSiteURL varchar(100)
DECLARE @SiteEvalType varchar(50)
--DECLARE      @UserName varchar(50)  

/*
exec usp_getBestPicks
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D'
@FromDate = '2009-05-01'
@ToDate = '2009-05-31',
@ResearchSiteURL = 'http://investing.money.msn.com/investments/stock-price?Symbol='
*/

BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
-- Set parameters
--SET @UserID = '0A742A71-B5D5-4E00-9E5F-BD3C82B5936A'
--SET      @UserName = 'maryann'
SET @SiteEvalType = '/analyst-research/'
SET @DaysBack = 30
SET @ToDate=(SELECT MAX(QuoteDate) FROM DownLoadDates WHERE MarketID=1)
--SET @ResearchSiteURL = 'http://investing.money.msn.com/investments/stock-price?Symbol='
SET @ResearchSiteURL = 'http://www.nasdaq.com/symbol/'

SET @FromDate = (SELECT MIN(QuoteDate)
  FROM
  (SELECT TOP (@DaysBack) QuoteDate
   FROM DownLoadDates
   WHERE MarketID= 3
   ORDER BY QuoteDate DESC) t);

SET @AsOfDate =(SELECT TOP 1 QuoteDate
  FROM DownLoadDates
  WHERE MarketID= 3
  ORDER BY QuoteDate DESC)

SET ROWCOUNT 1
 
SELECT  
 w.UserName
, w.SymbolID
, @FromDate AS FromDate
, @ToDate AS ToDate
, (SELECT SymbolName FROM Symbol WHERE SymbolID = w.SymbolID) AS SymbolName
, (SELECT SUBSTRING(SecName, 1, 20) FROM Symbol WHERE SymbolID = w.SymbolID) AS SecName
, w.MarketID
, (SELECT MarketName FROM Market
      WHERE MarketID = w.MarketID)
      AS MarketName
, w.WatchListID
, w.PickDate
, @ResearchSiteURL AS SiteURL

, @SiteEvalType AS SiteEvalType      

, (SELECT Count(*) FROM DownLoadDates
  WHERE QuoteDate >=(w.PickDate +1 ) AND MarketID = 1) As DaysOnList

, (SELECT PickDatePrice FROM WatchList
      WHERE PickDate=w.PickDate    
      AND SymbolID=w.SymbolID)
      AS PickPrice    

,  (SELECT ClosePrice FROM StockHist
      WHERE QuoteDate=@FromDate  
      AND SymbolID=w.SymbolID)
      AS BasePrice

,  (SELECT COUNT(*) FROM StockHist
      WHERE ClosePrice > ClosePricePrev
      AND QuoteDate BETWEEN w.PickDate AND @AsOfDate  
      AND  SymbolID=w.SymbolID )
      AS PriceUpCount
     
      , (SELECT Top(1) ClosePrice FROM StockHist
      WHERE QuoteDate=@AsOfDate    
      AND SymbolID=w.SymbolID)
      -
       (SELECT ClosePrice FROM StockHist
      WHERE QuoteDate=@FromDate  
      AND SymbolID=w.SymbolID)
      AS PriceChange
     
 ,   (SELECT Top(1) ClosePrice FROM StockHist
      WHERE QuoteDate=@AsOfDate    
      AND SymbolID=w.SymbolID)
      AS CurrentPrice
     
      , (SELECT 2* STDEV(HighPrice-LowPrice)
      FROM StockHist WHERE SymbolID=w.SymbolID)
      AS CI95
 
  -- PctChange = ((CurrentPrice - BasePrice)/BasePrice) * 100
     
   ,( ( (SELECT Top(1) ClosePrice FROM StockHist
      WHERE QuoteDate=@AsOfDate    
      AND SymbolID=w.SymbolID)
     
      -
       (SELECT ClosePrice FROM StockHist
      WHERE QuoteDate=@FromDate  
      AND SymbolID=w.SymbolID) )
     
      /
     
         (SELECT ClosePrice FROM StockHist
      WHERE QuoteDate=@FromDate  
      AND SymbolID=w.SymbolID) ) * 100
      AS PctChange  
      
FROM Watchlist w
WHERE w.UserID='0A742A71-B5D5-4E00-9E5F-BD3C82B5936A'
ORDER BY PctChange DESC
END

The returned more than one value error occurs when the webform containing the datasource that the gridview is based on is called.

<asp:GridView id="grdWatchList" runat="server"  
            Height="20px"
            Width="800px" Caption="Best Picks List " 
            BorderColor="Black" BorderStyle="Double" BorderWidth="1px"
            BackColor="Azure" AlternatingRowStyle-BackColor="#FFFF66"
            AlternatingRowStyle-BorderStyle="Solid" Font-Names="Arial"
            Font-Size="10pt" AutoGenerateColumns="False"
            DataKeyNames="SymbolID" PageSize="1"
            CaptionAlign="Left" ForeColor="Black" DataSourceID="dscWatchList"
            AllowPaging="True"              
           >

There is no error if I remove "DataSourceID="dscWatchList"
     
<asp:SqlDataSource ID="dscWatchList" runat="server"
         ConnectionString="<%$ ConnectionStrings:StockSelectSQLConnectionString %>"
         SelectCommand="usp_getBestPicksTop"
         SelectCommandType="StoredProcedure" >
        </asp:SqlDataSource>  


StoredProcedure [dbo].[usp_getBestPicksTop] is designed to return only one row.

Problem solved!

The data is now more than 30 days old.  This created an attempt to return more than one row.

Thanks,

Thank you.