Solved

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

Posted on 2013-12-08
7
1,032 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

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 82

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now