What is the right way to write SQLite DataBase Query to get User and pass form Users Table

Question

I have table for user info and need to get to user password and user user name Query:

 Cursor cursor = db.rawQuery("select "+User.COL_NAME +User.COL_PASS +" from " + User.TABLE_NAME+" WHERE "+User.COL_NAME+ User.COL_PASS +"=?", new String[]{UserName , PassWord});

But I can't do that, the error message is:

   Caused by: android.database.sqlite.SQLiteException: no such column: UserNamePassword (code 1): , while compiling: select UserNamePassword from users WHERE UserNamePassword=?

This is my code

public String getUserByUserAndPassword(String UserName , String PassWord) {


    String All_info="" ;

    Cursor cursor = db.rawQuery("select "+User.COL_NAME +User.COL_PASS +" from " + User.TABLE_NAME+" WHERE "+User.COL_NAME+ User.COL_PASS +"=?", new String[]{UserName , PassWord});

    while (cursor.moveToNext()) {
        String user_1 = cursor.getString(cursor.getColumnIndex(User.COL_NAME));
        String pass_1 = cursor.getString(cursor.getColumnIndex(User.COL_PASS));
        All_info= user_1+"/"+pass_1;


    }
    cursor.close();
    return All_info ;}

Caused by: android.database.sqlite.SQLiteException: no such column: UserNamePassword (code 1):, while compiling: select UserNamePassword from users WHERE UserNamePassword=?

Answer 1

Your query has syntax errors, but first I want to mention other parts of your code that are logically wrong.
Your query should return 0 or 1 row because I guess there is no case of 2 users with the same user name, right?
So there is no need for a while loop. A simple if statement is enough.
Also why do you want the query to return the user name and password?
You already know them because you use them to search for this user.
If there is no user with these data then the query will not return anything.
So change your query to this:

Cursor cursor = db.rawQuery(
    "SELECT COUNT(*) FROM " + User.TABLE_NAME +
    " WHERE "+ User.COL_NAME + " = ? AND " + User.COL_PASS +" = ?", 
    new String[]{UserName , PassWord}
);

This query will return only 1 row with 1 integer column with value 0 if there is no such user and 1 if there is.
And the rest of the code needed:

if (cursor.moveToFirst()) {
    int counter = cursor.getInt(0);
    if (counter > 0) {
        All_info= UserName + "/" + PassWord;
    }
}
cursor.close();
return All_info;