Solved

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

Posted on 2008-10-15
6
221 Views
Last Modified: 2010-08-05
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            
0
Comment
Question by:learningnet
  • 3
  • 2
6 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 300 total points
ID: 22719487
add to your create procecdure the following parameter:
@UserTypeID      CHAR(10),
@custId int output

in your code replace the cursor part with this: (you don't declare @custId in your procedure code)
replace
      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;
with
set @custId = SCOPE_IDENTITY()

that's it

when you call your procedure add the @custId parameter and make sure you define it's direction as output
after the procedure call will return, you will have the value in the parameter and you can copy it to your variable (from the value attribute)
0
 
LVL 16

Assisted Solution

by:TSmooth
TSmooth earned 200 total points
ID: 22719518
Add another parameter called something like "@CustID" to your stored procedure and set it's direction to OUTPUT by using a line like the following:

"@CustID int OUTPUT"

and add a SET @CustID = Scope_Identity() in the stored procedure if you don't already have one.

Then in your asp.net code, add the parameter to your cmd object's parameters collection and set that parameter's "Direction" property to "Output". Now once you call cmd.ExecuteNonQuery(), you can check the value of the new output parameter afterwards:

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

Author Comment

by:learningnet
ID: 22720916

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

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 16

Expert Comment

by:TSmooth
ID: 22720934
cmd.Parameters("@CustIdOut").Direction = ParameterDirection.Output
0
 

Author Comment

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

is this not correct?

0
 

Author Comment

by:learningnet
ID: 22721075
great it works now !! little typo my bad...

thanks for your help !!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now