Returning SQL Server Messages to ASP

[Brian Piotrowski]

Hi all,

Is there a way I can return an SQL server response message through an ASP
page?

Right now when I run a command through the Query Analyzer (such as an
insert, update, delete, etc. statement), I will get a message in the
response window (ie: 1 row(s) affected). What is the variable that I would
need to add to my ASP page to show this result.

I'm not interested in returning data, just this response to an SQL command.

Thanks,

Brian.
(remove NOSPAM to email me directly)

[Zenobia]

On Wed, 23 Jun 2004 12:32:34 -0400, "Brian Piotrowski"
<bp*********@NOSPAM.simcoeparts.com> wrote:

Hi all,

Is there a way I can return an SQL server response message through an ASP
page?

Right now when I run a command through the Query Analyzer (such as an
insert, update, delete, etc. statement), I will get a message in the
response window (ie: 1 row(s) affected). What is the variable that I would
need to add to my ASP page to show this result.

I'm not interested in returning data, just this response to an SQL command.

Thanks,

Brian.
(remove NOSPAM to email me directly)

This message returned from QA depends upon the SQL Server system
variable @@RowCount which returns the number of rows affected by
the last statement.

For instance if you were querying the Pubs database in QA with:

SELECT * FROM Authors WHERE state = 'CA'

The message you get is:

15 row(s) affected

This can be simulated with:

Select * From Authors where state = 'CA'
Select CAST(@@rowcount AS VarChar) + ' row(s) affected'

So I guest you would just return the last row of your SProc.

I think you should ask SQL Server specific questions in a more
relevant ng such as news:microsoft.public.sqlserver.misc

[Raymond Lewallen]

Select * From Authors where state = 'CA'
Select CAST(@@rowcount AS VarChar) + ' row(s) affected'

So I guest you would just return the last row of your SProc.

Brian, instead of doing this, modify it a bit so you can return a scalar
value as an output parameter back instead of having to open a datareader or
dataset and go into the items collection to get the value.. thats too much
work.

create proc MyProc @state char(2), @outputVar varchar(30) OUTPUT

Select * From Authors where state = @state

set @outputVar = CAST(@@RowCount As VarChar(10)) + ' row(s) affected.'

Return
Go