Solved

Subquery returned more than 1 value

Posted on 2013-12-16
7
630 Views
Last Modified: 2013-12-16
I have a stack trace. How can I find out where the error occurred?

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Stack Trace:



[SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1767866
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5352418
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691
   System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) +322
   System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) +230
   System.Data.SqlClient.SqlDataReader.Read() +34
   System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +64
   System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +148
   System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +421
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +173
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +316
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1481
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +138
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +30
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
   System.Web.UI.Control.EnsureChildControls() +83
   System.Web.UI.Control.PreRenderRecursiveInternal() +42
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Control.PreRenderRecursiveInternal() +155
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974

Thanks,

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18408
0
Comment
Question by:Dovberman
  • 4
  • 2
7 Comments
 
LVL 3

Assisted Solution

by:mikeyd234
mikeyd234 earned 100 total points
ID: 39721875
Are you able to post the SQL query?
0
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 400 total points
ID: 39721890
It would be hard to say what caused the problem without looking at the query or understanding the data... plz post the query
0
 

Author Comment

by:Dovberman
ID: 39722008
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
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Dovberman
ID: 39722010
I may need to rebuild the solution page by page.  I was hoping that there would be a clue in the stack trace.
0
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 400 total points
ID: 39722198
you can look for sub queries in the SQL statements and try using aggregate functions to see if that resolves the problem but without knowing the data, you might end with wrong outputs

you can run all queries at once in SSMS, if they are only Select statements....

SSMS returns a more meaningful error, atleast helps us identify the query that's causing the problem and you can post that here for more help
0
 

Author Comment

by:Dovberman
ID: 39722388
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,
0
 

Author Closing Comment

by:Dovberman
ID: 39722394
Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now