I got a problem to change the users' password from the VB
side. It is almost same as one from SQL7, and it works on
SQL7 but I have problem on SQL2000. It is ok when I run it
from QA, what's wrong? Do I need set ANSI NULLS?
Any one can help? Thanks you very much and have a good one
Abchawk
Here is the Stored procedure i wrote
if it is sysadminto change others PW, the login will be the person who PW
will be changed, otherwise, the person changes his own PW,
in this case, login will be ' ')
CREATE PROCEDURE I_USER_CHANGE_PASSWORD_SP
(
@.LOGIN NVARCHAR(10)=' ',
@.OLDPASSWD NVARCHAR(10),
@.NEWPASSWD NVARCHAR(10)
) AS
DECLARE @.OUT INT
IF @.LOGIN <>' ' BEGIN
EXEC @.OUT = sp_password NULL ,
@.NEWPASSWD,@.LOGIN
END
ELSE BEGIN
EXEC @.OUT = sp_password
@.OLDPASSWD,@.NEWPASSWD
END
RETURN @.OUT
GO
****************************************
**************
The VB side code like this:
Set adoCommand = CreateObject("ADODB.Command")
mySQL = "I_USER_CHANGE_PASSWORD_SP "
With adoCommand
.ActiveConnection = strCnn
.CommandText = mySQL
.CommandTimeout = 100
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RETURN",
adInteger, adParamReturnValue)
If SecurityLevel < 2 Or Trim(Me.txtLogin) =
userName Then
.Parameters.Append .CreateParameter
("Login", adWChar, adParamInput, 10, " ")
.Parameters.Append .CreateParameter
("OldPasswd", adWChar, adParamInput, 10, Trim
(Me.txtOldPassword))
Else
.Parameters.Append .CreateParameter
("Login", adWChar, adParamInput, 10, Trim(Me.txtLogin))
.Parameters.Append .CreateParameter
("OldPasswd", adWChar, adParamInput, 10, Null)
End If
.Parameters.Append .CreateParameter
("NewPasswd", adWChar, adParamInput, 10, Trim
(Me.txtNewPassword))
End With
adoCommand.Execute
OUT = adoCommand(0)
If OUT <> 1 Then
MsgBox "Change password successfully",
vbInformation
Else
MsgBox "Sorry,Changing password failured",
vbInformation
End If
Set adoCommand = Nothing
****************************************
******************Below is a VBScript code snippet that shows how to execute a proc with
parameters. This way, you don't have to build the SQL with properly quoted
strings.
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connection
cmd.CommandType = 'adCmdStoredProcedure
cmd.CommandText = "sp_addlogin"
Set pReturnCode = cmd.CreateParameter("@.RETURN_CODE")
pReturnCode.Type = adInteger
pReturnCode.Direction = 4 'adParamReturnValue
cmd.Parameters.Append pReturnCode
Set pLoginName = cmd.CreateParameter("@.loginame")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Size = 128
pLoginName.Value = "MyLogin"
cmd.Parameters.Append pLoginName
Set pLoginName = cmd.CreateParameter("@.passwd")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Size = 128
pLoginName.Value = "MyLoginPassword"
cmd.Parameters.Append pLoginName
cmd.Execute
'display return code
MsgBox cmd.Parameters(0).Name & "=" & cmd.Parameters(0).Value
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"abchawk" <anonymous@.discussions.microsoft.com> wrote in message
news:005d01c4d72f$1c866120$a601280a@.phx.gbl...
> Hi, All,
> I think I found the reason that cause the problem. It is
> the single quota ('). If I run SQL statement in VB
> like "sp_password '" & strOldPW & "','" & strNewPW & "'"
> it is not problem at all, but how can I do samething in VB
> using ado command? Does anyone know it?
> Samething for sp_addlogin, I create the login but can not
> access the DB because of Password. Using SQL statement
> like above, it is fine. just becasue the data type is
> sysname? For SQL 7 , double quota works fine, but not in
> SQL 2000
> Thanks and have a great day.
> hawk
