DOCUMENT:Q172578  12-AUG-1997  [vbwin]
TITLE   :HOWTO: Change the SQL Password Using RDO
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:4.0,5.0
OPER/SYS:WINDOWS
KEYWORDS:APrgDataODBC PrgOther vb432 VB4WIN vb5all kbhowto

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Professional and Enterprise Editions for
   Windows, version 5.0
 - Microsoft Visual Basic Enterprise Edition, 32-bit only, for Windows,
   version 4.0
---------------------------------------------------------------------

SUMMARY
=======

The following is an example of how to change the SQL server password from
Visual Basic using RDO and SQL Server's Stored Procedure - sp_Password. The
article also gives examples of allowing users to change their passwords and
allowing the SQL administrator to change the user's password.

MORE INFORMATION
================

Step-by-Step Example
--------------------

1. In a new project add two CommandButton to the form.

2. Copy the code in step 4 into the Form's Declaration section.

3. Change the values for the password/username variables.

4. Change the connection properties to match your connection. Note that
   this example uses a DSN-less connection:

   Private Sub Form_Load()
     Command1.Caption = "User"
     Command2.Caption = "Admin"
   End Sub

   Private Sub Command1_Click()
   'This procedure is an example of allowing the users to change
   'their own password.

     On Error GoTo ErrorHandler

     Dim En As rdoEnvironment
     Dim Cn As rdoConnection
     Dim Ps As rdoPreparedStatement
     Dim strConnect As String
     Dim strSQL As String
     Dim strOldPassword As String
     Dim strNewPassword As String

     Command2.Enabled = False

     'Change the following to match your values
     strOldPassword = "OldPwd"
     strNewPassword = "NewPwd"
     Set En = rdoEnvironments(0)
     En.CursorDriver = rdUseOdbc

     strConnect = "Driver={SQL Server};Server=MyServer;" & _
        "Database=pubs;Uid=UserID;Pwd=" & Trim(strOldPassword)

     Set Cn = En.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _
     ReadOnly:=False, Connect:=strConnect)
    'Note that the above is a DSN-less connection

    'Note: If you don't specify master, you will get this following error:
    '"An invalid parameter was passed."

     strSQL = "{ ? = call master.dbo.sp_password(?,?) }"
     Set Ps = Cn.CreatePreparedStatement("", strSQL)
     Ps.rdoParameters(0).Direction = rdParamReturnValue
     Ps.rdoParameters(1) = strOldPassword
     Ps.rdoParameters(2) = strNewPassword

     Ps.Execute
     Debug.Print Ps.rdoParameters(0).Value
     If Ps.rdoParameters(0) <> 0 Then
      MsgBox "Could not change password"
     Else
      MsgBox "Password has been changed"
    End If
    En.Close
    Ps.Close
    Cn.Close
    Unload Me
   Exit Sub

   ErrorHandler:
    MsgBox "Error - Password was not changed" & Chr(10) & Chr(13) & Error$
    En.Close
   Unload Me

   End Sub

   Private Sub Command2_Click()
     'This procedure is an example of the SQL Admin changing
     'a users password.
     On Error GoTo ErrorHandler

     Dim En As rdoEnvironment
     Dim Cn As rdoConnection
     Dim Ps As rdoPreparedStatement
     Dim strConnect As String
     Dim strSQL As String
     Dim strOldPassword As String
     Dim strNewPassword As String
     Dim strUserName As String

     Command1.Enabled = False

   'Change the following to match your values
     strOldPassword = "OldPwd"
     strNewPassword = "NewPwd"
     strUserName = "UserID"
     Set En = rdoEnvironments(0)
     En.CursorDriver = rdUseOdbc

     strConnect = "Driver={SQL Server};Server=MyServer;" & _
      "Database=master;Uid=sa;Pwd="

     Set Cn = En.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _
        ReadOnly:=False, Connect:=strConnect)
    'Note that the above is a DSN-less connection

     strSQL = "{ ? = call sp_password(?,?,?) }"
     Set Ps = Cn.CreatePreparedStatement("", strSQL)

     Ps.rdoParameters(0).Direction = rdParamReturnValue
     Ps.rdoParameters(1) = strOldPassword
     Ps.rdoParameters(2) = strNewPassword
     Ps.rdoParameters(3) = strUserName

     Ps.Execute
     Debug.Print Ps.rdoParameters(0).Value
     If Ps.rdoParameters(0) <> 0 Then
       MsgBox "Could not change password"
     Else
      MsgBox "Password has been changed"
     End If
     En.Close
     Ps.Close
     Cn.Close
     Unload Me
     Exit Sub

   ErrorHandler:
   MsgBox "Error - Password was not changed" & Chr(10) & Chr(13) & Error$
     En.Close
     Cn.Close
     Ps.Close
     Unload Me

   End Sub

Additional query words: rdoquery CreateQuery
======================================================================
Keywords          : APrgDataODBC PrgOther vb432 VB4WIN vb5all kbhowto
Technology        : odbc
Version           : WINDOWS:4.0,5.0
Platform          : WINDOWS
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1997.