Solved

Subquery returned more than 1 value

Posted on 2013-12-16
7
655 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Initial get page response times and IIS8 15 49
Delete HTML table rows 12 27
i have to take the screenshot of command prompt? how to do this? 1 35
Linq asp.net mvc 13 6
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

18 Experts available now in Live!

Get 1:1 Help Now