How to create Change password form/page in asp.net using Sql server and Stored procedure

Introduction: In this article I am going to explain with example how to create change password page/ form in asp.net using both the C# and VB.Net languages and using Stored procedure and Sql server as a back end database.

Description:  In this article you will learn the following:

• How to create change password form and update the password.
• How to check credentials i.e. username and password from the Sql server database
• How to use output parameter in stored procedure to return value.And how to get/read the return output in the code behind. 
• How to use COLLATE Latin1_general_CS_AS to check for case sensitive match.

In previous related articles I explained How to create Login page/form and check username,password in asp.net using stored procedure and sql server database and How to encrypt and decrypt username,password and store in Sql Server database using asp.net and Recover and reset the forgot/lost password using reset password link in email id and Pass parameter to stored procedure using SqlDataAdapter and check login and Ajax ModalPopupExtender example to open login form in popup window and Create drop down menu for login and signup using jQuery and CSS in asp.net and Convert Rupees,Currency or Numbers to Words.

The concept is simple. We first need to check  the entered username and password against the database and if matched then the new password will overwrite the old password otherwise message will be displayed "Wrong Username/Password"

Implementation: Let’s create an asp.net application to understand the concept.

• First of all create a Sql server database and name it "MyDataBase" and create a table with the following fields and name it "Login_Tb"

Column Name	Data Type
Id	Int( Primary Key. So set Is Identity=True)
UserName	varchar(100)
Password	varchar(100)

• Add dummy username e.g. admin and password e.g. demo in the table.
• Then create a Stored Procedure to check existing username and password and change the password as:

CREATE PROCEDURE Change_Pwd_sp

(             

                @username                       VARCHAR(100),

                @old_pwd                          VARCHAR(50),

                @new_pwd                        VARCHAR(50),

                @status                              int OUTPUT

)

AS

BEGIN 

               

IF EXISTS(SELECT * FROM Login_Tb WHERE UserName COLLATE Latin1_general_CS_AS=@username AND [PASSWORD] COLLATE Latin1_general_CS_AS=@old_pwd)

                                BEGIN

                                                UPDATE Login_Tb SET [PASSWORD]=@new_pwd WHERE UserName=@username

                                                SET @status=1

                                END

ELSE                    

                                BEGIN 

                                                SET @status=0

                                END     

END

RETURN @status

In this stored procedure first the entered username and password will be checked from the database and if matched then the new password will overwrite the old password otherwise message will be displayed "Wrong Username/Password". we are using Output parameter to return the status i.e 1  for success and 0 for failure. Also Notice that in this stored procedure i have also used the COLLATE Latin1_general_CS_AS to check for the exact username and password match because it is used to make the sql queries case sensitive. e.g. if the username is admin and password is demo then if user enters Admin in username or Demo in password field then it will not match and it will not update the username and password. 

• Now in the web.config file create the connection string to connect our asp.net web application with the Sql server database as.

  <connectionStrings>

    <add name="conStr" connectionString="Data Source=lalit;Initial Catalog=MyDataBase;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and the Initial Catalog(database name) as per your application.

Source Code:

• In the <Form> tag of the design page(.aspx) design the page as:

<div>

    <fieldset style="width:350px;">

    <legend>Change password example in asp.net</legend>

    <table>

    <tr>

    <td>User Name: * </td>

    <td>

        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox><br />

        <asp:RequiredFieldValidator ID="rfvuserName" runat="server"

            ErrorMessage="Please enter User Name" ControlToValidate="txtUserName"

            Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

        </td>

    </tr>

    <tr>

    <td>Old Password: * </td>

    <td>

        <asp:TextBox ID="txtOldPwd" runat="server" TextMode="Password"></asp:TextBox><br />

        <asp:RequiredFieldValidator ID="rfvOldPwd" runat="server"

            ErrorMessage="Please enter old password" ControlToValidate="txtOldPwd"

            Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

        </td>

    </tr>

     <tr>

    <td>New Password: * </td>

    <td>

        <asp:TextBox ID="txtNewPwd" runat="server" TextMode="Password"></asp:TextBox><br />

        <asp:RequiredFieldValidator ID="rfvNewPwd" runat="server"

            ErrorMessage="Please enter new password" ControlToValidate="txtNewPwd"

            Display="Dynamic" ForeColor="Red" SetFocusOnError="True"></asp:RequiredFieldValidator>

         </td>

    </tr>

         <tr>

    <td>Confirm Password: * </td>

    <td>

        <asp:TextBox ID="txtConfirmPwd" runat="server" TextMode="Password"></asp:TextBox><br />

        <asp:RequiredFieldValidator ID="rfvConfirmPwd" runat="server"

            ErrorMessage="Please re-enter password to confirm"

            ControlToValidate="txtConfirmPwd" Display="Dynamic" ForeColor="Red"

            SetFocusOnError="True"></asp:RequiredFieldValidator>

        <asp:CompareValidator ID="cmvConfirmPwd" runat="server"

            ControlToCompare="txtNewPwd" ControlToValidate="txtConfirmPwd"

            Display="Dynamic" ErrorMessage="New and confirm password didn't match"

            ForeColor="Red" SetFocusOnError="True"></asp:CompareValidator>

             </td>

    </tr>

     <tr>

    <td>

        &nbsp;</td><td>

        <asp:Button ID="btnSubmit" runat="server" Text="Change Password"

             onclick="btnSubmit_Click" /></td>

    </tr>

     <tr>

    <td colspan="2">

        <asp:Label ID="lblStatus" runat="server" Text=""></asp:Label>

         </td>

    </tr>

    </table>

    </fieldset>   

    </div>

