getting stored procedure insert return the primary key in C#.Net

So, all I wanted to do was to insert a row in the database and get the primary key of thus inserted row.

C# decided not to help.

Here is how I did it:

first, wrote a stored procedure to add the row to the table



/****** Object:  StoredProcedure [dbo].[AddMember]    Script Date: 09/28/2010 20:28:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddMember]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[AddMember]
GO




/****** Object:  StoredProcedure [dbo].[AddMember]    Script Date: 09/28/2010 20:28:25 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


-- ########################################################################
-- # STORED PROCEDURES                                     
-- ########################################################################


CREATE PROCEDURE [dbo].[AddMember]
           @SALUTATION varchar(10),
           @FIRST_NAME varchar(20),
           @MIDDLE_NAME varchar(20),
           @LAST_NAME varchar(25),
  @Person_ID int OUTPUT
AS
BEGIN


SET NOCOUNT ON


INSERT INTO [IAIPMD].[dbo].[Members]
           ([SALUTATION]
           ,[FIRST_NAME]
           ,[MIDDLE_NAME]
           ,[LAST_NAME])
     VALUES( @SALUTATION
           ,@FIRST_NAME
           ,@MIDDLE_NAME
           ,@LAST_NAME)
-- Only grab the scope identity if the INSERT worked
IF @@ROWCOUNT = 1
SET @Person_ID  =CAST (@@Identity as int)

SET NOCOUNT OFF
END


Then came the fun part, executing the procedure from C#. This is my solution


    try
            {
                SqlConnection conn = new SqlConnection(string);
                SqlCommand cmd = new SqlCommand("AddMember", conn);
                cmd.CommandType = CommandType.StoredProcedure;


                cmd.Parameters.AddWithValue("@SALUTATION", SALUTATION.Items[SALUTATION.SelectedIndex].Value);
                cmd.Parameters.AddWithValue("@FIRST_NAME", FIRST_NAME.Text);
                cmd.Parameters.AddWithValue("@MIDDLE_NAME", MIDDLE_NAME.Text);
                cmd.Parameters.AddWithValue("@LAST_NAME", LAST_NAME.Text);
//this is the fun and tricky part... pay attention here 
                SqlParameter paramter1 = cmd.Parameters.Add("@Person_ID", SqlDbType.Int);
                paramter1.Direction = ParameterDirection.Output;
                
                //The following value is now 6, the number of records inside the table
                conn.Open();


                cmd.ExecuteNonQuery();
                personID = (int)paramter1.Value; 
                Response.Write(personID);
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
                lblShowResults.Text = "

Successful Addition

The member  has been added successfully
";
               


            }
            catch (Exception ex)
            {


                lblShowResults.Text = "

Error

Member could not be added
";


            }
 


Hope I remember this the next time I struggle with weird stored procedures and almost am ready to kick the computer into submission.... Hey, it worked this time !!!!