Multiple Statements executed in a single step

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Remy - 20 May 2008 01:15 GMT

I have been working since 10am this morning (now after 1am in UK) on this. I
can't see what the problem is.

I have a classic ASP application with a development database in MS SQL
Express (All SPs applied). I have one occassasion where I would like to run
an SQL Update statement and a select statement to retreive the rows affected.
Code (abstracted):

strSQL = "update test set testID = 1 where testID = 1; select @@rowcount as
'RowsAffected';

I am trying to retrieve the 'RowsAffected' into a recordset and have tried
rs.open and conn.execute methods. Regardless of what I try (providers,
cursors etc.,) the result is always the same, the first statement executes
and the 2nd doesn't. If I invalidate the second statement (malformed SQL) the
expected error is thrown so its being parsed. I have changed the second
statement to a simple select and its still ignored.

I can't for the life of me see what the issue is, I'm sure I did this years
ago. This is time critical for me and I would be overjoyed if someone could
help.

Thanks in advance.

Reply to this Message

Remy - 20 May 2008 01:17 GMT

Should also have stated that the sql statements run fine in T-SQL query
window so it looks like an issue with ADO/Providers or something? I just
spend 2 hours doing a full windows update 1.5GB of Service Packs etc., and
still no joy :(

Reply to this Message

Bob Barrows [MVP] - 20 May 2008 01:51 GMT

> I have been working since 10am this morning (now after 1am in UK) on
> this. I can't see what the problem is.
[quoted text clipped - 20 lines]
>
> Thanks in advance.

Change it to

strSQL = "SET NOCOUNT ON;" & _
"update test set testID = 1 where testID = 1; " & _
"select @@rowcount as 'RowsAffected';"

The update statement generates an informational "x rows affected" message
which is returned as a closed recordset, resulting in two recordsets
instead of one. You have two options:

1. use NextRecordset to see the second recordset containing the rowcount,
2. recommended, suppress the informational message by turning NOCOUNT on
(and no, this has no effect on the @@rowcount variable - try it and see)

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Remy - 20 May 2008 09:44 GMT

It was staring me in the face, I can't thank you enough Bob. Have a great day!

> > I have been working since 10am this morning (now after 1am in UK) on
> > this. I can't see what the problem is.
[quoted text clipped - 34 lines]
> 2. recommended, suppress the informational message by turning NOCOUNT on
> (and no, this has no effect on the @@rowcount variable - try it and see)

Reply to this Message

Bob Barrows [MVP] - 20 May 2008 13:45 GMT

I have to ask why you aren't encapsulating this code into a stored
procedure?

> It was staring me in the face, I can't thank you enough Bob. Have a
> great day!

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

Evertjan. - 20 May 2008 18:28 GMT

=?Utf-8?B?UmVteQ==?= wrote on 20 mei 2008 in
microsoft.public.inetserver.asp.db:

> I have been working since 10am this morning (now after 1am in UK) on
> this. I can't see what the problem is.

Seems you have been working nearly minus 9 hours?
That would be a problem in a single statement?

Signature

Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Reply to this Message

Old Pedant - 26 May 2008 05:32 GMT

<%
...
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "... your connection string ..."

SQL = "update test set testID = 1 where testID = 1"
affected = -1
conn.Execute SQL, affected

Response.Write affected & " records updated"
...
%>

You don't *have* to initialize the "affected" variable before doing the
execute--just DIM'ing it will do the trick--but I like to do that as a sanity
check. You can even initialize it to
affected = "Something went wrong"
if you want, since all VBS variables are Variants.

Reply to this Message

Webmaster Forum / ASP / Database Access / May 2008

Multiple Statements executed in a single step