Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
|
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 14th, 2006, 10:43 AM
Authorized User
 
Join Date: Jul 2005
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
)
 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 :)

 
Old December 26th, 2006, 05:08 AM
Authorized User
 
Join Date: Jul 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old December 26th, 2006, 08:44 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old December 26th, 2006, 09:53 AM
Registered User
 
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old February 28th, 2008, 10:22 AM
Registered User
 
Join Date: Feb 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 23rd, 2008, 06:50 PM
Registered User
 
Join Date: Jul 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old July 24th, 2008, 02:00 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Expaning on ExecuteNonQuery () in DataAccess class kalel_4444 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 July 21st, 2008 03:52 PM
Please help my code is not giving me a result set yudee JSP Basics 0 September 20th, 2006 09:55 AM
VB.NET ExecuteNonQuery() command Lofa SQL Server 2000 1 September 20th, 2005 10:03 AM
OleDbCommand.ExecuteNonQuery(); kaz VS.NET 2002/2003 1 December 7th, 2003 04:29 PM





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