C# - Multiple Users from SQL Database for Login Form

Question

So, I have a loginform where a user has to login to go into the mainform. I have a database with a table created to store usernames and passwords, for logging in to the application.

If the user types in the correct username and password and clicks login, it should take him/her to the mainform. This I know how to do, but how do I get the usernames and passwords from the SQL database and check if they exist, and if they exist, is it the correct username for the correct password or vice versa?

Like I said, I created a SQL database to store usernames and passwords. I then saved a user with the username and password both as "admin", just for testing purposes.

I tried this following code, but it isn't letting me log in even though I typed the correct username and password.

string username;
string password;

private void btnLogin_Click(object sender, EventArgs e)
{
    try
    {
        SqlCeConnection con = new SqlCeConnection(@"connectionString");
        SqlCeCommand com = new SqlCeCommand("SELECT username, password FROM UsersPass WHERE username = '" + txtUsername.Text + "' AND password = '" + txtPassword.Text + "'", con);
        con.Open();

    if (con.State == ConnectionState.Open)
    {
        SqlCeDataReader dtr = com.ExecuteReader();

        while (dtr.Read())
        {
            username = dtr["username"].ToString();
            password = dtr["password"].ToString();
            if (username == txtUsername.Text && password == txtPassword.Text)
            {
                Mainform frm = new Mainform();
                frm.Show();
                this.Hide();     
            }
            else
            {
                MessageBox.Show("Invalid credentials!\nPlease enter a valid username and password to continue.", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
    }
    catch (Exception)
    {
        MessageBox.Show("Erorr", "Error");
    }
}

Forgive me if I'm missing something completely obvious, I'm fairly new to C#. Thank you in advance.

Answer 1

You say you are getting the error message. Start by giving yourself more information on that. Either place a breakpoint in

catch (Exception)
{
    MessageBox.Show("Erorr", "Error");
}

so you can see some details on the exception or change it to

catch (Exception ex)
{
    MessageBox.Show("Erorr", ex.Message + Environment.NewLine + ex.StackTrace);
}

That will give you details on exactly why your application is failing and set you on a path towards getting things working like you want.

I suspect you have a bad connection string.

Edit: This particular issue was caused by sql server compact edition references being used in place of standard edition references. See the comments.