Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 14th, 2006, 10:43 AM
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
)
 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 :)

  #2 (permalink)  
Old December 26th, 2006, 05:08 AM
Authorized User
 
Join Date: Jul 2005
Location: Pune, Maharasthra, India.
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


  #3 (permalink)  
Old December 26th, 2006, 08:44 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
  #4 (permalink)  
Old December 26th, 2006, 09:53 AM
Registered User
 
Join Date: Nov 2006
Location: hcm, , Vietnam.
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.

  #5 (permalink)  
Old February 28th, 2008, 10:22 AM
Registered User
 
Join Date: Feb 2008
Location: , , .
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 :)

  #6 (permalink)  
Old July 23rd, 2008, 06:50 PM
Registered User
 
Join Date: Jul 2008
Location: columbia, sc, USA.
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.

  #7 (permalink)  
Old July 24th, 2008, 02:00 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:53 AM.


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