asked on

How to Return Id of the Record from Stored Proc Using ASP.NET and SQL Server 2005

Helo Experts,

I am adding customer record to the database using the stored procedure below

CREATE PROCEDURE [dbo].[procAddRegistrationDetails]
      -- Parameters
      @GiftCodeID CHAR(16),
      @CertRegTitle VARCHAR(10),
      ...
....
      @UserTypeID      CHAR(10)
AS

BEGIN
      DECLARE @CustID      INT
      DECLARE @RowID      INT

      INSERT INTO Customer(CustTitle, CustFirstName, CustLastName, CustAddr1, CustAddr2, CustAddr3, CustCity, CustCounty, CustPostCode, CustCountryID, CustEmail, CustPhone)
      VALUES (@CustTitle, @CustFirstName, @CustLastName, @CustAddr1, @CustAddr2, @CustAddr3, @CustCity, @CustCounty, @CustPostCode, @CustCountryID, @CustEmail, @CustPhone)

      DECLARE cur_CustID CURSOR STATIC FOR
      SELECT SCOPE_IDENTITY() AS CustID

      OPEN cur_CustID
      FETCH NEXT FROM cur_CustID INTO @CustID

      CLOSE cur_CustID
      DEALLOCATE cur_CustID;

-- Update the GiftCertificate with the Registration details
      DECLARE @StatusID      CHAR(30)
      DECLARE @GiftType      CHAR(2)

      SET @GiftType = LEFT(@GiftCodeID, 2)

--       UPDATE FINISH

END

and submitting data to the above SP from the ASP.Net page using below

      ' connection and necessary statements here
      cmd.CommandText = "procAddRegistrationDetails"
cmd.CommandType = CommandType.StoredProcedure

'set up which parameters to pass
cmd.Parameters.Add("@GiftCodeID", SqlDbType.Char, 16)
cmd.Parameters.Add("@UserID", SqlDbType.Char, 30)
cmd.Parameters.Add("@UserTypeID", SqlDbType.Char, 10)
' ......
       cmd.ExecuteNonQuery()

All I wanted to do is to return the CustId from the SP to some variable

SessionData.CustomerID = cmd.ExecuteNonScalar ?????? I tried this but did not work.

Please can someone advise me here?

Many thanks for your help

Regards
Kay

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

TSmooth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

learningnet

ASKER

thanks both for your comments,

I have added the following in the Stored Procedure as CustID was already been used for something else

DECLARE @CustIdOut INT OUTPUT
SET @CustIdOut = SCOPE_IDENTITY()

Please can you advise how about adding and setting the output diretion on the command object

cmd.Parameters.Add("@CustIdOut", SqlDbType.Int, 100)

cmd.ExecuteNonQuery()
Dim newID As Integer = Cint(cmd.Parameters("@CustID").Value)

Thanks for your help

TSmooth

cmd.Parameters("@CustIdOut").Direction = ParameterDirection.Output

learningnet

ASKER

I have used this but it gave me an error
cmd.Parameters("@CustIdOut").Direction = ParameterDirection.Output

is this not correct?

learningnet

ASKER

great it works now !! little typo my bad...

thanks for your help !!