470,594 Members | 1,427 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

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

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!
Jul 23 '05 #1
2 1344
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/
Jul 23 '05 #2
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.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by JohnS | last post: by
2 posts views Thread by Dean | last post: by
13 posts views Thread by Kai Grossjohann | last post: by
27 posts views Thread by C Gillespie | last post: by
5 posts views Thread by srini.venkatesan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.