Mohammed Imadh Mohammed Imadh - 2 years ago 731
C# Question

Oracle.DataAccess.Client.OracleException C#

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()

Answer Source

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}");
  }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download