Note: I have used the RequiredFieldValidator and CompareValidator validation controls to validate the username and password field.

C#.Net code to create Change password form/page

• In the code behind file (.aspx.cs) write the following code on change pass word as:

First of all include the following namespaces:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

then write the code as:

protected void btnSubmit_Click(object sender, EventArgs e)

    {

         try

        {

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

            SqlCommand cmd = new SqlCommand("Change_Pwd_sp", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@username", txtUserName.Text.Trim());

            cmd.Parameters.AddWithValue("@old_pwd",  txtOldPwd.Text.Trim());

            cmd.Parameters.AddWithValue("@new_pwd", txtNewPwd.Text.Trim());

            cmd.Parameters.Add("@Status", SqlDbType.Int);

            cmd.Parameters["@Status"].Direction = ParameterDirection.Output;

            con.Open();

            cmd.ExecuteNonQuery();      

            cmd.Dispose();

            con.Close();

          //read the return value (i.e status) from the stored procedure
           int retVal= Convert.ToInt32(cmd.Parameters["@Status"].Value);

           if (retVal == 1)

           {

               lblStatus.Text = "Password has been changed successfully";

               // Or show in messagebox using: ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Password has been changed successfully');", true);

           }

           else

           {

               lblStatus.Text = "Wrong old username/password. Please re-enter.";

               // Or show in messagebox using: ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Wrong username/password. Please re-enter.');", true);

           }          

        }

        catch (Exception ex)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Oops!! following error occured : " + ex.Message.ToString() + "');", true);

            // Response.Write("Oops!! following error occured: " +ex.Message.ToString());           

        }       

    }

VB.Net Code to create Change password form/page

• In the code behind file (.aspx.vb) write the following code on change pass word as:

First of all import the following namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Then write the code as:

Protected Sub btnSubmit_Click(sender As Object, e As System.EventArgs) Handles btnSubmit.Click

        Try

            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)

            Dim cmd As New SqlCommand("Change_Pwd_sp", con)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@username", txtUserName.Text.Trim())

            cmd.Parameters.AddWithValue("@old_pwd", txtOldPwd.Text.Trim())

            cmd.Parameters.AddWithValue("@new_pwd", txtNewPwd.Text.Trim())

            cmd.Parameters.Add("@Status", SqlDbType.Int)

            cmd.Parameters("@Status").Direction = ParameterDirection.Output

            con.Open()

            cmd.ExecuteNonQuery()

            cmd.Dispose()

            con.Close()

'read the return value (i.e status) from the stored procedure            
Dim retVal As Integer = Convert.ToInt32(cmd.Parameters("@Status").Value)

            If retVal = 1 Then

                lblStatus.Text = "Password has been changed successfully"

                ' Or show in messagebox using: ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Password has been changed successfully');", true);

            Else

                lblStatus.Text = "Wrong old username/password. Please re-enter."

                ' Or show in messagebox using: ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Wrong username/password. Please re-enter.');", true);

            End If

        Catch ex As Exception

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! following error occured : " & ex.Message.ToString() & "');", True)

            ' Response.Write("Oops!! following error occured: " +ex.Message.ToString()); 

        End Try

    End Sub