asked on

Query too big

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;
}
}

Fushi

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

ANAT2403

ASKER

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

ASKER CERTIFIED SOLUTION

rama_krishna580

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial