retrieve data from a stored procedure

Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can't retrieve the data. Here is my code.

CREATE PROCEDURE stpMyAuthentication
(
@.fldUsername varchar( 50 ),
@.fldPassword Char( 25 )--,
--@.fldFirstName char( 30 ) OUTPUT,
--@.fldLastName char( 30 ) OUTPUT
)
As
DECLARE @.actualPassword Char( 25 )
SELECT
@.actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @.fldUsername
IF @.actualPassword IS NOT NULL
IF @.fldPassword = @.actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @.fldUsername
GO

############### login page ################

 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 myConn As SqlConnection
 Dim myCmd As SqlCommand
 Dim myReturn As SqlParameter
 Dim intResult As Integer
 Dim sqlConn As String
 Dim strFirstName, strLastName As String
 sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
 myConn = New SqlConnection(sqlConn)
 myCmd = New SqlCommand("stpMyAuthentication", myConn)
 myCmd.CommandType = CommandType.StoredProcedure
 myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
 myReturn.Direction = ParameterDirection.ReturnValue
 myCmd.Parameters.Add(Trim("@.fldUsername"), Trim(strUsername))
 myCmd.Parameters.Add(Trim("@.fldPassword"), Trim(strPassword))
 myCmd.Parameters.Add("@.fldFirstName", strFirstName)
 myCmd.Parameters.Add("@.fldLastName", strLastName)
 myCmd.Parameters.Add("@.fldPassword", strPassword)
 myConn.Open()
 myCmd.ExecuteNonQuery()
 intResult = myCmd.Parameters("RETURN_VALUE").Value
 myConn.Close()
 'If strPassword = 55555555 Then
 ' Session("intDefaultPass") = 1
 'End If
 Session("strFullName") = strFirstName & " " & strLastName
 Session("strPassword") = strPassword
 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 intResult
 End Function

At this time I am not getting any errors. How can I retrieve the data that I am after?
Michaeldude
your code is all screwed up. re-work on your logic. you dont need to select the actualpassword in a sep variable and compare it against whats in the variable. you can directly compare it against the value in the table.

i'd would rewrite your query as :

CREATE PROCEDURE stpMyAuthentication
(
@.fldUsername varchar( 50 ),
@.fldPassword varChar( 25 ),
@.fldFirstName varchar(30) OUTPUT,
@.fldLastName varchar( 30 ) OUTPUT
@.intRes int OUTPUT
)
As
SET NOCOUNT OFF
BEGIN
--@.intRes 0=no user. 1=valid. 2=invalid passwd
IF EXISTS ( Select * from tbMembUsers where fldUsername = @.fldUsername and fldPassword=@.fldPassword )
BEGIN
SELECT @.fldFirstName=fldFirstName, @.fldLastName =fldLastName
FROMtbMembUsers
WHEREfldUsername = @.fldUsername AND fldPassword=@.fldPassword
SET@.intRes=1
END
ELSE
BEGIN
-- INVALID PASSWORD
IF EXISTS ( Select * from tbMembUsers where fldUsername = @.fldUsername )
SET @.intRes=2
--No CREDENTIALS FOUND SO RETURN 0
IF EXISTS ( select * From tbMembUsers where fldUsername = @.fldUsername and fldPassword=@.fldPassword )
SET @.intRes=0
END
SET NOCOUNT OFF
END

and from the asp.net page you can use the sample code here to modify it acc to your stored proc.

Dim myCommand As SqlCommand
Dim myParam As SqlParameter
dim resvalue as integer
myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "testmovein_cusinfo"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@.username",SqlDbType.varchar,30))
myCommand.Parameters("@.username").Value = Trim(username)
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
resvalue=convert.toint16((mycommand.Parameters("@.result").Value))
Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
 objCon.Close()
End If
'objCon.Dispose()
End Try

hth|||why do you SET NOCOUNT OFF twice?

and i think you'll confuse him with
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.int
mycommand.Parameters.Add(myParam)
----

mycommand.Paramters.Add("@.Result", SqlDbType.int)
mycommand.Parameters("@.Result").Direction = ParameterDirection.Output

does the same thing. :)|||yeah sorry abt the nocount thing.

theres always more than one way you can do a thing in. i just cut n pasted some template code.|||Thanks guys. I finally got it. Even though this code works just fine, I am wondering if I have some unnecessary code. Can you please check it for me thanks.

 Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
 ' Variable Declaration
 Dim myConn As SqlConnection
 Dim myCmd As SqlCommand
 Dim myParam As SqlParameter
 Dim intResult As Integer
 Dim sqlConn As String
 Dim strFirstName, strLastName As String
 Dim resvalue As Integer
 sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
 myConn = New SqlConnection(sqlConn)
 myCmd = New SqlCommand("stpMyAuthentication", myConn)
 myCmd.CommandType = CommandType.StoredProcedure
 'output parameter
 myParam = myCmd.CreateParameter()
 myParam.Direction = ParameterDirection.Output
 myParam.SqlDbType = SqlDbType.Int
 myCmd.Parameters.Add(myParam)
 '########################################################
 myCmd = New SqlCommand
 myCmd.Connection = myConn
 myCmd.CommandText = "stpMyAuthentication"
 myCmd.CommandType = CommandType.StoredProcedure
 myCmd.Parameters.Add("@.intRes", SqlDbType.Int)
 myCmd.Parameters("@.intRes").Direction = ParameterDirection.Output
 myCmd.Parameters.Add(Trim("@.fldUsername"), Trim(strUsername))
 myCmd.Parameters.Add(Trim("@.fldPassword"), Trim(strPassword))
 myCmd.Parameters.Add("@.fldFirstName", SqlDbType.VarChar, 30)
 myCmd.Parameters("@.fldFirstName").Direction = ParameterDirection.Output
 myCmd.Parameters.Add("@.fldLastName", SqlDbType.VarChar, 30)
 myCmd.Parameters("@.fldLastName").Direction = ParameterDirection.Output
 '########################################################
 Try
 Response.Write("1<br>")
 If myConn.State = 0 Then myConn.Open()
 myCmd.ExecuteNonQuery()
 resvalue = Convert.ToInt16((myCmd.Parameters("@.intRes").Value))
 strFirstName = myCmd.Parameters("@.fldFirstName").Value
 strLastName = myCmd.Parameters("@.fldLastName").Value
 Session("strFullName") = strFirstName & " " & strLastName
 Catch exc As Exception
 Response.Write(exc)
 Finally
 If myConn.State = ConnectionState.Open Then
 myConn.Close()
 End If
 'objCon.Dispose()
 End Try
 Select Case resvalue
 Case 0
 lblMessage.Text = "Username Not Registered!<br><br>"
 Case 1
 If strPassword = "55555555" Then
 Session("intDefaultPass") = 1
 End If
 Case 2
 lblMessage.Text = "Invalid Password!<br><br>"
 End Select
 Return resvalue
 End Function