Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query too big

Posted on 2006-05-10
6
Medium Priority
?
243 Views
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;
        try
        {  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;

            conn.Open();
            da.Fill(ds);
            conn.Close();

            GRVCandSearch.DataSource = ds;
            GRVCandSearch.DataBind();
            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;
    }
}
0
Comment
Question by:ANAT2403
3 Comments
 
LVL 2

Expert Comment

by:Fushi
ID: 16647562
Hello,

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:

http://www.vbdotnetheaven.com/Code/Sept2003/2173.asp

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:

http://groups.google.com/group/microsoft.public.dotnet.languages.vb/browse_thread/thread/41813dff31ff8abd/a2be4f4225a8d7d9?lnk=st&q=SQLDataAdapter+too+many+records&rnum=5&hl=en#a2be4f4225a8d7d9

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?

Regards,
W
0
 

Author Comment

by:ANAT2403
ID: 16650198
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}

Thanks
Anat
0
 
LVL 23

Accepted Solution

by:
rama_krishna580 earned 2000 total points
ID: 16662646
Hi,

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.

OR

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, http://www.vpsw.com/WebFormPref.aspx that has a little project that includes the source code for a data access class if you need a starting point.

R.K
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

581 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