?
Solved

Something wrong with Oracle DataReader

Posted on 2005-05-13
4
Medium Priority
?
1,266 Views
Last Modified: 2008-01-16
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
0
Comment
Question by:tuanbass
  • 2
  • 2
4 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 2000 total points
ID: 14001119
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
 

Author Comment

by:tuanbass
ID: 14001156
Why did I forget oraDataReader.Read() ?
It's a tired day!

Thank Mr Wood
0
 

Author Comment

by:tuanbass
ID: 14001190
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 14001843
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question