July 11, 2001 at 3:37 am
Hi all
Developers are calling @@ROWCOUNT after their queries to give them a total count of rows returned. They use this in the front-end screen to tell the users the total rows return whilst they are paging throw the result set.
They are finding issues with @@ROWCOUNT which returns zero for various queries based on
a) total data returned
b) total rows returned
for example, one query will return say 100 rows with XYZ where clause criteria and @@ROWCOUNT reports this correctly. We change the where clause, thus returned over 294 rows and now @@ROWCOUNT is returning zero!?
It doesnt seem to be related to the total rows returned, but also affected by the size (amount of data) returned from the query. For example, another query will report @@ROWCOUNT correctly after 1000 rows, but not after 2345 rows!?
They are using ADO recordsets via COM+ (component services). They open the record set, run the query, close then check @@ROWCOUNT. As mentioned, it works sometimes and others not. Its like ADO or SQLServer is executing another job after the SQL statement, but when tracing via PROFILER, they are executed one after the other (query then then rowcount check).
Ideas?
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 11, 2001 at 6:26 am
Connection pooling and/or object pooling may be the issue here. I havent had any problems with @@rowcount (have you checked MSDN to be sure though?). I would suggest you test without the COM+ piece to see if the problem still occurs. An alternative if you could do it would be to build a stored proc that returned the value of @@rowcount.
Andy
July 11, 2001 at 10:21 am
I agree with Andy that this is probably a connection pooling issue. I would also recommend running these queries in Query Analyzer and see if the results are correct. Something you can have your developers try is sending both sql commands in one trip to the server. Example:
strSQL="SELECT * from users where type='int';Select @@RowCount"
adoRs.open strSQL,conn
This way both sql commands should keep the same connection and also you are increasing your chances that the @@rowCount really corresponds to the query you just ran.
July 11, 2001 at 11:34 am
I had a similar problem with a very busy web app and ended up putting all of those kinds of functions that needed return row counts into sprocs to keep them grouped together. sending them with the same connection also works. Also, if you are using ADO record sets you can pull the record count from the set directly thats what I do to setup paging in my app. A side bennie for using record sets is you can dump the set into a local var and disconnect the connection so you don't have to worry about updates or invalid row counts after the recordset is loaded.
July 11, 2001 at 4:05 pm
I've used both of these suggestions, though developers usually prefer burthold's suggestion of using the ADO value.
Steve Jones
July 13, 2001 at 1:54 pm
no you don't have to disconnect the record set to pull a row count. you do have to setup the recordset to pull the right count though.
**
objrec.cursorlocation = 3 'set cursor location so we can check the count adUseClient if you have adovbs.inc and are using the defs
**
set cursor location so we can check the count
If you don't set the cursorlocation you get a -1 rowcount return. This is because its using the server instead of the client. This is a cut streight out of one of my pages.
Wes
August 5, 2001 at 9:45 pm
Hi, I am also getting one major error. I cannot seem to check for the EOF or rather that line doesn't work. So I tried to get the rs.Recordcount. It returns me a -1
August 5, 2001 at 10:22 pm
Can you post a small sample of your code? Generally there shouldn't be any reason .EOF would fail unless the connection isn't getting opened and/or the recordset isn't getting created.
Edited by - bkelley on 08/05/2001 10:23:08 PM
K. Brian Kelley
@kbriankelley
August 5, 2001 at 11:51 pm
set rsConn = Server.CreateObject("ADODB.Connection")
rsConn.Open SQLdb
set newscmd = Server.CreateObject("ADODB.Command")
set newscmd.activeconnection = rsConn
newscmd.CommandText = "Select * FROM News WHERE Category = '" & Category & "' ORDER BY ID DESC"
newscmd.CommandType = 1
Set rsNews = newscmd.Execute
If Not rsNews.EOF Then
......
End If
Can u pls help me out here??
Marvin
August 6, 2001 at 12:09 am
oh yah and i forgot to tell u...i also need to check basically whether it is an empty recordset...for cases when the user enters nonsensical data...which doesn't exist in the database.
Thanks
Marvin
August 6, 2001 at 2:31 am
Hi there, I had this problem yonks ago when going a bit of VB work.
Its something to do with when EOF gets initialised and its value...
try this:
a) Remove the NOT from the IF and reverse your logic
b) Check for an empty record set (no data)
Does it work with a single record returned from the record set?
also check you are not missing off the last or first records from the record set with your logic.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 6, 2001 at 3:02 am
Hi,
I reversed the logic already and checked whether the rs.EOF returns true first. But it still returns a time-out error.
In another page, i used the same method to display a single set of data. It works but the link to that set of data is definitely valid.
My script is supposed to display the latest 20 items from the database. I have been cracking my head over this problem. I hope any of you could help me out on this.
Thanks
August 6, 2001 at 8:49 am
sqlst = select top 20 * from tbl_name order by tbl_object
objRec.Open sqlst, conn, adOpenStatic, adLockReadOnly, adCmdText
This is the way to do it with a sql statement. It will pull the top 20 based on what order you give it. the objRec.Open is formatted sqlst variable to hold our statement, conn tells it what open connection to use. don't forget to close your record set and your connection when you are done.
Wes
August 6, 2001 at 12:26 pm
August 6, 2001 at 1:41 pm
I've got all my statements in store procs unless the app is built for multi database systems.
Wes
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply