tags:

views:

85

answers:

3
public static int MassEmpNumUpdate(string empToUpdateFrom, string empToUpdateTo)
{
    string sql;
    int retval;
    using (cn = new SqlConnection(ConnectionString()))
    {
        cn.Open();
        sql = "uspUpdateDet"; // THIS IS THE 1ST SP
        using (cmd = new SqlCommand(sql, cn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@EmpToUpdateFrom", empToUpdateFrom);
            cmd.Parameters.AddWithValue("@EmpToUpdateTo", empToUpdateTo);
            cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            cmd.ExecuteNonQuery();

            retval = (int)cmd.Parameters["@ReturnValue"].Value;

        }

        if (retval == 0)
        {
            sql = "uspUpdatePrev"; // THIS IS THE 2ND SP - not working :(
            using (cmd = new SqlCommand(sql, cn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EmpToUpdateFrom", empToUpdateFrom);
                cmd.Parameters.AddWithValue("@EmpToUpdateTo", empToUpdateTo);
                cmd.Parameters.Add("@returnvalue", SqlDbType.Int).Direction =   ParameterDirection.ReturnValue;
                return cmd.ExecuteNonQuery();
            }
        }

        return retval;
    }

}

}

I have 2 stored procedure (that will update 2 tables) the uspUpdateDet and uspUpdatePrev. My question is why is my second (uspUpdatePrev) statement is not working. btw the sp is working right from sql server 08.

SP1:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go

ALTER PROCEDURE [dbo].[uspUpdateDet]

 @EmpToUpdateFrom varchar(7)
, @EmpToUpdateTo varchar(7)

AS SET NOCOUNT ON;

DECLARE @affectedRows int SET @affectedRows = 0;

BEGIN UPDATE tbl1 SET DET1 = @EmpToUpdateTo WHERE DET1 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET DET2 = @EmpToUpdateTo WHERE DET2 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L1 = @EmpToUpdateTo WHERE L2 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L2 = @EmpToUpdateTo WHERE L2 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L3 = @EmpToUpdateTo WHERE L3 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L4 = @EmpToUpdateTo WHERE L4 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L5 = @EmpToUpdateTo WHERE L5 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L6 = @EmpToUpdateTo WHERE L6 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L7 = @EmpToUpdateTo WHERE L7 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

BEGIN UPDATE tbl1 SET L8 = @EmpToUpdateTo WHERE L8 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END

return @affectedRows

+1  A: 

First guess is that your using statement in the first block is closing your connection. What error are you getting?

Edit: After looking at this question it seems like this does not happen so the best guess would be that you're not hitting the block at all. Are you sure that's happening?

Jon
I am not getting an error is just a message: no records were updated, but there are some records exist.
Kombucha
If you profile your database does the SP run?
Jon
yes it runs correctly in sql server 08
Kombucha
Jon, I believe you are right so should i not use -using-?
Kombucha
+1 good eye Jon
Kombucha
+4  A: 

I don't know what you mean by "not working", but most likely retval is not 0.

You really ought to debug the application and see what you are getting back from the first call.

Andrew Hare
A: 

Is it possible that (retalValue != 0) ?

Can you post the code of your 1st SP?

Max