p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 2.0 Professional (http://p2p.wrox.com/forumdisplay.php?f=215)
-   -   ExecuteNonQuery not giving proper result (http://p2p.wrox.com/showthread.php?t=47838)

Lalit Pradhan September 14th, 2006 10:43 AM

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
)
 AS

SET NOCOUNT ON

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

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

    ELSE
        BEGIN
            SELECT 0 AS RowsAffected
        END
END
GO

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;
            try
            {
                Con = new SqlConnection(Connection.ConnectionString);
                Con.Open();
                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";
        }
        else
        {
            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.
I HAD GONE THROUGH THE MSDN HELP ON THE FOLLOWING LINK
http://msdn.microsoft.com/library/de...QueryTopic.asp

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 :)


Lalit Pradhan December 26th, 2006 05:08 AM

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
)
 AS

SET NOCOUNT ON

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

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

    ELSE
        BEGIN
            SELECT 0 AS RowsAffected
        END
END
GO

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;
            try
            {
                Con = new SqlConnection(Connection.ConnectionString);
                Con.Open();
                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";
        }
        else
        {
            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.
I HAD GONE THROUGH THE MSDN HELP ON THE FOLLOWING LINK
http://msdn.microsoft.com/library/de...QueryTopic.asp

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.

I would also like Mr. IMAR to go through this problem as Iam hoping for the positive response from him as he is the most advanced member of wrox p2p forum.

Lalit



Imar December 26th, 2006 08:44 AM

Quote:

quote:I would also like Mr. IMAR to go through this problem as Iam hoping for the positive response from him as he is the most advanced member of wrox p2p forum.
You can do that, but you just trashed your own luck by sending me all this stuff through personal messages as well, which I asked you not to do specifically. If you can't read (e.g. my Profile page), then why bother....

The Wrox forum is a web based forum where people can read and answer posts in public. If we all start harassing the "advanced members" by personal mail, they're gone in no time.

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

xuan9801170 December 26th, 2006 09:53 AM

I agreed with Mr IMAR.But please ignore what Lalit said.You should help others who had the same problem.That will be great.I've just joined this forum, but I can realize what Mr IMAR have done for this forum is so great.Thank you very much for that.


shenimr February 28th, 2008 10:22 AM

Hi,

Did you find a solution for this.

Thanks Sheni

Quote:

quote:Originally posted by Lalit Pradhan
 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
)
AS

SET NOCOUNT ON

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

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

    ELSE
        BEGIN
            SELECT 0 AS RowsAffected
        END
END
GO

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;
            try
            {
                Con = new SqlConnection(Connection.ConnectionString);
                Con.Open();
                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";
        }
        else
        {
            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.
I HAD GONE THROUGH THE MSDN HELP ON THE FOLLOWING LINK
http://msdn.microsoft.com/library/de...QueryTopic.asp

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 :)



amit July 23rd, 2008 06:50 PM

I am also facing the same problem and am pulling my hair...anyone has any solution to this problem..
"executenonquery always returns -1 even though it updates/deletes successfully in database"
Pl help.


Imar July 24th, 2008 02:00 AM

Can you show us your code?

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.


All times are GMT -4. The time now is 01:37 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.