Hmm.. have you tried to use this select statement directly within SQL Studio? The error is indicating a problem with your query. It looks like the value of the user name was not passed correctly. Does the username include an apostrophe? If so, the way you are implementing this is problematic. Its generally recommended to use parameterized SQL instead so that these values are treated as literals.
For example here is a c# example....
strSQL = "SELECT COUNT(*) FROM member where Username = @Username AND Password = @Password";
SqlCmd cmd = new SQLCommand(strSQL);
Int32 count = (Int32) cmd.ExecuteScalar();
This code may/may not work as expected as I wrote it from memory...