Return value from Stored Procedure

Hi All,

I've posted this question on the aspnet NG where I got some useful responses but they've yet solved my problem, then I find this classic asp NG, and hopefully, someone can spot the error right away.

I inherited some legacy ASP codes in my office (I am a total newbie in ASP and SQL). The original code's back-end is using the SQL Server 2000 and I am testing to use it on the Express edition. In brief, the code works with the SQL Sever 2000 but not in the Express Edition. The table I want to create is created correctly in the Express sever also.

The problem is describes as follows.

One of the pages is using a stored procedure to create a new table and
return the value of the new table ID.

The codes on the .asp file are written in VBScript, briefly, it is as follows:

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection= dbConnection
cmd.CommandText = "NewCompany"
cmd.CommandType = adCmdStoredProc

cmd.Parameters("@varCompanyName") = Request.Form("COMPANYNAME")
:
:
cmd.Parameters("@RETURNVALUE") = 0 'initialized to 0

Set rsID = cmdCom.Execute
Set rsID = rsID.NextRecordset
ID = cmdCom.Parameters("@RETURNVALUE")

On the SQL Server 2000, the ID is returned correctly, but the Express
doesn't. In fact, it didn't fill in the vat all. For example, If I set the RETURNVALUE to -5 before the execute, the new value remains -5. And I could see the table is created correctly with the proper id. It is just the return value received at the client web
browser is never set.

Here is the shorten version of the stored procedure code:

CREATE Procedure NewCompany
(
@varCompanyName varchar (50) = null,
:
:
@RETURN_VALUE int OUTPUT
)
As
INSERT INTO tblCompany(varCompanyName)
values
(@varCompanyName )
SELECT @RETURN_VALUE = @@identity /* Scope_identity() was also tried, same problem */
return @RETURN_VALUE

So my question is, is there any change I need to make for the Express's
stored procedure?

Any suggestion is highly appreciated. This problem is kind of important as a lot of our customers are migrating their SQL servers from 2000 to the 2005. I've Googled far and wide for a solution and nothing came out of it.

TIA
.

Relevant Pages

  • Connection.Execute and SQL stored procedures
    ... ..asp, the connection object and MS SQL Server as the DB can help me out. ... Five of the six calls execute as expected. ... This particular stored procedure does quite a bit of work within the DB, ...
    (microsoft.public.sqlserver.odbc)
  • Re: ASP and SQL
    ... run a stored procedure in an SQL server from a asp page. ... Is there a way to create a user on an SQL server from a ASP page, ... You will need to use an account with sysadmin privileges in order to execute ...
    (microsoft.public.inetserver.asp.db)
  • Re: @@IDENTITY in SQL server
    ... Aaron Bertrand [SQL Server MVP] wrote: ... First, use a stored procedure, second, use SCOPE_IDENTITY (I assume SQL ... And I don't know what the point of "SET NOCOUNT ... Better yet, use an OUTPUT paremeter to send the IDENTITY value back to ASP, ...
    (microsoft.public.inetserver.asp.db)
  • SQL/ASP DB Locking/updating tables
    ... MS SQL Server 2k Dev ed ... IIS 5, ASP classic. ... Now the above ASP page calls a Stored procedure before displaying some ... does some calculations involving several tables bases on the old ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with the Legacy ASP files and the Sql Server Express
    ... you have to set the Express product to accept non-local connections and enable some other connection protocol than shared memory. ... Then, check the stored procedure and make sure it is using RETURN @value, or similar. ... I inherited some legacy ASP codes in my office. ... On the SQL Server 2000, the ID is returned correctly, but the Express ...
    (microsoft.public.dotnet.framework.aspnet)