Firstly sql query syntax was incorrect - that is why you were getting error.
As you don't need column value from query, it is better to use ExecuteScalar()
Secondly it is recommended to use make query safe otherwise it violates SQL injection.
OleDbCommand myCommand = new OleDbCommand(checkUser, myConnection);
myConnection.Open();
String checkPassword = "select count(*) from client where [password] = ?";
OleDbCommand passComm = new OleDbCommand(checkPassword, myConnection);
OleDbParameter p1 = new OleDbParameter();
p1.Value = passwrdloginBox.Text;
passComm.Parameters.Add(p1);
int rowsAffected = (int)passComm.ExecuteScalar();
if (rowsAffected > 0)
{
Session[""] = nameloginBox.Text;
Response.Write("Password Correct");
}
else
{
Response.Write("Password Incorrect");
}