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.SpeakerReferenceFROM 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;

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.

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

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 TSELECT 'And this one'UNIONSELECT 'Will be a count of 2.'SELECT @@rowcount AS NoRecords, * FROM sys.tables T

Featured Post

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.

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.

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.