@@ROWCOUNT problem

  • 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"

  • 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

  • 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.

  • 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.

  • I've used both of these suggestions, though developers usually prefer burthold's suggestion of using the ADO value.

    Steve Jones

    steve@dkranch.net

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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"

  • 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

  • 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

  • I like the last post, though I'd move it to a stored proc.

    Steve Jones

    steve@dkranch.net

  • 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