Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/172578

From BetaArchive Wiki
Knowledge Base


Article ID: 172578

Article Last Modified on 6/29/2004



APPLIES TO

  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Visual Basic 4.0 32-Bit Enterprise Edition



This article was previously published under Q172578

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 kbdse kbDSupport kbVBp kbVBp500 kbVBp600 kbVBp400 kbRDO

Keywords: kbhowto KB172578