Return Values With Stored Procedure

Hello Group


I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page.


Thank you


Michael








CREATE PROCEDURE stpMyAuthentication


(


@fldUsername varchar( 50 ),


@fldPassword Char( 25 ),


@fldFullName varchar( 75 ) OUTPUT


)


As


DECLARE @actualPassword Char( 25 )


SELECT


@actualPassword = fldPassword





FROM [tbUsers]


Where fldUsername = @fldUsername


IF @actualPassword IS NOT NULL


IF @fldPassword = @actualPassword


RETURN 1


ELSE


RETURN -2


ELSE


RETURN -1


GO








code








Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)


If IsValid Then


If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then


FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)


End If


End If


End Sub





Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer


Dim strFullName As String


' Variable Declaration


Dim myConn As SqlConnection


Dim myCmd As SqlCommand


Dim myReturn As SqlParameter


Dim intResult As Integer


Dim sqlConn As String





' Set conn equal to the conn. string we setup in the web.config


sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")


myConn = New SqlConnection(sqlConn)





' We are going to use the stored procedure setup earlier


myCmd = New SqlCommand("stpMyAuthentication", myConn)


myCmd.CommandType = CommandType.StoredProcedure





' Set the default return parameter


myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)


myReturn.Direction = ParameterDirection.ReturnValue





' Add SQL Parameters


myCmd.Parameters.Add("@fldUsername", strUsername)


myCmd.Parameters.Add("@fldPassword", strPassword)


myCmd.Parameters.Add("@fldFullName", strFullName)





' Open SQL and Execute the query


' Then set intResult equal to the default return parameter


' Close the SQL connection


myConn.Open()


myCmd.ExecuteNonQuery()


intResult = myCmd.Parameters("RETURN_VALUE").Value


Session("strFullName") = strFullName


myConn.Close()





Response.Write(strFullName)


' If..then..else to check the userid.


' If the intResult is less than 0 then there is an error


If intResult < 0 Then


If intResult = -1 Then


lblMessage.Text = "Username Not Registered!<br><br>"


Else


lblMessage.Text = "Invalid Password!<br><br>"


End If


End If


' Return the userid


Return intResult





End Function