Something wrong with Oracle DataReader

I am working with a Login function. Here is my sql to check valid user:
    Public Shared ReadOnly SQL_LOGIN As String = "SELECT kojin.shimei_kana staffname, " & _
            "kojin.kaisha_cd_kb companycode, kaisha.kaisha_mei_knj companyname, " & _
            "FROM rireki_ms_kojin kojin, rireki_ms_kaisha kaisha, rireki_ms_kyoten kyoten " & _
            "WHERE UPPER(username) = UPPER(:in_username) AND password = :in_password AND " & _
            "kojin.kaisha_cd_kb = kaisha.kaisha_cd AND kyoten.kyoten_cd = kojin.shozoku_kyoten_cd"

Here is my code to use the sql:
  Try
            oraConnection.Open()
            oraCommand.Connection = m_oraConnection
            oraCommand.CommandText = SQLCommand.SQL_LOGIN
            oraCommand.Parameters.Add("in_username", OracleDbType.Varchar2, strUsername, ParameterDirection.Input)
            oraCommand.Parameters.Add("in_password", OracleDbType.Varchar2, strPassword, ParameterDirection.Input)
            oraDataReader = oraCommand.ExecuteReader()
      If (oraDataReader.HasRows()) Then
                  HttpContext.Current.Session("STAFFNAME") = oraDataReader("staffname")
      End if
  Catch ex As OracleException
           log.PrintError(ex.ToString())
  End Try

When I tried valid username and password (already checked strUsername and strPassword), oraDataReader.HasRows() is false. When I hardcoded username and password in SQL, oraDataReader.HasRows() is true but oraDataReader("staffname") made an Exception. The exception is

Oracle.DataAccess.Client.OracleException Data provider internal error(-3000)    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
   at Oracle.DataAccess.Client.OracleDataReader.IsDBNull(Int32 i)
   at Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)
   at Oracle.DataAccess.Client.OracleDataReader.get_Item(Int32 i)
   at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)
   at TougoDBService.TougoDBServiceSecurity.IsAuthenticated(String strUsername, String strPassword)

Could you introduce me a way to solve this problem.

Many thanks
tuanbassAsked:
Who is Participating?
 
Arthur_WoodCommented:
try this change:

  Try
            oraConnection.Open()
            oraCommand.Connection = m_oraConnection
            oraCommand.CommandText = SQL_LOGIN
            oraCommand.CommandType = CommandType.Text
            oraCommand.Parameters.Add("in_username", OracleDbType.Varchar2, strUsername, ParameterDirection.Input)
            oraCommand.Parameters.Add("in_password", OracleDbType.Varchar2, strPassword, ParameterDirection.Input)
            oraDataReader = oraCommand.ExecuteReader()
            if oraDataReader.Read then
                 HttpContext.Current.Session("STAFFNAME") = oraDataReader.getValue("staffname")
            else
                 HttpContext.Current.Session("STAFFNAME") = "No Data Returned"              
            End if
  Catch ex As OracleException
           log.PrintError(ex.ToString())
  End Try


AW
0
 
tuanbassAuthor Commented:
Why did I forget oraDataReader.Read() ?
It's a tired day!

Thank Mr Wood
0
 
tuanbassAuthor Commented:
Sorry,

After changing hardcode username and password to
    oraCommand.Parameters.Add("in_username", OracleDbType.Varchar2, strUsername, ParameterDirection.Input)
    oraCommand.Parameters.Add("in_password", OracleDbType.Varchar2, strPassword, ParameterDirection.Input)

oraDataReader.Read() returns false
Did you find something wrong with my code?
0
 
Arthur_WoodCommented:
should the two lines read:

    oraCommand.Parameters.Add(":in_username", OracleDbType.Varchar2, strUsername, ParameterDirection.Input)
    oraCommand.Parameters.Add(":in_password", OracleDbType.Varchar2, strPassword, ParameterDirection.Input)


?  

AW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.