.NET Change Password (SQL Database)

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?

THANKS!
gcargileAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ho_alanCommented:
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>
</form>

updatePwd1.asp
<%
oldPass=request.form("txtOldPass")
'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>
</form>
<%'end if%>

updatePwd2.asp

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

if not newPass = confirmNewPass then
'go back
else
'open DB connection, assume lgName stored the logged in name for user
'strSQL = "update urTable set Password="&newPass &"where logname="&lgName
end if
%>
Programming_GalCommented:
Oops... Shouldn't it be .net?
Programming_GalCommented:
gcargile,

Do you mean that how to update your SQL Table when the user click on the btnChange Button?
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Programming_GalCommented:
Assuming your table look like this
UserPWTable

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'
Programming_GalCommented:
Maybe you need to be more specified on when help you need.
Programming_GalCommented:
Maybe you need to be more specified on what help you need.
David H.H.LeeCommented:
gcargile,
Here's my complete version about what you need :

changePassword.aspx
========================
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="changePassword.aspx.vb" Inherits="EETest.changePassword"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>changePassword</title>
            <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="http://schemas.microsoft.com/intellisense/ie5">
      </HEAD>
      <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>
            </form>
      </body>
</HTML>

changePassword.aspx.vb
====================
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.
        InitializeComponent()
    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)
            objCB.RefreshSchema()

            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!!!"
                Else
                    lblStatus.Text = "Please insert valid old password!!"
                End If
            Else
                    lblStatus.Text = "Please insert an valid userName!!!"
            End If

            objDataSet.Tables("tblUser").Reset()
        End If

    End Sub
End Class

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

Regards
x_com

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gcargileAuthor Commented:
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
 
gcargileAuthor Commented:
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?
gcargileAuthor Commented:
Nevermind again.. that was an easier one than I thought.  THANKS!!!
David H.H.LeeCommented:
Glad to help, gcargile.

Regards
x_com
gcargileAuthor Commented:
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?
            objCB.RefreshSchema()

This worked very nicely by the way!
David H.H.LeeCommented:
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.
eg:
strSQL = "select userName,password from tblUser where userName=@userName"

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

Hope that clear for you.

Regards
x_com
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.