Update database via stored procedure from code-behind fails to update

Question

I have worked on this, but unable to figure why my update fails. I'm not big on SQL. I wrote a simple stored procedure.

Here is the code:

CREATE PROCEDURE [dbo].sp_UpdateTeamMemberProfile
    @TeamMemberId int OUTPUT,
    @FirstName varchar(25),
    @LastName varchar(30),  
    @ContactNumber varchar (20),
    @ContactExt varchar (20) ,  
    @EmailAddress varchar(100),
    @Fax varchar (25),
    @LocationCity varchar (20),
    @LocationState varchar (20),
    @BeginWorkHrs int,
    @BeginWorkTime   varchar  (20) ,
    @EndWorkHrs  int ,
    @EndWorkTime varchar (20) ,
    @ZONE Varchar (20),
    @TeamMemberLanId varchar (20),
    @ManagerId varchar (20),
    @UpdateDate datetime
AS 
BEGIN
    SET NOCOUNT ON;

    BEGIN
       UPDATE dbo.LoanOwnerStamp
       SET FirstName = @FirstName,
           LastName = @LastName,
           ContactNumber = @ContactNumber,
           ContactExt = @ContactExt ,
           EmailAddress = @EmailAddress,
           Fax = @Fax, 
           LocationCity = @LocationCity, 
           LocationState = @LocationState,
           BeginWorkHrs = @BeginWorkHrs,
           BeginWorkTime = @BeginWorkTime,
           EndWorkHrs = @EndWorkHrs,
           EndWorkTime = @EndWorkTime, 
           Zone = @Zone,
           TeamMemberLanId = @TeamMemberLanId,
           ManagerId = @ManagerId ,
           UpdatedDate = @UpdateDate
       WHERE  
           TeamMemberLanId = @TeamMemberLanId 
           AND TeamMemberId = @TeamMemberId
     END 

     SET @TeamMemberId = SCOPE_IDENTITY()

     RETURN @TeamMemberId
 END

Now the code behind follows...

