Javascript in ASP I need a way of returning SQL message.

[http://ray1.net/]

Dear Readers.

Using Javascript in ASP I need a way of returning through ADO
(connection or recordset) the message returned via SQL when I run a
query that UPDATES, DELETES or an SP that returns a message.

So I basically when the below code gets executed I need a way of
returning the message if it's there and if not nothing.

a)
var conn = Server.CreateObject("ADODB.Connection")
conn.Open(connStr)
conn.Execute("UPDATE shoe SET size = 12"); /*returns (12 row(s)
affected)*/
conn.Close
conn = null

b)
var conn = Server.CreateObject("ADODB.Connection")
conn.Open(connStr)
conn.Execute("EXEC usp_bob"); /*SP returns Couldn't find bob or
nothing*/
conn.Close
conn = null

It's being used in a generic function, so no case specific code.
Any help or link really appreciated.

Thanks folks!

[Aaron Bertrand - MVP]

Instead of this:

conn.Execute("UPDATE shoe SET size = 12"); /*returns (12 row(s)
affected)*/

Try this:

var rs = conn.Execute("SET NOCOUNT ON; UPDATE shoe SET size = 12; SELECT
@@ROWCOUNT");
Response.Write(rs(0) + ' row(s) affected.');

You might also consider doing this in a stored procedure. Ad hoc sql is
generally not recommended.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[http://ray1.net/]

Aaron.

Thanks for your reply, but it wasn't exactly what I wanted.
I have a function called fireSQLXML(query) which in a nutshell runs a
query passed as a string from 100's of locations through out the code.
The function has some code to handle SELECT queries, and now some more
code that concatenates ‘SET NOCOUNT ON; SELECT @@ROWCOUNT' to DELETE &
UPDATE. But what I really need to get is any message returned by the
execution of a SP or some other query that isn't DELETE,UPDATE or
SELECT.

Some SP's might have a message, some don't, so I need a way of finding
out if the query returned a message or not.

In VB i usually do this with the conn.errors but I can't get this to
work correctly in JS using try/catch with conn.errors.

Thanks again for your help.

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message news:<u9**************@TK2MSFTNGP11.phx.gbl>...

Instead of this:

conn.Execute("UPDATE shoe SET size = 12"); /*returns (12 row(s)
affected)*/

Try this:

var rs = conn.Execute("SET NOCOUNT ON; UPDATE shoe SET size = 12; SELECT
@@ROWCOUNT");
Response.Write(rs(0) + ' row(s) affected.');

You might also consider doing this in a stored procedure. Ad hoc sql is
generally not recommended.