View Single Post
  #1 (permalink)  
Old September 14th, 2006, 10:43 AM
Lalit Pradhan Lalit Pradhan is offline
Authorized User
Join Date: Jul 2005
Location: Pune, Maharasthra, India.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default ExecuteNonQuery not giving proper result

Hi everybody,
Iam facing a very strange problem regarding ExecuteNonQuery() function in my C# class library for my project.My page(ChangePassword.aspx) changes the existing user's password when the password for the existing user is entered correctly.For that I have developed an application(or just a module) based on 3 tier architecture.

On the Data layer (i.e. the database side) I wrote a stored procedure called spUserUpd which has the following code:

CREATE PROCEDURE [dbo].[spUserUpd]
     @option int = NULL,
     @userName varchar(100) = NULL,
     @userPassword varchar(50) = NULL,
     @newPassword varchar(50) = NULL


IF (@option = 3)
    IF EXISTS(SELECT UserName FROM dbo.tbUsers
                       WHERE UserName =@userName AND UserPassword = dbo.fnMakeSHA1(@userPassword) AND Status = 'A')

            UPDATE dbo.tbUsers
                                SET UserPassword = dbo.fnMakeSHA1(@newPassword)
                                                     , UpdatedDate = GETDATE()
                                WHERE UserName = @userName AND Status = 'A'

            SELECT 0 AS RowsAffected

Now Iam accessing this stored procedure through my class library(CCR.dll) which is my bussiness layer which has the following code:

public int ChangeUserPassword(string loginName, string oldPassword, string newPassword)
            int rowsAffected = 0;
                Con = new SqlConnection(Connection.ConnectionString);
                using (Con)
                    SqlCommand cmd = new SqlCommand("[dbo].[spUserUpd]", Con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@option", SqlDbType.Int).Value = 3;
                    cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = loginName;
                    cmd.Parameters.Add("@userPassword", SqlDbType.VarChar).Value = oldPassword;
                    cmd.Parameters.Add("@newPassword", SqlDbType.VarChar).Value = newPassword;

                    rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected;
            catch (Exception ex)
                return 0;

And iam calling this funtion of the dll through my ChangePassword.aspx.cs which is the presentation layer of my project.The code for calling it as follows:

protected void ibtnChangePassword_Click(object sender, ImageClickEventArgs e)
        string loginName;
        string oldPassword;
        string newPassword;
        int IsChanged;

        User objUser = new User();

        loginName = txtLoginName.Text;
        oldPassword = txtOldPassword.Text;
        newPassword = txtNewPassword.Text;

IsChanged = objUser.ChangeUserPassword(loginName, oldPassword, newPassword);

        if (IsChanged == 1)
            lblMessage.Text = "Password changed successfully";
            lblMessage.Text = "Password change operation failed";

Now the thing is my application is working fine and capable of changing the existing user password if the old password entered is correct.But Iam facing the problem in displaying the message if the password has been successfully changed.I always get the message
"Password change operation failed"; irrespective of weather password has been changed successfully or it has failed to changed.
And I figured out the reason too.Its happening bcoz the given line on my dll
              int rowsAffected = 0;
              rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected;

is not giving me the desired output.Iam always getting the value of rowsAffected = -1 which is not supposed to be. It should return a value of 1 as we all know ExecuteNonQuery() returns the number of rows affected when it applies to UPDATE, INSERT or DELETE statements.And in my case only 1 rows get affected. For all other statements it returns the value of -1.
My question is why here ExecuteNonQuery()is not working properly as it should be.

but it doesn't help me out. I had also gone to wrox press's Begining VC# 2005 and got the example on chapter 24, page no. 819 under Retrieving No Data section and Try it out named Data Modification with ExecuteNonQuery which shows that it returns the number of rows affected when it is used for UPDATE statement.Please go through the example if it is possible for all of you.
All I want to know why ExecuteNonQuery() is not giving the intended results to me?
Does it mean that ExecuteNonQuery() is not supposed to work with 3-tier architectures?

Its not just a question of displaying a message after changing password but now its also a question of correct working of ExecuteNonQuery()function of ADO.NET.
Please help me out. Iam very curious to know the answers of my queries.Hoping for early response from experienced developers as well as all who are interested in telling me the solution of this problem.

Thanks a lot for paying attention to this long mail and reading it.

Lalit :)