Thread: Login validating from a local and remote db

  1. #1
    Registered User
    Join Date
    Jun 2015
    Posts
    8

    Login validating from a local and remote db

    OK, back-story, I am only doing this the way I am because it is required by the boss... Here's what I'm doing:

    I have a local SQL database that contains 6 employee numbers (these numbers indicate admins and setup managers), if someone logs into the program and is in this database, certain features become active. If they are not in this database, we look into an Oracle database with all employee numbers in order to ensure a proper employee number is logging in. Here is what I have:
    Code:
    private void button_Login_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection("Data Source=DAHLENO0744\\;Initial Catalog=USPCNew;Integrated Security=True;Pooling=False");
                try
                {
                    con.Open();
                }
                catch (Exception)
                {
                    MessageBox.Show("Data Connection Failed");
                }
                String liq = "SELECT * FROM Employee where EmpNum=@EmpNum";
                SqlCommand lli = new SqlCommand(liq, con);
                lli.Parameters.AddWithValue("@EmpNum", this.textBox1.Text);
                SqlDataReader dr1 = lli.ExecuteReader();
                while (dr1.Read())
                {
                    if (dr1.HasRows == true)
                    {
                        MessageBox.Show("Login Successful");
                        this.Hide();
                        MainForm ss = new MainForm();
                        ss.Show();
                        con.Close();
                    }
                }
                if (dr1.HasRows == false)
                {
                    con.Close();
                    OdbcConnection con2 = new OdbcConnection("Driver={Microsoft ODBC for Oracle};Dsn=dahp;uid=admin;pwd=passw;server=dahp");
                    try
                    {
                        con2.Open();
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Data Connection Failed");
                    }
                    OdbcCommand rli = con2.CreateCommand();
                    rli.CommandText = "SELECT * FROM VWEMPLOYEE where EMPNUM=:EMPNUM";
                    rli.Parameters.AddWithValue(":EMPNUM", this.textBox1.Text);
                    OdbcDataReader dr2;
                    dr2 = rli.ExecuteReader();
                    while (dr2.Read())
                    {
                        if (dr2.HasRows == true)
                        {
                            MessageBox.Show("Login Successful");
                            this.Hide();
                            MainForm ss = new MainForm();
                            ss.Show();
                            con2.Close();
                        }
                        if (dr2.HasRows == false)
                        {
                            MessageBox.Show("Please enter valid Employee Number");
                        }
    
                    }
                }
                
            }
    Any idea why this doesn't work?

  2. #2
    Registered User
    Join Date
    Jun 2015
    Posts
    8
    So, I got it... here is the code, in case anyone needs to do this in the future

    Code:
    private void button_Login_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection("Data Source=DAHLENO0744\\;Initial Catalog=USPCNew;Integrated Security=True;Pooling=False");
                OdbcConnection con2 = new OdbcConnection("Driver={Microsoft ODBC for Oracle};Dsn=dahp;uid=admin;pwd=passw;server=dahp");
                try
                {
                    con.Open();
                }
                catch (Exception)
                {
                    MessageBox.Show("Data Connection Failed");
                }
                String liq = "SELECT * FROM Employee where EmpNum=@EmpNum";
                SqlCommand lli = new SqlCommand(liq, con);
                lli.Parameters.AddWithValue("@EmpNum", this.textBox1.Text);
                SqlDataReader dr1 = lli.ExecuteReader();
                while (dr1.Read())
                {
                    if (dr1.HasRows == true)
                    {
                        MessageBox.Show("Login Successful");
                        this.Hide();
                        MainForm ss = new MainForm();
                        ss.Show();
                        con.Close();
                    }
                }
                if (dr1.HasRows == false)
                {
                    con.Close();
                    try
                    {
                        con2.Open();
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Data Connection Failed");
                    }
                    finally
                    {
                        OdbcCommand rli = con2.CreateCommand();
                        rli.CommandText = "SELECT * FROM PLANT.VWEMPLOYEE where EMPNUM=EMPNUM";
                        rli.Parameters.AddWithValue("EMPNUM", this.textBox1.Text);
                        OdbcDataReader dr2;
                        dr2 = rli.ExecuteReader();
                        while (dr2.Read())
                        {
                            if (dr2.HasRows == true)
                            {
                                MessageBox.Show("Login Successful");
                                this.Hide();
                                MainForm ss = new MainForm();
                                ss.Show();
                                break;
                            }
                            else
                            {
                                MessageBox.Show("Please enter valid Employee Number");
                            }
                        }
                    }
                }
                con2.Close();

  3. #3
    Registered User
    Join Date
    Jun 2015
    Posts
    8
    OK, well, unfortunately, it allows anyone to log in. So this didn't really work... you can log in without an employee number at all

  4. #4
    Sweet
    Join Date
    Aug 2002
    Location
    Tucson, Arizona
    Posts
    1,819
    You need to change the parameter passing logic in the second if. The Oracle one.

    This:
    Code:
    rli.CommandText = "SELECT * FROM PLANT.VWEMPLOYEE where EMPNUM=EMPNUM";
    rli.Parameters.AddWithValue("EMPNUM", this.textBox1.Text);
    Should be:
    Code:
    rli.CommandText = "SELECT * FROM PLANT.VWEMPLOYEE where EMPNUM=:EMPNUM";
    rli.Parameters.AddWithValue(":EMPNUM", this.textBox1.Text);
    Not saying that will 100% work just did a quick search on how to pass parameters to Oracle but should get you on right track
    Woop?

  5. #5
    train spotter
    Join Date
    Aug 2001
    Location
    near a computer
    Posts
    3,868
    Just for future reference....

    You should check the input (in textBox1 ) is a valid ID prior to calling the DB.

    You should always check the exact number of rows returned, if rows returned is not 1 then login is unsuccessful.

    If I entered into the textbox "12345 or 1=1" would I get logged in?

    Your main Oracle code is in the 'finally' block.
    This is not good practice, the finally block should be used for clean-up only (closing the connection).
    Your code risks throwing an exception in the 'finally' and leaving DB connections open.
    Last edited by novacain; 09-11-2015 at 08:37 PM.
    "Man alone suffers so excruciatingly in the world that he was compelled to invent laughter."
    Friedrich Nietzsche

    "I spent a lot of my money on booze, birds and fast cars......the rest I squandered."
    George Best

    "If you are going through hell....keep going."
    Winston Churchill

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 6
    Last Post: 01-02-2015, 10:13 PM
  2. Remote login
    By ajayb in forum C Programming
    Replies: 3
    Last Post: 01-17-2011, 02:06 AM
  3. Replies: 8
    Last Post: 12-08-2009, 12:55 PM
  4. How to keep local and remote DB in sync
    By nicksnels in forum Tech Board
    Replies: 0
    Last Post: 09-10-2007, 02:17 AM
  5. Intercepting Data Bound for Local Application from Remote Server
    By maththeorylvr in forum Networking/Device Communication
    Replies: 2
    Last Post: 11-29-2005, 01:57 AM

Tags for this Thread