Return Parameter in ApplicationBlocks.Data ExecuteScalar

[Guest]

All,

I am writing a form field application for user data with firstname,
middlename, lastname, department. I have written an Add/Edit Stored Procedure
in SQL 2000 which checks if an ID exists, if so it backs up existing and
updates, then is supposed to return the ID back and a status ID, if it does
not exist it returns the idenentity and a status. Currently it is returning
the status, but not the newID, which is passed as an output parameter, it is
only returning a 0, but in SQL query analizer, it returns the correct value.
What am I doing wrong so I can get it to return correctly?

Here is the code for the procedure.
--------------------
ALTER PROCEDURE sp_EditAuthorization
@ID AS INTEGER,
@FIRSTNAME as VARCHAR(100),
@MIDDLENAME AS VARCHAR(100),
@LASTNAME AS VARCHAR(100),
@AUTHID AS INTEGER,
@NEWID AS INTEGER OUTPUT
AS
DECLARE @STATUS AS INTEGER
IF @ID > 0 --Begin Edit
BEGIN
SELECT @NEWID = @ID
INSERT INTO tblAuthorizationArchive (ID, FIRSTNAME, MNAME, LASTNAME,
AUTHID, MOD_DATE, CONTENT_TYPE, FILENAME, AUTH_FORM)
SELECT ID, FIRSTNAME, MNAME, LASTNAME, AUTHID, MOD_DATE,
CONTENT_TYPE, FILENAME, AUTH_FORM FROM tblAuthorizations
WHERE ID = @ID
SELECT @STATUS = @@ERROR

IF @@ERROR = 0
BEGIN
UPDATE tblAuthorizations
SET FIRSTNAME = LTRIM(RTRIM(@FIRSTNAME)), MNAME =
LTRIM(RTRIM(@MIDDLENAME)),
LASTNAME = LTRIM(RTRIM(@LASTNAME)), AUTHID = @AUTHID,
MOD_DATE = GETDATE() WHERE ID = @ID
SELECT @STATUS = @@ERROR
END
END
ELSE
BEGIN
INSERT INTO tblAuthorizations (FIRSTNAME, MNAME, LASTNAME,
AUTHID, MOD_DATE)
VALUES (LTRIM(RTRIM(@FIRSTNAME)),
LTRIM(RTRIM(@MIDDLENAME)),
LTRIM(RTRIM(@LASTNAME)), @AUTHID, GETDATE())
SELECT @NEWID = @@IDENTITY
SELECT @STATUS = @@ERROR
END
RETURN @STATUS

------------

Here is the call in VB.NET
-------------------------
Public Overrides Function AddEditAuthorization(ByVal ID As Integer,
ByVal FirstName As String, ByVal MiddleName As String, ByVal LastName As
String, ByVal AuthID As String) As Integer
Try
Dim OutID As Integer
Dim NewID As Integer

OutID = SqlHelper.ExecuteScalar(ConnectionString,
"sp_EditAuthorization", ID, FirstName, MiddleName, LastName, AuthID, NewID)
Return CStr(NewID)
Catch ex As Exception
End Try

End Function
---------------
I am not handling the return of both variables out of VB yet, since NEWID is
not returning the correct value.

Any ideas?
Thank you,
Nathan

 

[Guest]

You are coding incorrectly. In order to use

OutID = cmd.ExecuteScalar()

you have to have a SELECT in your sproc:

SELECT @NewID

If you desire to get it on the return parameter, you have to set that
parameter as an output parameter instead. This means explicitly adding
parameters to the command object prior to firing off the ExecuteNonQuery or
ExecuteScalar.

Try this sproc in place of yours and see if you get your answer:

ALTER PROCEDURE sp_EditAuthorization
@ID AS INTEGER,
@FIRSTNAME as VARCHAR(100),
@MIDDLENAME AS VARCHAR(100),
@LASTNAME AS VARCHAR(100),
@AUTHID AS INTEGER,
@NEWID AS INTEGER OUTPUT
AS
DECLARE @STATUS AS INTEGER
IF @ID > 0 --Begin Edit
BEGIN
SELECT @NEWID = @ID
INSERT INTO tblAuthorizationArchive (ID, FIRSTNAME, MNAME, LASTNAME,
AUTHID, MOD_DATE, CONTENT_TYPE, FILENAME, AUTH_FORM)
SELECT ID, FIRSTNAME, MNAME, LASTNAME, AUTHID, MOD_DATE,
CONTENT_TYPE, FILENAME, AUTH_FORM FROM tblAuthorizations
WHERE ID = @ID
SELECT @STATUS = @@ERROR

IF @@ERROR = 0
BEGIN
UPDATE tblAuthorizations
SET FIRSTNAME = LTRIM(RTRIM(@FIRSTNAME)), MNAME =
LTRIM(RTRIM(@MIDDLENAME)),
LASTNAME = LTRIM(RTRIM(@LASTNAME)), AUTHID = @AUTHID,
MOD_DATE = GETDATE() WHERE ID = @ID
SELECT @STATUS = @@ERROR
END
END
ELSE
BEGIN
INSERT INTO tblAuthorizations (FIRSTNAME, MNAME, LASTNAME,
AUTHID, MOD_DATE)
VALUES (LTRIM(RTRIM(@FIRSTNAME)),
LTRIM(RTRIM(@MIDDLENAME)),
LTRIM(RTRIM(@LASTNAME)), @AUTHID, GETDATE())
SELECT @NEWID = SCOPE_IDENTITY()
SELECT @STATUS = @@ERROR
END

--Returns for the ExecuteScalar
SELECT @NEWID

RETURN @STATUS

After you see the output, you can start explicitly attaching parameters to
the command object. NewID will have to be an output parameter
(ParameterDirection = Output).

NOTE: I changed from @@IDENTITY to SCOPE_IDENTITY(). The reason is
@@IDENTITY could potentially return an incorrect value on a heavily used
system, as a new value could be added in between your insert and your setting
@NEWID.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

Thank you,
I mostly did VB 6.0 and used parameter queries to return, in .NET I am
trying using the ApplicatonBlocks.data object for simplicity and this is the
first return parameter I've done outside of datatable and datasets.

Thanks,
Nathan