.NET Change Password (SQL Database)

Posted on 2003-12-08
Last Modified: 2013-11-25
I have a SQL table that I have user names and passwords in.  I want the user to be able to change their passwords.  So I created a form that says: (I'm also using VB.NET as codebehind)

UserName:           txtUserName
OldPassword:       txtOldPass
NewPassword:     txtNewpass
          Change Button     btnChange

So, How do I write the code to let the user change the password through the form?

Question by:gcargile
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1

Expert Comment

ID: 9901539
assume the user has already login, here is the basic idea
hope can inspire u :-)

change password:
please enter the old password
<form method=post action=updatePwd1.asp>
<input type=password name=txtOldPass>
<input type=submit value=submit>

'sql stuff, open connection
'if old password matches, ask user to enter new password
<form method=post action=updatePwd2.asp>
<input type=password name=newPass>
<input type=password name=confirmNewPass>
<input type=submit value=submit>
<%'end if%>


newPass = request.form("newPass")
confirmNewPass = request.form("confirmNewPass")

if not newPass = confirmNewPass then
'go back
'open DB connection, assume lgName stored the logged in name for user
'strSQL = "update urTable set Password="&newPass &"where logname="&lgName
end if

Expert Comment

ID: 9901929
Oops... Shouldn't it be .net?

Expert Comment

ID: 9901939

Do you mean that how to update your SQL Table when the user click on the btnChange Button?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 9901948
Assuming your table look like this

Username | Password
eric             cire
doreen        neerod

User 'eric' wants to change his password from cire to eric12345, you need to use SQL statement to update your database that Username='eric'

Expert Comment

ID: 9901950
Maybe you need to be more specified on when help you need.

Expert Comment

ID: 9901951
Maybe you need to be more specified on what help you need.
LVL 29

Accepted Solution

David H.H.Lee earned 500 total points
ID: 9902476
Here's my complete version about what you need :

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="changePassword.aspx.vb" Inherits="EETest.changePassword"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
            <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
            <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
            <meta name="vs_defaultClientScript" content="JavaScript">
            <meta name="vs_targetSchema" content="">
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <asp:Label id="lblUser" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 56px" runat="server">UserName</asp:Label>
                  <asp:TextBox id="txtUserName" style="Z-INDEX: 102; LEFT: 144px; POSITION: absolute; TOP: 56px"
                        runat="server" Width="208px"></asp:TextBox>
                  <asp:Label id="lblOldPassword" style="Z-INDEX: 103; LEFT: 24px; POSITION: absolute; TOP: 96px"
                        runat="server">Old Password</asp:Label>
                  <asp:TextBox id="txtOldPassword" style="Z-INDEX: 104; LEFT: 144px; POSITION: absolute; TOP: 96px"
                        runat="server" Width="208px"></asp:TextBox>
                  <asp:TextBox id="txtNewPassword" style="Z-INDEX: 105; LEFT: 144px; POSITION: absolute; TOP: 136px"
                        runat="server" Width="208px"></asp:TextBox>
                  <asp:Label id="lblNewPassword" style="Z-INDEX: 106; LEFT: 24px; POSITION: absolute; TOP: 136px"
                        runat="server">New Password</asp:Label>
                  <asp:RequiredFieldValidator id="RequiredFieldValidator1" style="Z-INDEX: 107; LEFT: 368px; POSITION: absolute; TOP: 56px"
                        runat="server" ErrorMessage="Please fill in userName" Font-Bold="True" ControlToValidate="txtUserName"></asp:RequiredFieldValidator>
                  <asp:RequiredFieldValidator id="RequiredFieldValidator2" style="Z-INDEX: 108; LEFT: 368px; POSITION: absolute; TOP: 96px"
                        runat="server" ErrorMessage="Please fill in old password" Font-Bold="True" ControlToValidate="txtOldPassword"></asp:RequiredFieldValidator>
                  <asp:RequiredFieldValidator id="RequiredFieldValidator3" style="Z-INDEX: 109; LEFT: 368px; POSITION: absolute; TOP: 136px"
                        runat="server" ErrorMessage="Please fill in new password" Font-Bold="True" ControlToValidate="txtNewPassword"></asp:RequiredFieldValidator>
                  <asp:Button id="btnChange" style="Z-INDEX: 110; LEFT: 144px; POSITION: absolute; TOP: 192px"
                        runat="server" Text="Change Password"></asp:Button>
                  <asp:Label id="lblStatus" style="Z-INDEX: 111; LEFT: 144px; POSITION: absolute; TOP: 248px"
                        runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>

