| Author |
Topic |
|
namitjung
Starting Member
10 Posts |
 Posted - 2005-09-07 : 05:01:22
|
| Hi all, How can i know how many rows are there in a result set.I am executing following query'made connection to databasedim sql,objsql="select id from table_one"set obj=server.createobject("ADODB.Recordset")obj.open sql,0,3,adCmd textIs there is any function which can let us know how many rows are there in a result set.I know i can do this by executing following query.sql="select count(id) as count_number from one"Since i am using php and in php there is a function mysql_num_rows($resultset) which would return how many rows are in result set, i am searching for a particular function whic would do the exact job.So, Is there any function which let us know the number of rows in a result set or we have to do it by executing the queryI used obj.recordcount but it is returning -1.Thanks in advance |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-07 : 05:20:51
|
obj.RecordCountGo with the flow & have fun! Else fight the flow  |
 |
|
|
namitjung
Starting Member
10 Posts |
Posted - 2005-09-07 : 05:26:55
|
| Thank You very much spirit1I got my solution |
|
|
|
namitjung
Starting Member
10 Posts |
Posted - 2005-09-07 : 05:33:34
|
| NO i didn't get solutionI tried obj.recordcount but it is returning -1 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-07 : 06:58:09
|
| "I tried obj.recordcount but it is returning -1"ADO does that until the whole recordset has been loaded - can you just bulk load it into an array, then obj.recordcount should give you a value (or you can even check the Upper Bound of the array!)Otherwise you'll need to do a COUNT(*) upfrontKristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-07 : 07:20:22
|
| kristen what do you mean by "until the whole recordset has been loaded" ?is that an async operation??Go with the flow & have fun! Else fight the flow |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-07 : 14:19:11
|
| "what do you mean by "until the whole recordset has been loaded""My understanding is that if you do something like:WHILE (NOT rs.EOF) Process Recordrs.GetNextRow()the rs.RecordCount may not be known until you get to the end of that loopWe always pull the whole resultset into an array to get around this - but that would obviously be useless if the resultset was massive.Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-08 : 03:26:30
|
| Otherwise Declare CursorLocation of that RecordSet to ClientSet rs.CursorLocation=AdUseClientMadhivananFailing to plan is Planning to fail |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-08 : 10:13:44
|
| In VB....recordsets can be of 4 types...(one of which is forward only)One of these types will return a recordcount, when the query is executed, without having to loop the recordset using a 'while not rs.eof' style loop....BUT it's dog slow...(basically ADO does a dummy 'while not rs.eof' loop for you before it returns the recordset to you for processing. You would be better off, getting your SP (I do hope you are using SP's!!!!!!!) to return a parameter containing the @ROWCOUNT value. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-08 : 11:21:19
|
"to return a parameter containing the @ROWCOUNT value"But that will be in the "next" recordset, so that's the same as getting ADO to "look ahead", isn't it?Is there a consensus on whether doing an up-front COUNT(*) and then the actual query adds much overhead? (Is there a chance that SQL will cache some part of the FROM/WHERE so it can then more quickly get the required columns in the second recordset?SELECT [RowCount] = COUNT(*)FROM MyTableWHERE ColA = 123 AND ColB = 'XYZ'SELECT ColD, ColE, ColFFROM MyTableWHERE ColA = 123 AND ColB = 'XYZ' |
|
|
|
|
didn't work Knowing Number of rows in a result set