Solved

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

Posted on 2013-12-08
7
1,121 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 53

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 27

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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