Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Getting Number Of Rows returned from query

Reply
Thread Tools

Getting Number Of Rows returned from query

 
 
Chris Tremblay
Guest
Posts: n/a
 
      09-09-2004
I am trying to figure out how to go about retrieving the number of results
returned from my queries in SQL server from VB.NET without using a the
Select Count(*) query. The method that I was using was the following:

Take the query that I am executing, copy the query and turn it into a count
query, run the count query, then execute the original query.

The reason for this is so that I can implememt public paging on my website.
The problem with this method is that it is putting to much stress on my SQL
Server. Is there a way that I can find the number of rows returned. The way
I figure, if you use an SQLdatareader, it knows when it has reached the end
of the result list, therefore, you should be able to have some access to the
number of results without reading through each record.

--

Thanks,
Chris Tremblay
www.mtgfanatic.com, Inc.


 
Reply With Quote
 
 
 
 
Karl
Guest
Posts: n/a
 
      09-09-2004
Chris,
You won't be able to do it via the SQLDataReader without reading through the
records first (in which case you can simply increment a counter, or use the
solution I briefly outline in #2)

1 - Consider using a datatable. You'll easily be able to access the number
of rows via datatable.Rows.Count. Additionally, if your SQL Server is
struggling, you'll be able to cache the datatable and reduce the load (I
realize that you are probably doing a search in which case the results
aren't very cache friendly, but hey , I thought I'd throw it out there
anyways).

2 - You can access an output parameter after doing a dr.close, which could
be the @@RowCount SQL server automatically generates. I'd go into more
details, but since you need to have the datareader closed, I'll assume that
you'll have already read through it and could simply have used a
counter...so this probably is a no go.

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/


"Chris Tremblay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am trying to figure out how to go about retrieving the number of results
> returned from my queries in SQL server from VB.NET without using a the
> Select Count(*) query. The method that I was using was the following:
>
> Take the query that I am executing, copy the query and turn it into a

count
> query, run the count query, then execute the original query.
>
> The reason for this is so that I can implememt public paging on my

website.
> The problem with this method is that it is putting to much stress on my

SQL
> Server. Is there a way that I can find the number of rows returned. The

way
> I figure, if you use an SQLdatareader, it knows when it has reached the

end
> of the result list, therefore, you should be able to have some access to

the
> number of results without reading through each record.
>
> --
>
> Thanks,
> Chris Tremblay
> www.mtgfanatic.com, Inc.
>
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?LS1kd2Vlemls?=
Guest
Posts: n/a
 
      09-09-2004
You could use a counter variable like so...

SQLDataReader dr = new SQLDataReader();
int counter = 0;
// other database code goes here
while ( dr.Read() )
{
counter +=;
}
Response.Write ( "Number of records is: " + counter );

Hope this helps

"Chris Tremblay" wrote:

> I am trying to figure out how to go about retrieving the number of results
> returned from my queries in SQL server from VB.NET without using a the
> Select Count(*) query. The method that I was using was the following:
>
> Take the query that I am executing, copy the query and turn it into a count
> query, run the count query, then execute the original query.
>
> The reason for this is so that I can implememt public paging on my website.
> The problem with this method is that it is putting to much stress on my SQL
> Server. Is there a way that I can find the number of rows returned. The way
> I figure, if you use an SQLdatareader, it knows when it has reached the end
> of the result list, therefore, you should be able to have some access to the
> number of results without reading through each record.
>
> --
>
> Thanks,
> Chris Tremblay
> www.mtgfanatic.com, Inc.
>
>
>

 
Reply With Quote
 
Greg Burns
Guest
Posts: n/a
 
      09-09-2004
Unless I missing something this won't work.

If he is doing paging, he is probably only returning in the datareader a
subset (1 page) of the total records.

Greg


"--dweezil" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You could use a counter variable like so...
>
> SQLDataReader dr = new SQLDataReader();
> int counter = 0;
> // other database code goes here
> while ( dr.Read() )
> {
> counter +=;
> }
> Response.Write ( "Number of records is: " + counter );
>
> Hope this helps
>
> "Chris Tremblay" wrote:
>
>> I am trying to figure out how to go about retrieving the number of
>> results
>> returned from my queries in SQL server from VB.NET without using a the
>> Select Count(*) query. The method that I was using was the following:
>>
>> Take the query that I am executing, copy the query and turn it into a
>> count
>> query, run the count query, then execute the original query.
>>
>> The reason for this is so that I can implememt public paging on my
>> website.
>> The problem with this method is that it is putting to much stress on my
>> SQL
>> Server. Is there a way that I can find the number of rows returned. The
>> way
>> I figure, if you use an SQLdatareader, it knows when it has reached the
>> end
>> of the result list, therefore, you should be able to have some access to
>> the
>> number of results without reading through each record.
>>
>> --
>>
>> Thanks,
>> Chris Tremblay
>> www.mtgfanatic.com, Inc.
>>
>>
>>



 
Reply With Quote
 
http://www.visual-basic-data-mining.net/forum
Guest
Posts: n/a
 
      09-09-2004
After you run the query, run the statement SELECT @@ROWCOUNT

--
data mining and .net team
http://www.visual-basic-data-mining.net/forum


"Chris Tremblay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I am trying to figure out how to go about retrieving the number of results
> returned from my queries in SQL server from VB.NET without using a the
> Select Count(*) query. The method that I was using was the following:
>
> Take the query that I am executing, copy the query and turn it into a

count
> query, run the count query, then execute the original query.
>
> The reason for this is so that I can implememt public paging on my

website.
> The problem with this method is that it is putting to much stress on my

SQL
> Server. Is there a way that I can find the number of rows returned. The

way
> I figure, if you use an SQLdatareader, it knows when it has reached the

end
> of the result list, therefore, you should be able to have some access to

the
> number of results without reading through each record.
>
> --
>
> Thanks,
> Chris Tremblay
> www.mtgfanatic.com, Inc.
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change the returned rows in a gridview Oystein@Norway ASP General 2 10-24-2006 10:15 AM
Re: Number of rows returned from ObjectDataSource.FilterExpression John ASP .Net 2 03-18-2006 02:52 PM
2.0 ObjectDataSource number rows returned John A Grandy ASP .Net 0 03-16-2006 06:26 PM
quick one--how do I know how many rows returned? middletree ASP General 8 11-28-2003 02:22 AM
SQL get count(*) from items... no rows returned error Pikarde ASP .Net 2 11-24-2003 09:13 PM



Advertisments