Solved

Classic ASP does not let me return @@ROWCOUNT from SQL SERVER

Posted on 2013-12-08
7
1,078 Views
Last Modified: 2013-12-09
I have a classic ASP statement that throws the following (via ADO) to SQL Server:

SELECT @@ROWCOUNT AS NoRecords,
SP.SpeakerId,
SP.Title,
SP.FirstName,
SP.LastName,
SP.Letters,
SP.Tags,
SP.SpeakerReference
FROM Speaker SP 
INNER JOIN Client CL ON CL.ClientId = SP.ClientId 
WHERE 
	(
		(
			(SP.Title LIKE '%a%' AND SP.Title <> 'Mr' AND SP.Title <> 'Ms') OR 
			(SP.FirstName LIKE '%a%') OR 
			(SP.LastName LIKE '%a%') OR 
			(SP.Letters LIKE '%a%')
		) AND
		(
			(CL.FirstName LIKE '%fay%') OR 
			(CL.LastName LIKE '%fay%') OR 
			(CL.CompanyName LIKE '%fay%') OR 
			(SP.Biography LIKE '%fay%') OR 
			(CL.Email LIKE '%fay%')
		)
	) 
ORDER BY SP.FirstName, SP.LastName; 

Open in new window


In SQL Server Management Studio I get the Number of records correctly returned as 'NoRecords' in my results.

If I run the same statement via VBScript/ASP/ADO I always get '0'.

Can someone please shed some light on this?

Regards,
0
Comment
Question by:splanton
7 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39704413
Is this a stored procedure?  If it is, you would call it like this example (using your own parameters) http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28234393.html#a39477170

response.write(ALtest("abc123"))

Function ALtest(stockCode)


	Set cmd = Server.CreateObject("ADODB.Command")
With cmd
   ' Set up DB connection to use, set the type of SQL command
   	.ActiveConnection = MyConnectionString
   	.CommandType = adCmdStoredProc
   	.CommandText = "ALtest" 
   
   	.Parameters.Append .CreateParameter("@p_StockCode",adVarChar, adParamInput,50)
   	.Parameters("@p_StockCode") = stockCode

 
   set rs = .Execute
End With
 
  ALtest= rs(0)

set cmd = nothing
set rs = nothing

end Function

Open in new window

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 350 total points
ID: 39704437
@@ROWCOUNT returns the number of rows returned by the last statement that executed, not the current one.

If you ran your query as a single statement, followed by SELECT @@ROWCOUNT, then you would get the correct value. As is, you should be using COUNT(*) instead of @@ROWCOUNT.
0
 
LVL 2

Author Comment

by:splanton
ID: 39704439
No it is NOT a stored Procedure.

As I said in my original post If you run the code against MY database in SQL Server MS I get NoRecord = 10 (in this particular case).

When I try and execute the SAME statement from Classic ASP (via ADO) it always returns 0 in NoRecords.

This highlights a difference in the way MS handles the statement and ADO handles the statement - I want to know how to get this statement working in Classic ASP so that it returns the correct number of rows.

Regards,
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39704465
It appears that @@ROWCOUNT can return a different value depending on the statements it is used with.  http://technet.microsoft.com/en-us/library/ms187316.aspx

Which driver / provider are you using?
0
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 75 total points
ID: 39704501
For what it's worth, I tried adding @@ROWCOUNT to one of my working ASP/MSSQL pages and all I got was 0's.
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 75 total points
ID: 39705051
I don't know how you fooled yourself into thinking @@ROWCOUNT worked that way in management studio, as stated above @@ROWCOUNT is always the results of the previously run statement, never of the current one.  Try the be test below in management studio
SELECT 'Bet you it is 1 no matter how many tables you have!'

SELECT @@rowcount AS NoRecords, * 
FROM sys.tables T

SELECT 'And this one'
UNION
SELECT 'Will be a count of 2.'

SELECT @@rowcount AS NoRecords, * 
FROM sys.tables T

Open in new window

If you are not on an ancient version of SQL, this should work for you:
SELECT COUNT(*) OVER () AS NoRecords, * 
FROM sys.tables T

Open in new window

0
 
LVL 2

Author Closing Comment

by:splanton
ID: 39705901
Looks like I got 'fooled' by @@ROWCOUNT - must have just been 'lucky' with my results I guess. :)

Thanks for the input guys - even if it is to point out I have the wrong end of the stick - learning is fun!

Will be using count() instead with a group by.

Many thanks and kind regards.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question