Devart Forums

Code: Select all

private void btnLogin_Click_1(object sender, EventArgs e)
        {
            //populatelblEmp_id_no();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandTimeout = 0;
            cmd.CommandText = "hhrcv_logon_validation";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("pv_emp_username", OracleDbType.VarChar).Value = txtUsername.Text;
            cmd.Parameters.Add("pv_emp_password", OracleDbType.VarChar).Value = txtPassword.Text;
            cmd.Parameters.Add(new OracleParameter("pv_return_message", OracleDbType.VarChar));
            cmd.Parameters["pv_return_message"].Direction = ParameterDirection.Output;
            string valid;
            conn.Open();
            cmd.ExecuteNonQuery();
            valid = cmd.Parameters["pv_return_message"].Value.ToString();
            if (valid.ToString() == "")
            {
                frmHome main = new frmHome(lblEmp_id_no.Text);
                main.Show();
                this.Hide();
            }
            else
            {
                MessageBox.Show("" + valid, "Error");
            }
            conn.Close();
        }

Code: Select all

create or replace
PROCEDURE          hhrcv_logon_validation (
                                                pv_emp_username in varchar2, 
                                                pv_emp_password in varchar2,
                                                pv_return_message out varchar2) is

  lv_emp_username   dc_emp.emp_username%type;
  e_stop_error      exception;
  
  cursor c_emp_exists is 
    select upper(emp_username)
      from dc_emp
     where emp_username = upper(pv_emp_username);                                   

  cursor c_emp_password_exists is 
    select upper(emp_username)                     
      from dc_emp
     where emp_username = upper(pv_emp_username)
       and emp_password = upper(pv_emp_password);                                    
begin
  open c_emp_exists;
    fetch c_emp_exists into lv_emp_username;
    if c_emp_exists%notfound then
      pv_return_message := 'Invalid Employee Username.';
      raise e_stop_error;    
    end if;
  close c_emp_exists;
   
  open c_emp_password_exists;
    fetch c_emp_password_exists into lv_emp_username;
    if c_emp_password_exists%notfound then
      pv_return_message := 'Invalid Password. Please try again.';
      raise e_stop_error;     
    end if;
  close c_emp_password_exists; 
  
exception
  when e_stop_error then
    null; 
  when others then
    pv_return_message := 'Others '||sqlerrm;
end;