Problem With Getting Last Record From Stored Procedure

I have three tables in this shipping scenario -- one to record the Sender's Info, one to record the Recipient's Info, and one to record the Shipment Info, and I perform the inserts in that order.  I want to get the last inserted USERID (which is an INT, and the PK) from both the Sender's table and the Recipient's table so that I can record that info as a foreign key in my Shipment table. 

Everything compiles and runs.  However, every time I run it, "2" is inserted in my foreign key columns in the Shipment Info table.  I can't figure out where that 2 is coming from.

So I analyzed the SP in query analyzer, and when I run it it shows:

"(0 row(s) returned)
@RETURN_VALUE=0"

Can anyone help me with this?  I'm so close I think:

First, my SP's:

***********************************************************

ALTER PROCEDURE dbo.INSERT_NEW_SENDER
(
@firstname nvarchar(50),
@lastname nvarchar(50),
@email nvarchar(50),
@phone nvarchar(50)
)
AS
BEGIN TRANSACTION
DECLARE @NewID int
SET NOCOUNT ON
INSERT INTO SHP_USER(email, firstname, lastname, phone) VALUES(@email, @firstname, @lastname, @phone)
SET @NewID = CONVERT(int, SCOPE_IDENTITY())
COMMIT TRANSACTION
GO

**************************************************************

ALTER PROCEDURE dbo.SP_INSERT_NEW_RECIPIENT
(
@firstname nvarchar(50),
@lastname nvarchar(50),
@company nvarchar(50),
@division nvarchar(50),
@address1 nvarchar(50),
@address2 nvarchar(50),
@city nvarchar (50),
@state nvarchar (50),
@zip nvarchar (50),
@country nvarchar(50),
@phone nvarchar(50),
@fax nvarchar(50),
@email nvarchar(50),
@address_type nvarchar(50),
@recipient_type nvarchar(50),
@NewID int OUTPUT
)
AS
/* SET NOCOUNT ON */
INSERT INTO SHP_recipient(firstname, lastname, company, division, address1, address2, city, state, zip, country, phone, fax, email, address_type, recipient_type) VALUES(@firstname, @lastname, @company, @division, @address1, @address2, @city, @state, @zip, @country, @phone, @fax, @email, @address_type, @recipient_type)
SET @NewID = CONVERT(int, SCOPE_IDENTITY())
RETURN

**********************************************************************

And my .NET stuff:

//NEW USER INSERT SqlCommand myCommand;

SqlDataReader myReader; int sender_userid = 0;

int recipient_userid = 0;

//int NewID; myCommand = new SqlCommand("INSERT_NEW_SENDER", conn);

myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@firstname", str_firstname));

myCommand.Parameters.Add(new SqlParameter("@lastname", str_lastname));myCommand.Parameters.Add(new SqlParameter("@phone", str_phone));

myCommand.Parameters.Add(new SqlParameter("@email", str_email));myCommand.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

myCommand.Connection.Open();

myReader = myCommand.ExecuteReader();sender_userid = (int)myCommand.Parameters["@NewID"].Value;

myCommand.Connection.Close();

//RECIPIENT INSERT SqlCommand myCommand2;

SqlDataReader myReader2; myCommand2 = new SqlCommand("SP_INSERT_NEW_RECIPIENT", conn);

myCommand2.CommandType = CommandType.StoredProcedure;

//myCommand2.Parameters.Add(new SqlParameter("@userid", recipient_userid));myCommand2.Parameters.Add(new SqlParameter("@firstname", str_recip_firstname));

myCommand2.Parameters.Add(new SqlParameter("@lastname", str_recip_lastname));myCommand2.Parameters.Add(new SqlParameter("@company", str_company));

myCommand2.Parameters.Add(new SqlParameter("@division", str_division));myCommand2.Parameters.Add(new SqlParameter("@address1", str_recip_addr1));

myCommand2.Parameters.Add(new SqlParameter("@address2", str_recip_addr2));myCommand2.Parameters.Add(new SqlParameter("@city", str_city));

myCommand2.Parameters.Add(new SqlParameter("@state", str_state));myCommand2.Parameters.Add(new SqlParameter("@zip", str_zip));

myCommand2.Parameters.Add(new SqlParameter("@country", str_country));myCommand2.Parameters.Add(new SqlParameter("@phone", str_recip_phone));

myCommand2.Parameters.Add(new SqlParameter("@fax", str_fax));myCommand2.Parameters.Add(new SqlParameter("@email", str_recip_email));

myCommand2.Parameters.Add(new SqlParameter("@recipient_type", str_recipient_type));myCommand2.Parameters.Add(new SqlParameter("@address_type", str_Address_Type));

myCommand2.Parameters.Add(new SqlParameter("@NewID", ParameterDirection.Output));myCommand2.UpdatedRowSource = UpdateRowSource.OutputParameters;

myCommand2.Connection.Open();

myReader2 = myCommand2.ExecuteReader();recipient_userid = (int)myCommand2.Parameters["@NewID"].Value;

myCommand2.Connection.Close();

try

{

conn.Open();

//INSERT data into the SHP_SHIPMENTS tableSqlCommand cmd = new SqlCommand("INSERT INTO SHP_SHIPMENTS(sender, recipient, ship_method, rma, payment_method, must_pay, project, business_unity, office, attachments, shipment_description,shipment_notes,date_created) VALUES(@sender_userid, @recipient_userid, @ship_method,@rma,@payment_method,@must_pay,@project,@business_unity,@office,@attachments,@shipment_description,@shipment_notes, GetDate())", conn);

 cmd.Parameters.Add(new SqlParameter("@sender_userid", sender_userid));

cmd.Parameters.Add(new SqlParameter("@recipient_userid", recipient_userid));cmd.Parameters.Add(new SqlParameter("@ship_method", str_ship_method));

cmd.Parameters.Add(new SqlParameter("@rma", str_rma));cmd.Parameters.Add(new SqlParameter("@payment_method", str_payment_method));

cmd.Parameters.Add(new SqlParameter("@must_pay", str_must_pay));cmd.Parameters.Add(new SqlParameter("@project", str_project));

cmd.Parameters.Add(new SqlParameter("@business_unity", str_business_unit));cmd.Parameters.Add(new SqlParameter("@office", str_office));

cmd.Parameters.Add(new SqlParameter("@attachments", str_attachments));cmd.Parameters.Add(new SqlParameter("@shipment_description", str_shipment_description));cmd.Parameters.Add(new SqlParameter("@shipment_notes", str_shipment_notes));

cmd.ExecuteNonQuery();

 

}catch (Exception ex)

{FormMessage.Text = "Error: Insert Shipment - " + ex.Message;

}

finally

{

conn.Close();

}