Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1207
  • Last Modified:

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

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
splanton
Asked:
splanton
3 Solutions
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
@@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
 
splantonAuthor Commented:
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
Technology Partners: 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!

 
Dave BaldwinFixer of ProblemsCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
splantonAuthor Commented:
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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now