Post reply

Cannot get SP with 2 Outputs to work

  • July 31, 2007 at 8:54 am

    #95324

    This is my first attempt at writing a SP. I am using SQL 2000 with VB.Net 2005. All I am wanting to do is pass from a login is the username and password. Then return back whether the password was correct or not, and if so, are they a SuperUser as well. All I get back is Null. I would like to know where I have gone wrong.

    Here is my SP:

    CREATE PROCEDURE VerifyPassword

    @lUserID varchar(10),

    @strPassword varchar(15),

    @bSuperUser bit OUTPUT,

    @strPasswordExist bit OUTPUT

    AS

    Declare @tmpPasswordRtrv varchar(15),

    @tmpRslt bit

    IF @tmpPasswordRtrv = (SELECT strPassword FROM Users WHERE lUniqueID = @lUserID)

    IF @tmpPasswordRtrv = @strPassword SELECT @strPasswordExist = "True" ELSE SELECT @strPasswordExist = "False"

    IF @tmpRslt = (SELECT bSuperUser FROM Users WHERE lUniqueID = @lUserID)

    IF @tmpRslt = 1 SELECT @bSuperUser = 1 ELSE SELECT @bSuperUser = 0

    GO

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

    My VB code is:

    Dim prmProcedure As SqlParameter

    Dim prmPasswordVerifyOutput As SqlParameter

    Dim prmIsSuperUserOutput As SqlParameter

    Dim myConnection As New SqlConnection(CONNECT_STRING)

    Dim myCommand As New SqlCommand("VerifyPassword", myConnection)

    Try

    myConnection.Open()

    myCommand.CommandType = CommandType.StoredProcedure

    prmProcedure = myCommand.Parameters.AddWithValue("@lUserID", 1)

    prmProcedure = myCommand.Parameters.AddWithValue("@strPassword", txtPassword.Text)

    prmPasswordVerifyOutput = myCommand.Parameters.Add("@strPasswordExist", SqlDbType.VarChar, 15)

    prmIsSuperUserOutput = myCommand.Parameters.Add("@bSuperUser", SqlDbType.Bit)

    prmPasswordVerifyOutput.Direction = ParameterDirection.Output

    prmIsSuperUserOutput.Direction = ParameterDirection.Output

    Dim numAffected As Integer

    Dim strTest As String = ""

    numAffected = myCommand.ExecuteNonQuery

    MessageBox.Show("Result = " & prmPasswordVerifyOutput.Value.ToString)

    MessageBox.Show("Result = " & prmIsSuperUserOutput.Value.ToString)

    myConnection.Close()

    Catch ex As Exception

    MessageBox.Show(ex.Message, "Error Verifying Password")

    End Try

  • July 31, 2007 at 9:47 am

    #723637

    Might be I'm wrong, but I don't htink you can compare varaible without value to result of select. I meant

    IF @tmpPasswordRtrv = (SELECT strPassword FROM Users WHERE lUniqueID = @lUserID)

    and

    IF @tmpRslt = (SELECT bSuperUser FROM Users WHERE lUniqueID = @lUserID)

    try

    set @tmpPasswordRtrv = SELECT strPassword FROM Users WHERE lUniqueID = @lUserID

  • July 31, 2007 at 10:02 am

    #723646

    That was it!

    Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply