Microsoft KB Archive/172578: Difference between revisions
From BetaArchive Wiki
m (Text replacement - ">" to ">") |
m (Text replacement - """ to """) |
||
Line 63: | Line 63: | ||
<li><p>Change the connection properties to match your connection. Note that this example uses a DSN-less connection:</p> | <li><p>Change the connection properties to match your connection. Note that this example uses a DSN-less connection:</p> | ||
<pre class="codesample"> Private Sub Form_Load() | <pre class="codesample"> Private Sub Form_Load() | ||
Command1.Caption = | Command1.Caption = "User" | ||
Command2.Caption = | Command2.Caption = "Admin" | ||
End Sub | End Sub | ||
Line 84: | Line 84: | ||
'Change the following to match your values | 'Change the following to match your values | ||
strOldPassword = | strOldPassword = "OldPwd" | ||
strNewPassword = | strNewPassword = "NewPwd" | ||
Set En = rdoEnvironments(0) | Set En = rdoEnvironments(0) | ||
En.CursorDriver = rdUseOdbc | En.CursorDriver = rdUseOdbc | ||
strConnect = | strConnect = "Driver={SQL Server};Server=MyServer;" & _ | ||
"Database=pubs;Uid=UserID;Pwd=" & Trim(strOldPassword) | |||
Set Cn = En.OpenConnection(dsName:= | Set Cn = En.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _ | ||
ReadOnly:=False, Connect:=strConnect) | ReadOnly:=False, Connect:=strConnect) | ||
'Note that the above is a DSN-less connection | 'Note that the above is a DSN-less connection | ||
'Note: If you don't specify master, you will get this following error: | 'Note: If you don't specify master, you will get this following error: | ||
' | '"An invalid parameter was passed." | ||
strSQL = | strSQL = "{ ? = call master.dbo.sp_password(?,?) }" | ||
Set Ps = Cn.CreatePreparedStatement( | Set Ps = Cn.CreatePreparedStatement("", strSQL) | ||
Ps.rdoParameters(0).Direction = rdParamReturnValue | Ps.rdoParameters(0).Direction = rdParamReturnValue | ||
Ps.rdoParameters(1) = strOldPassword | Ps.rdoParameters(1) = strOldPassword | ||
Line 108: | Line 108: | ||
Debug.Print Ps.rdoParameters(0).Value | Debug.Print Ps.rdoParameters(0).Value | ||
If Ps.rdoParameters(0) <> 0 Then | If Ps.rdoParameters(0) <> 0 Then | ||
MsgBox | MsgBox "Could not change password" | ||
Else | Else | ||
MsgBox | MsgBox "Password has been changed" | ||
End If | End If | ||
En.Close | En.Close | ||
Line 119: | Line 119: | ||
ErrorHandler: | ErrorHandler: | ||
MsgBox | MsgBox "Error - Password was not changed" & Chr(10) & Chr(13) & Error$ | ||
En.Close | En.Close | ||
Unload Me | Unload Me | ||
Line 142: | Line 142: | ||
'Change the following to match your values | 'Change the following to match your values | ||
strOldPassword = | strOldPassword = "OldPwd" | ||
strNewPassword = | strNewPassword = "NewPwd" | ||
strUserName = | strUserName = "UserID" | ||
Set En = rdoEnvironments(0) | Set En = rdoEnvironments(0) | ||
En.CursorDriver = rdUseOdbc | En.CursorDriver = rdUseOdbc | ||
strConnect = | strConnect = "Driver={SQL Server};Server=MyServer;" & _ | ||
"Database=master;Uid=sa;Pwd=" | |||
Set Cn = En.OpenConnection(dsName:= | Set Cn = En.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _ | ||
ReadOnly:=False, Connect:=strConnect) | ReadOnly:=False, Connect:=strConnect) | ||
'Note that the above is a DSN-less connection | 'Note that the above is a DSN-less connection | ||
strSQL = | strSQL = "{ ? = call sp_password(?,?,?) }" | ||
Set Ps = Cn.CreatePreparedStatement( | Set Ps = Cn.CreatePreparedStatement("", strSQL) | ||
Ps.rdoParameters(0).Direction = rdParamReturnValue | Ps.rdoParameters(0).Direction = rdParamReturnValue | ||
Line 166: | Line 166: | ||
Debug.Print Ps.rdoParameters(0).Value | Debug.Print Ps.rdoParameters(0).Value | ||
If Ps.rdoParameters(0) <> 0 Then | If Ps.rdoParameters(0) <> 0 Then | ||
MsgBox | MsgBox "Could not change password" | ||
Else | Else | ||
MsgBox | MsgBox "Password has been changed" | ||
End If | End If | ||
En.Close | En.Close | ||
Line 177: | Line 177: | ||
ErrorHandler: | ErrorHandler: | ||
MsgBox | MsgBox "Error - Password was not changed" & Chr(10) & Chr(13) & Error$ | ||
En.Close | En.Close | ||
Cn.Close | Cn.Close |
Revision as of 11:07, 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