Imports System.Data
Imports System.Data.SqlClient

Public Class changePassword
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents lblUser As System.Web.UI.WebControls.Label
    Protected WithEvents txtUserName As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblOldPassword As System.Web.UI.WebControls.Label
    Protected WithEvents txtOldPassword As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtNewPassword As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblNewPassword As System.Web.UI.WebControls.Label
    Protected WithEvents RequiredFieldValidator1 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents RequiredFieldValidator2 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents RequiredFieldValidator3 As System.Web.UI.WebControls.RequiredFieldValidator
    Protected WithEvents btnChange As System.Web.UI.WebControls.Button
    Protected WithEvents lblStatus As System.Web.UI.WebControls.Label

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

    Private Sub btnChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChange.Click
        If Page.IsValid Then
            Dim conData As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=EE")
            Dim strSQL As String
            Dim objDataAdapter As SqlDataAdapter
            Dim objCB As SqlCommandBuilder
            Dim objDataSet As DataSet = New DataSet

            'userName as primary key
            'table tblUser - userName,password
            strSQL = "select userName,password from tblUser where userName=@userName"
            objDataAdapter = New SqlDataAdapter(strSQL, conData)
            objCB = New SqlCommandBuilder(objDataAdapter)
            objDataAdapter.SelectCommand.CommandText = strSQL
            objDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

            objDataAdapter.SelectCommand.Parameters.Add("@userName", txtUserName.Text.Trim)

            objDataAdapter.Fill(objDataSet, "tblUser")

            'check wheter exist this logged in user or not
            If objDataSet.Tables("tblUser").Rows.Count > 0 Then
                'check existing old password - must match, not ignore case sensitive
                If String.Compare(Trim(objDataSet.Tables("tblUser").Rows(0).Item("password")), txtOldPassword.Text.Trim, False) = 0 Then
                    objDataSet.Tables("tblUser").Rows(0).Item("password") = txtNewPassword.Text.Trim
                    objDataAdapter.Update(objDataSet, "tblUser")
                    lblStatus.Text = "Success change the old password!!!"
                    lblStatus.Text = "Please insert valid old password!!"
                End If
                    lblStatus.Text = "Please insert an valid userName!!!"
            End If

        End If

    End Sub
End Class

-I'd include all the details explanations had include inside the code.


Author Comment

ID: 9904997
Great... Thanks x_com....   I get this error though:

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Source Error:

Line 59:                 If String.Compare(Trim(objDataSet.Tables("Credentials").Rows(0).Item("password")), txtOldPass.Text.Trim, False) = 0 Then
Line 60:                     objDataSet.Tables("Credentials").Rows(0).Item("password") = txtNewPass.Text.Trim
Line 61:                     objDataAdapter.Update(objDataSet, "Credentials")
Line 62:                     lblStatus.Text = "Success change the old password!!!"
Line 63:                 Else

Author Comment

ID: 9906030
Nevermind that question.  I didn't have Username set as my primary key.  Do you know how to have the passwords come up as ***** when you type them in?

Author Comment

ID: 9906053
Nevermind again.. that was an easier one than I thought.  THANKS!!!
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9910124
Glad to help, gcargile.


Author Comment

ID: 9912080
x_com: Only if you feel like, but will you explain these lines...

           objDataAdapter.SelectCommand.CommandText = strSQL
                'Why do I need the sql command in selectcommand.commandtext ?
            objDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
               'What is MissingSchemaAction?
            objDataAdapter.SelectCommand.Parameters.Add("@userName", txtUserName.Text.Trim)
               'What am I adding exactly?

This worked very nicely by the way!
LVL 29

Expert Comment

by:David H.H.Lee
ID: 9918137
gcargile ,
Here my explanations:

objDataAdapter.SelectCommand.CommandText = strSQL
-Use to select old password - before any changes

objDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
-Adds the necessary columns and primary key information to complete the schema. For more information about how primary key information is added

objDataAdapter.SelectCommand.Parameters.Add("@userName", txtUserName.Text.Trim)
-select field from DB based on criteria (here i'm using userName as a search keyword).This keyword will replace the value for @userName inside SQL statement as you see in my code.
strSQL = "select userName,password from tblUser where userName=@userName"

-you need to use it for any SELECT statement associated with the SqlCommandBuilder changes.

Hope that clear for you.


Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question