SQL Stored Proc Not Returning Any Data In The Web Page

Hi

I have coded the simple login page in vb .net  which calls the stored proc to verify whether the user login details exists in the database.  The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I  execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.

 

If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then

 

 

Dim conn As New SqlConnection()

conn.ConnectionString = Session("constr")

conn.Open()

 

Dim cmd As New SqlCommand("dbo.CheckLogin", conn)

cmd.CommandType = CommandType.StoredProcedure

' Create a SqlParameter for each parameter in the stored procedure.

Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)

usernameParam.Value = Trim(txtuser.Text)

 

Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)

pswdParam.Value = Trim(txtpassword.Text)

 

Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)

Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)

Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)

 

'IMPORTANT - must set Direction as Output

useridParam.Direction = ParameterDirection.Output

usercodeParam.Direction = ParameterDirection.Output

levelParam.Direction = ParameterDirection.Output

 

'Finally, add the parameter to the Command's Parameters collection

cmd.Parameters.Add(usernameParam)

cmd.Parameters.Add(pswdParam)

cmd.Parameters.Add(useridParam)

cmd.Parameters.Add(usercodeParam)

cmd.Parameters.Add(levelParam)

 

Dim reader1 As SqlDataReader

Try

If conn.State = ConnectionState.Closed Then

conn.Open()

End If

Try

reader1 = cmd.ExecuteReader

Using reader1

 

If reader1.Read Then

Response.Write(CStr(reader1.Read))

Session("userid") = reader1.GetValue(0)

Session("usercode") = CStr(usercodeParam.Value)

Session("level") = CStr(levelParam.Value)

Server.Transfer("home.aspx")

Else

ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")

End If

End Using

Catch ex As InvalidOperationException

ErrorLbl.Text = ex.ToString()

End Try

Finally

If conn.State <> ConnectionState.Closed Then

conn.Close()

End If

 

End Try

 

Else

ErrorLbl.Text = "Please enter you username and password"

 

 

 

End If