Solved

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

Posted on 2013-12-08
7
1,106 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

732 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