using (SqlCommand updatetLSTeamMember = new SqlCommand("sp_UpdateTeamMemberProfile", myConnection))
{
    updatetLSTeamMember.CommandType = CommandType.StoredProcedure;
    updatetLSTeamMember.Parameters.Add("@FirstName", SqlDbType.VarChar, 25).Value = txtFirstName.Text;
    updatetLSTeamMember.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value = txtLastName.Text;
    updatetLSTeamMember.Parameters.Add("@ContactNumber", SqlDbType.VarChar, 20).Value = txtContactNumber.Text;
    updatetLSTeamMember.Parameters.Add("@ContactExt", SqlDbType.VarChar, 20).Value = txtContactExt.Text;
    updatetLSTeamMember.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 100).Value = txtEmailAddress.Text;
    updatetLSTeamMember.Parameters.Add("@Fax", SqlDbType.VarChar, 25).Value = txtFax.Text;
    updatetLSTeamMember.Parameters.Add("@LocationCity", SqlDbType.VarChar, 20).Value = txtLocationCity.Text;
    updatetLSTeamMember.Parameters.Add("@LocationState", SqlDbType.VarChar, 20).Value = txtState.Text;
    updatetLSTeamMember.Parameters.Add("@BeginWorkHrs", SqlDbType.Int).Value = ddlBeginHrs.SelectedValue.ToString();
    updatetLSTeamMember.Parameters.Add("@BeginWorkTime", SqlDbType.VarChar, 20).Value = ddlBeginTime.SelectedValue.ToString();
    updatetLSTeamMember.Parameters.Add("@EndWorkHrs", SqlDbType.Int).Value = ddlEndHrs.SelectedValue.ToString();
    updatetLSTeamMember.Parameters.Add("@EndWorkTime", SqlDbType.VarChar, 20).Value = ddlEndTime.SelectedValue.ToString();
    updatetLSTeamMember.Parameters.Add("@Zone", SqlDbType.VarChar, 20).Value = ddlZone.SelectedItem.Text;
    updatetLSTeamMember.Parameters.Add("@TeamMemberLanId", SqlDbType.VarChar, 20).Value = txtTeamMemberLaniId.Text;
    updatetLSTeamMember.Parameters.Add("@ManagerId", SqlDbType.VarChar, 20).Value = gblUserId;
    updatetLSTeamMember.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = DateTime.Now.ToLocalTime();

    pID = updatetLSTeamMember.Parameters.Add("@TeamMemberId", SqlDbType.Int);  // I'm thinking this is a culprit
    pID.Direction = ParameterDirection.Output;  // are these assignment correct.

    try
    {
        if (updatetLSTeamMember.Connection.State == ConnectionState.Closed)
        {
            updatetLSTeamMember.Connection.Open();
        }

        updatetLSTeamMember.ExecuteNonQuery();  // code bombs out here 

        string idcat = updatetLSTeamMember.Parameters["@TeamMemberId"].Value.ToString();

Now my questions are in several places, is my Parameter.Direction output correct and is it correct from a stored procedure perspective?

Any help is appreciated.

Answer 1

If you want to return a value I would suggest creating another variable in the stored procedure instead of using a value that you use for input.

This is what I would do:

@returnValue int OUTPUT
@TeamMemberId int,
@FirstName varchar(25),
@LastName varchar(30),  
@ContactNumber varchar (20),
@ContactExt varchar (20) ,  
@EmailAddress varchar(100),
@Fax varchar (25),
@LocationCity varchar (20),
@LocationState varchar (20),
@BeginWorkHrs int,
@BeginWorkTime   varchar  (20) ,
@EndWorkHrs  int ,
@EndWorkTime varchar (20) ,
@ZONE Varchar (20),
@TeamMemberLanId varchar (20),
@ManagerId varchar (20),
@UpdateDate datetime

AS

BEGIN

SET NOCOUNT ON;

   UPDATE dbo.LoanOwnerStamp
   SET FirstName = @FirstName,
       LastName = @LastName,
       ContactNumber = @ContactNumber,
       ContactExt = @ContactExt ,
       EmailAddress = @EmailAddress,
       Fax = @Fax, 
       LocationCity = @LocationCity, 
       LocationState = @LocationState,
       BeginWorkHrs = @BeginWorkHrs,
       BeginWorkTime = @BeginWorkTime,
       EndWorkHrs = @EndWorkHrs,
       EndWorkTime = @EndWorkTime, 
       Zone = @Zone,
       TeamMemberLanId = @TeamMemberLanId,
       ManagerId = @ManagerId ,
       UpdatedDate = @UpdateDate
   WHERE  
       TeamMemberLanId = @TeamMemberLanId 
       AND TeamMemberId = @TeamMemberId 

 SET @returnValue= SCOPE_IDENTITY()

 RETURN @returnValue

END

And on your code behind:

EDIT:You also need to add this:

updatetLSTeamMember.Parameters.AddWithValue("@TeamMemberId",  int.Parse(txtTeamMemberId.Text)); // or wherever you are getting the team member id from.



pID = updatetLSTeamMember.Parameters.Add("@returnValue", SqlDbType.Int);
pID.Direction = ParameterDirection.Output;

string idcat = updatetLSTeamMember.Parameters["@returnValue"].Value.ToString();

NOTE: I have never used SCOPE_IDENTITY() On an Update. You might want to look into that.

Answer 2

I might be wrong but you're updating record based on where condition where you're using TeamMemberId (Output parameter), but not providing any value to it in c# side. The syntax si corect for output parameter but you're using it in where clause, what is the value,... The Scope Identity part will be executed after executing the update command

Answer 3

If you need to return something in case of success - u can use @@ROWCOUNT instead of SCOPE_IDENTITY() - it will return number of rows affected by the update.

So if 0 is returned - you will know that no records were updated.