Query too big

Posted on 2006-05-10
Last Modified: 2010-08-05
In ASP.NET 2.0 & SQL Server 2000
I have a page with a gridview and some textboxes.
I make a query that returns 8000 records.
I get a System.InvalidOperationException because the query is to big. If I ask for the TOP 3500 It will be ok.
Is there something to do to  the SqlDataAdapter to get more records?
Here is the kod:

   private bool Get_Cands()
    {   bool result = false;
        {  SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IIB_DataConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT Candidate.Candidate_id,  Candidate.SurName FROM  Candidate";
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;


            GRVCandSearch.DataSource = ds;
            result = true;
        catch (System.InvalidOperationException ex)
            PublicFunctions.Show_Message("query_big", Page);
        catch (Exception ex)
            PublicFunctions.Show_Message("sys_error", Page, ex.Message);
        return result;
Question by:ANAT2403
    LVL 2

    Expert Comment


    First of all I would highly suggest not retriving 3000 rows and displaying them on a web page - it will be quite slow... Having said that look at paging, might come in usefull to you:

    From my personal experience with ADO.NET I can say I hav not yet reached a querry that retyurns too many records...  I have made a data editor tool that returns 20000 + records in more than 200 columns from SQL Server and I had no problem.  if you don't believe me check this thread out, clearly somone is returning more than 8000 records:

    Anyways, I'm pretty sure it is a different problem. Can you debug and post the entire exception?

    Also, keep incrementing the TOP statement and see on which records the error occurs, check if maybe there is something wrong in the DB?

    Does the statement execute ok from a query tool?


    Author Comment

    Hi W,
    The statement execute fine from a query tool in the sqlserver.
    If I run a query in the visual studio 2005 in the server explorer it brings me the first 5717 records
    and the I have to push the arrow to get more records. I would like to know if
    there is any place that the number of records to select is limited?
    If you say that a regular query  should bring more records how do you do that?
    The complete error:

                ex.GetBaseException()      {System.InvalidOperationException: Internal connection fatal error.
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.CloseInternal(Boolean closeReader)
       at System.Data.SqlClient.SqlDataReader.Close()
       at System.Data.Common.DbDataReader.Dispose(Boolean disposing)
       at System.Data.Common.DbDataReader.Dispose()
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at CandSearch.Get_Cands(String SearchStr) in c:\Documents and Settings\Anat.BUSINESS-SOLUTI.000\My Documents\Visual Studio 2005\WebSites\IIBsite\CandSearch.aspx.cs:line 154}      System.Exception {System.InvalidOperationException}

    LVL 23

    Accepted Solution


    We're having the same issue. Our setup is Win 2003 Server IIS and Win 2000 SQL. I've found that the version of MDAC is different on the two machines. Is your setup also 2 machines? If it is, let me know if your versions of MDAC differ.


    Your sessions are sharing these objects and you are stepping on yourself.  You'd never see it on a dev box because you're never under load.

    You have two choices.  You could change your methods to make these local variables or you could make this a class and then instantiate the class and then call the methods more or less as they are.  My recommendation would be the class route.  The encapsulation will make it a more reliable and scalable approach.

    There is a zip file on this page, that has a little project that includes the source code for a data access class if you need a starting point.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
    I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

    734 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