no value given for one or more required parameters. oledb c#

Question

I have created a simple login (C# Windows Forms) with MS Access Database(2016), but when I tried to log in, in the username field if I enter uppercase or lowercase letters its given a successful login.Then after that I used COLLATE in my SELECT query to avoid this error, but after this its always given "no value given for one or more parameters" error

string username = textBox1.Text.Trim();
string password = textBox2.Text.Trim();

try
{
    if (username == "" || password == "")
    {
        MessageBox.Show("Please Fill All Credentials to Login !", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    db.OpenConnection();
    da = new OleDbDataAdapter(" SELECT * FROM tblusers WHERE [username = '" + username + "' COLLATE SQL_Latin1_General_CP1_CS_AS] AND [password = '" + password + "' COLLATE SQL_Latin1_General_CP1_CS_AS] ", db.con);
    DataSet ds = new DataSet();
    da.Fill(ds, "tblusers");
    db.CloseConnection();

    if (ds.Tables[0].Rows.Count == 0)
    {
        MessageBox.Show("Invalid User Name or Password! Please Check Your Credentials!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }

    else
    {
        this.Hide();
        frmDashboard dashboard = new frmDashboard();
        dashboard.Show();
    }
}

Answer 1

You are trying to use the COLLATE clause in your SQL query, which is specific to SQL Server and not supported by MS Access.

In MS Access, you can use the StrComp function to perform a case-sensitive search. To modify your code, update your OleDbDataAdapter query to use StrComp instead of COLLATE.

da = new OleDbDataAdapter("SELECT * FROM tblusers WHERE StrComp([username], '" + username + "', 0) = 0 AND StrComp([password], '" + password + "', 0) = 0", db.con);