Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Return scope_identity

I am somewhat embarrassed that I am having a problem with this...but regardlesss.

I am submitting data through a insert on a stored procedure and need to return the value into a variable. I keep getting an empty string value.

#### Stored Procedure #####
CREATE PROC spIns_OrgAddressContact

    @OrgName varchar(50),
    @Address1 varchar(50),
    @Address2 varchar(50),
    @City varchar(50),
    @State varchar(50),
    @ZipCode varchar(50),
    @OrgPhone varchar(50),
    @OrgFax varchar(50),
    @FName varchar(50),
    @LName varchar(50),
    @Title varchar(50),
    @Phone varchar(50),
    @Email varchar(50)

AS

    Declare    @OrgId int    /*primary key for org*/
    Declare    @AddressId int    /*primary key for address*/

Begin Tran

    INSERT INTO organizations (name) VALUES (@OrgName)
    set @OrgId = SCOPE_IDENTITY()

    INSERT INTO Addresses (OrganizationId, Address1, Address2, City, State, ZipCode, Phone, Fax) VALUES(@OrgId, @Address1, @Address2, @City, @State, @ZipCode, @OrgPhone, @OrgFax)
    set @AddressId = SCOPE_IDENTITY()

    INSERT INTO Contacts (OrganizationId, AddressId, FirstName, LastName, Title, Phone, Email, PrimaryContact) VALUES(@OrgId, @AddressId, @FName, @LName, @Title, @Phone, @Email, '1')

Commit Tran

    RETURN @OrgId
GO


#### Code Behind ####
...
strComm = "spIns_OrgAddressContact"
Dim objComm As New SqlCommand(strComm, objConn)
        objComm.CommandType = CommandType.StoredProcedure
With objComm
    .Parameters.Add("@OrgName", txtOrgName.Text)
    .Parameters("@OrgName").DbType = DbTye.String
    .Parameters.Add("@Address1", txtAddress1.Text)
    .Parameters("@Address1").DbType = DbType.String
    .Parameters("@Phone").DbType = DbType.String
    .Parameters.Add("@Email", txtEmail.Text)
    .Parameters("@Email").DbType = DbType.String
...
End With

objConn.Open()
txtOrgName.Text = objComm.ExecuteScalar()
objConn.Close()

Suthern
Monday, May 16, 2005

From the .NET documentation regarding the return value of the ExecuteScalar() method:
    The first column of the first row in the result set, or a null reference if the result set is empty.

You don't have a resultset in your stored procedure - there is no SELECT statement. The RETURN keyword does not create a resultset to send back to the client - it only sets the return parameter.

For the easiest "fix", change this line in your stored procedure:
RETURN @OrgId
to this:
SELECT @OrgId

That's not the most efficient means, but it's the easiest. Google for "C# SQL return parameter" if you'd like to learn more about using return parameters, which would be more efficient.

Good luck.

Ryan LaNeve
Monday, May 16, 2005

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

Dennis Forbes
Monday, May 16, 2005

Use an Output parameter

It's really the only sane option

Dan G
Tuesday, May 17, 2005

+1 for Dan G's suggestion of using an output parameter.

~Joel Spolsky
Wednesday, May 18, 2005

replace "RETURN @OrgId" with "SELECT @OrgId" and use SqlCommand.ExecuteScalar.

Tien on Software
Tuesday, May 24, 2005

What's wrong with using ExecuteNonQuery and then simply reading the return value from
      .Parameters("@RETURN_VALUE").Value
?

Hardin
Thursday, May 26, 2005

*  Recent Topics

*  Fog Creek Home