Login takes the first user only

Question

I have an login which works, but i only can login with the first user in the table

Can anyone explain why?

private void btnLogin_Click(object sender, RoutedEventArgs e)
{
    SqlConnection sqlCon = new SqlConnection(@"Data Source=user\SQLEXPRESS; User ID=user;password=password;Initial Catalog=login;Trusted_Connection=True;");
    try
    {
        if (sqlCon.State == System.Data.ConnectionState.Closed)
            sqlCon.Open();

        string query = "SELECT * FROM tblUser WHERE Username=@Username AND Password=@Password";
        SqlCommand sqlCmd = new SqlCommand(query, sqlCon);
        sqlCmd.CommandType = System.Data.CommandType.Text;
        sqlCmd.Parameters.AddWithValue("@Username", txtUsername.Text);
        sqlCmd.Parameters.AddWithValue("@Password", pwd.Password);
        int count = Convert.ToInt32(sqlCmd.ExecuteScalar());

        if (count == 1)
        {
            MainDashboard window = new MainDashboard();
            this.Close();
            window.ShowDialog();
        }
        else
        {
            MessageBox.Show("Wrong Username or Password");
        }
    }
    catch (Exception)
    {

        throw;
    }
}

my table name is tblUsers and i have 2 users but i can only succesfully login with the first user in my table

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executescalar?view=netframework-4.7.2 Says following "[scalar method] Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored."... From same page, "Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database." — MaxB, Oct 22 '18 at 12:39
Side note: [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and [AddWithValue is Evil](http://www.dbdelta.com/addwithvalue-is-evil/) — Filburt, Oct 22 '18 at 12:42
@AhmadTI you still have some issues in your code. Consider using an `using` statement with the `SqlConnection` and `SqlCommand` objects - example: https://stackoverflow.com/q/4717789/558486 — Rui Jarimba, Oct 22 '18 at 12:59

Rui Jarimba · Accepted Answer · 2018-10-22T13:02:29.037

According to the documentation, ExecuteScalar executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

So instead of

string query = "SELECT * FROM tblUser WHERE Username=@Username AND Password=@Password";

Try

string query = "SELECT COUNT(*) FROM tblUser WHERE Username=@Username AND Password=@Password";

Explanation:

Using ExecuteScalar() with SELECT * will return the value of the first column of the row (I suppose that in your case it's the ID/primary key of table tbluser?)
Using ExecuteScalar() with SELECT Count(*) will return 1 if the username and password matches (assuming there are no username/password duplicates).

Login takes the first user only

1 Answers1