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()
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}");
}
}