Check access database for a name

Question

I have an access table called LoginTable with text columns named Username, Password and a integer column called group. A windows form called AddUser with a textbox called Username_txtBx and a combobox called Department_cmbBx. and also a button called Add_btn . I can add a user with the following code in the button click event. But how would I go about having it Search the database to check if the Username already exists and if it does throw a messagebox telling the user it does and if it doesn't run the code below. I have found a lot of examples for SQL databases but none for an Access database.

try
{
    int g = new int();

    if (Department_cmbBx.SelectedItem.ToString() == "Office")
    {
        g = 1;
    }
    else if (Department_cmbBx.SelectedItem.ToString() == "Stores")
    {
        g = 2;
    }
    else if (Department_cmbBx.SelectedItem.ToString() == "Workshop")
    {
        g = 3;
    }
    else if (Department_cmbBx.SelectedItem.ToString() == "Management")
    {
        g = 4;
    }
    else if (Department_cmbBx.SelectedItem.ToString() == "Admin")
    {
        g = 5;
    }

    connection.Open();
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection;
    command.CommandText = "insert into LoginTable(Username,[Password],[Group]) values ('" + Username_txtBx.Text + "','password'," + g + ")";
    command.ExecuteNonQuery();
    connection.Close();
    Username_txtBx.Text = "";
    Department_cmbBx.Text = "";                
}
catch (Exception ex)
{
    MessageBox.Show("error   " + ex);
}

Either sql or access the select query won't change, use the example you saw for SQL — Krishna
– Krishna, Commented Apr 25, 2017 at 13:55
It appears you are storing passwords in plaintext in your database. This is a serious security vulnerability. You should never do this. Passwords should be one way hashed and salted, and you should compared hashed forms of the passwords in order to verify them. — mason
– mason, Commented Apr 25, 2017 at 14:10

mason · Accepted Answer · 2017-04-25 14:19:13Z

First, your if statements can be replaced with a more efficient and readable switch statement.

Second, you can use a OleDbDataReader with the following select query to check if the username already exists in your table.

Please note I am using Command.Parameters.Add which is more reliable and best practice when writing SQL commands as string.

int g;
bool UserExists = false;

switch(Department_cmbBx.SelectedItem.ToString())
{
    case "Office":
       g = 1;
       break;
    case "Stores":
       g = 2;
       break;
    case "Workshop":
       g = 3;
       break;
    case "Management":
       g = 4;
       break;
    case "Admin":
       g = 5;
       break;   
    default:
       MessageBox.Show("error: an invalid value.");
       break;
}

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    using (OleDbCommand command = new OleDbCommand("select [Username] from LoginTable where Username=@Username" , connection))
    {
        command.Parameters.Add("@Username", Username_txtBx.Text); 
        connection.Open();

        using(OleDbDataReader reader = command.ExecuteReader())
        {
            // If at least 1 row was returned, this means the user exists in the table.
            while (reader.Read())
            {
                UserExists = true;
            }
        }    

        if (!UserExists) 
        {
            // The user does not exists - you can create it.
            command.Parameters.Clear();
            command.CommandText = "insert into LoginTable([Username],[Password],[Group]) values (@Username,@Username,@G)";
            command.Parameters.Add("@Username", Username_txtBx.Text); 
            command.Parameters.Add("@Password", "password"); 
            command.Parameters.Add("@G", g);
            command.ExecuteNonQuery();
        }
        else
        {
            // Show an error message - the user already exists
            MessageBox.Show("The user you eneterd already exists.");
        }
    }
}

please don't encourage string concatenation for sql commands.
Although the answer is correct, I would suggest the use of parameters in order to avoid sql injection. You can see an example here msdn.microsoft.com/en-us/library/…
@Nino You are 100% correct, but I'm not about to teach the OP about command parameters, it seems to me he is in a learning process so I decided to answer the question as is without too many modifications.
If someone is learning, then this is absolutely the right time to teach parameterization. Don't teach them the wrong way to do things.

Paul Sharrock · Accepted Answer · 2017-04-25 17:08:18Z

Thanks Mason I couldn't get your code to work for some reason but did use the switch statement out of it and a select query like others have said. I ended up using the following code

               bool UserExists = false;
            command.CommandText = "Select [Username] from LoginTable where Username = '" + Username_txtBx.Text + "'";
            OleDbDataReader reader = command.ExecuteReader();

            int g = new int();
            while (reader.Read())
            {
                UserExists = true;
            }
            connection.Close();
            if (!UserExists)
            {

Collectives™ on Stack Overflow

Check access database for a name

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related