Microsoft KB Archive/172578: Difference between revisions
From BetaArchive Wiki
m (Text replacement - """ to """) |
m (Text replacement - "&" to "&") |
||
Line 89: | Line 89: | ||
En.CursorDriver = rdUseOdbc | En.CursorDriver = rdUseOdbc | ||
strConnect = "Driver={SQL Server};Server=MyServer;" & | strConnect = "Driver={SQL Server};Server=MyServer;" & _ | ||
"Database=pubs;Uid=UserID;Pwd=" & | "Database=pubs;Uid=UserID;Pwd=" & Trim(strOldPassword) | ||
Set Cn = En.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _ | Set Cn = En.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _ | ||
Line 119: | Line 119: | ||
ErrorHandler: | ErrorHandler: | ||
MsgBox "Error - Password was not changed" & | MsgBox "Error - Password was not changed" & Chr(10) & Chr(13) & Error$ | ||
En.Close | En.Close | ||
Unload Me | Unload Me | ||
Line 148: | Line 148: | ||
En.CursorDriver = rdUseOdbc | En.CursorDriver = rdUseOdbc | ||
strConnect = "Driver={SQL Server};Server=MyServer;" & | strConnect = "Driver={SQL Server};Server=MyServer;" & _ | ||
"Database=master;Uid=sa;Pwd=" | "Database=master;Uid=sa;Pwd=" | ||
Line 177: | Line 177: | ||
ErrorHandler: | ErrorHandler: | ||
MsgBox "Error - Password was not changed" & | MsgBox "Error - Password was not changed" & Chr(10) & Chr(13) & Error$ | ||
En.Close | En.Close | ||
Cn.Close | Cn.Close |
Latest revision as of 12:29, 21 July 2020
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
- In a new project add two CommandButton to the form.
- Copy the code in step 4 into the Form's Declaration section.
- Change the values for the password/username variables.
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