CODEWITHSUNDEEP
asp.netsqlsql-servervb.net
Chalumeau
Chalumeau

Reputation: 101

@@ROWCOUNT returns 0 on the first time

I want to know how many rows I get with this query : 

Using ConLoad As SqlConnection = New SqlConnection(constring)
     Dim Query As String = "SELECT @@ROWCOUNT AS ROWnum, *  FROM Specifications")

     ConLoad.Open()
     Using cmd As SqlCommand = New SqlCommand(Query, ConLoad)
         Response.Write(cmd.ExecuteScalar)
     End Using
End Using

This query executed in SQL Server Management Studio returns 38, but in my webform it's returning 0.

I improve to launch it twice:

Using ConLoad As SqlConnection = New SqlConnection(constring)
    Dim Query As String = "SELECT @@ROWCOUNT AS ROWnum, *  FROM Specifications")

    ConLoad.Open()
    Using cmd As SqlCommand = New SqlCommand(Query, ConLoad)
        Response.Write(cmd.ExecuteScalar)
    End Using
    Using cmd2 As SqlCommand = New SqlCommand(Query, ConLoad)
        Response.Write(cmd2.ExecuteScalar)
    End Using
End Using

And the result on cmd2.ExecuteScalar is 38.

How I have to do to have the good result at the first time?

Upvotes: 0

Views: 327

Answers (2)

user2864740
user2864740

Reputation: 61925

From the documentation:

@@ROWCOUNT Returns the number of rows affected by the last statement ..

Generally COUNT(*) is used to determine the number of results, where the result-set is not itself desired.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34784

@@ROWCOUNT is populated as the result of a query, not used within a query, you could use COUNT(*):

SELECT COUNT(*)
FROM Specifications

Or:

SELECT *
FROM Specifications
PRINT @@ROWCOUNT

Upvotes: 2

Related Questions