Re: Return value from Stored Procedure

I suspect that SQL Express has NOCOUNT off by default, at least on your
installation.

PS. Since it appears from the snippet of the stored procedure you showed in
your OP that you are not retrieving a recordset back from your procedure,
you should specif adExecuteNoRecords in the <execute options> argument of
your Execute call. Actually, you should get rid of the rsID stuff totally:
why make ADO construct a recordset when you are not retrieving records?

PPS. You should avoid @@IDENTITY, using Scope_identity instead. It will
prevent unexpected results, especially if your table has a trigger on it.

Light wrote:
After playing with the code for awhile, especially with this

Set rsID = rsID.NextRecordset

I found out that on the Sql Server 2000, I need this line, and yet on
the Express, I can do without it, what gives?

TIA


Bob Barrows [MVP] wrote:
1. When you execute a query in Query Analyzer, did you ever notice
those "x rows affected" messages? Well, it's not just QA that those
messages get delivered to. They get returned to ADO as recordsets
... 2. SQL Server does not return the output or return parameter values
until all recordsets generated by the procedure are consumed

Setting NOCOUNT to off prevents those extra resultsets from being
generated and returned.

Light wrote:

Hi Bob,

That does it, but why?

Thanks a billion, you just save my life:).

Bob Barrows [MVP] wrote:

Light wrote:


CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,


@RETURN_VALUE int OUTPUT
)
As


You need to add "SET NOCOUNT ON" to this procedure - right here as
a matter of fact.

--
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"


.

Relevant Pages

  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)