Oracle.DataAccess.Client.OracleException C#

Question

I have this class where I am selecting two values from the database and comparing it to the textbox values provided by the users. Below is my class.

     public void Userlogin(TextBox username, TextBox pwd)
    {
        int _failedAttempt = 0;

        OpenConnection();

        command = new OracleCommand();
        command.CommandText = "SELECT username, user_pwd FROM dinein_system_users WHERE username:usrname AND user_pwd:pwd";
        command.Connection = dbconnect;
        command.BindByName = true;

        try
        {
            command.Parameters.Add("usrname", username.Text);
            command.Parameters.Add("pwd", pwd.Text);
        }
        catch (NullReferenceException NRE)
        {
            MessageBox.Show("Please contact your developer about this error. Thank you " + NRE);
        }

        _reader = command.ExecuteReader();
        if (_reader.Read() != true)
        {
            _failedAttempt = _failedAttempt + 1;
            while (_failedAttempt < 3)
            {
                MessageBox.Show("Incorrect Username or Password. Please try again " + "Attempts: " + _failedAttempt);
                username.ResetText();
                pwd.ResetText();
            }
        }
        else
        {
            MessageBox.Show("Welcome");
        }
    }

my connection string

this._connectionString = "Data Source=xe;Max Pool Size=50;Min Pool Size=1;Connection Lifetime=120;Enlist=true;User Id=hr;Password=hr";

So when the program is executed I am getting this error

An unhandled exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll

Additional information: External component has thrown an exception. I have been at this for the past hour any help would be appreciated.

Update

Open Connection method

public void OpenConnection()
    {
        try
        {
            if (dbconnect == null)
            {
                dbconnect = new OracleConnection(this._connectionString);
                dbconnect.Open();
                return;
            }

            switch (dbconnect.State)
            {
                case ConnectionState.Closed:
                case ConnectionState.Broken:
                    dbconnect.Close();
                    dbconnect.Dispose();
                    dbconnect = new OracleConnection(this._connectionString);
                    dbconnect.Open();
                    return;
            }
        }
        catch (OracleException oracleException)
        {
            MessageBox.Show("Database connectionString is null. Contact your developer! " + oracleException);
        }

    }

Exception Stack Trace

   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
   at DINEIN.OracleDB_Connection.Userlogin(TextBox username, TextBox pwd) in f:\My Documents\Projects\DINEIN\DINEIN\OracleDB_Connection.cs:line 92
   at DINEIN.Login.btn_login_Click(Object sender, EventArgs e) in f:\My Documents\Projects\DINEIN\DINEIN\Login.cs:line 31
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.PerformClick()
   at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
   at System.Windows.Forms.Control.PreProcessControlMessageInternal(Control target, Message& msg)
   at System.Windows.Forms.Application.ThreadContext.PreTranslateMessage(MSG& msg)
       at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FPreTranslateMessage(MSG& msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(Form mainForm)
   at DINEIN.Program.Main() in f:\My Documents\Projects\DINEIN\DINEIN\Program.cs:line 19
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

Please show your code for `OpenConnection()` method. Also, what line in your code is the last to execute before the exception occurs? Find that in the exception's stack trace and please provide. — STLDev, Feb 02 '17 at 08:45
@STLDeveloper I have updated my question with the OpenConnection() method and the exception stack trace. can you have look now. thank you — , Feb 02 '17 at 08:58
Thanks. Which line of your code in the `Userlogin()` method above is line number 92? — STLDev, Feb 02 '17 at 09:02
@STLDeveloper this ` _reader = command.ExecuteReader();` is line number 92 — , Feb 02 '17 at 09:04

STLDev · Accepted Answer · 2017-02-02T10:10:25.120

0

Rather than using your separate OpenConnection() method, consider coding with the using() statement. It will ensure that your connection an other database-related objects are always disposed.

For example:

int _failedAttempt = 0;

public void Userlogin(TextBox username, TextBox pwd)
{
  try
  {
    using (var connection = new OracleConnection(_connectionString))
    {
      connection.Open();

      using (var command = new OracleCommand())
      {
        command.CommandText = "SELECT username, user_pwd FROM dinein_system_users WHERE username= :usrname AND user_pwd= :pwd";
        command.Connection = connection;
        command.BindByName = true;

        command.Parameters.Add("usrname", username.Text);
        command.Parameters.Add("pwd", pwd.Text);

        using (var reader = command.ExecuteReader())
        {
          if (reader.Read() != true)
          {
            _failedAttempt += 1;
            if (_failedAttempt < 3)
            {
              MessageBox.Show("Incorrect Username or Password. " +
                              "Please try again. " + 
                              $"Attempts: {_failedAttempt}");
              username.ResetText();
              pwd.ResetText();
            }
            else
            {
              // 3 failed attempts
            }
          }
          else
          {
            _failedAttempt = 0;
            MessageBox.Show("Welcome");
          }
        }
      }
    }
  }
  catch(OracleException ex)
  {
    MessageBox.Show("Error: {ex}");
  }
}

edited Feb 02 '17 at 10:10

answered Feb 02 '17 at 09:16

STLDev

5,950
25
36

I tried this code an it still give me the same error at `var reader = command.ExecuteReader()` – Feb 02 '17 at 09:23
I've updated my answer to add a try-catch block. Notice that it's catching an exception of type OracleException. I think that when you look at the exception with that specific type you'll get more useful information to help you determine what's going on here. – STLDev Feb 02 '17 at 09:31
Yes now its giving `An unhandled exception of type 'System.NullReferenceException' occurred in DINEIN.exe Additional information: Object reference not set to an instance of an object.` in the CloseConnection() method – Feb 02 '17 at 09:38
I don't see any call to `CloseConnection()` in `Userlogin()`. Am I missing something? It looks like you may be trying to maintain the state of your database connection between uses of it. Instead, consider following the architecture above, and open and close your connection as it's needed. The `using` statement will do this for you automatically. – STLDev Feb 02 '17 at 09:39
I am a Implementing the `IDisposable` interface in this class the close method checks if the connection is opened and closes the connection. I have `Dispose()` method that calls the `CloseConnection()` method at the end of the class – Feb 02 '17 at 09:43
It seems that the code is trying to close a connection that doesn't exist, hence a null reference exception. Don't try to maintain the connection state outside of the function that's doing data access. If you're concerned about performance, recall that your database connections are cached by ADO.NET and the systems it uses. There's no need for you to manage the connection in the way you're doing it. – STLDev Feb 02 '17 at 09:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/134669/discussion-between-mohammed-imadh-and-stldeveloper). – Feb 02 '17 at 09:58

Oracle.DataAccess.Client.OracleException C#

1 Answers1