android.database.sqlite.SQLiteException: no such column: COLUMN_USER_USERNAME

Question

I know that StackOverflow has probably seen too many questions about no such column.
But as far as I searched, the ones I found were using rawQuery() and using a single String with the commands.
As requested by @Code-Apprentice and himself recommended me to use query() instead, I'm creating mine.

This method below connects to a table in order for the user to check login/password and then, start another Activity.
My error is "no such column".

Before posting this, I've spent quite some time searching for an answer and trying other stuff, to no avail.
So, if it is a duplicate for another, I'm sorry.

public boolean search_access(user user){
    SQLiteDatabase db = this.getReadableDatabase();
    String [] tabela_user = new String[]{"usuaid","username","password"};
    String [] whereargs   = new String[]{user.getUsername()};
    String compare_user = "COLUMN_USER_USERNAME = ?";

    Cursor cursor = db.query(TABLE_USER, tabela_user, compare_user, whereargs, null, null, null );

    cursor.moveToFirst();
    if(cursor.getString(0).equals(user.getPassword())) {
        return true;
    } else{
        CharSequence text = "Incorrect Username/Password!!";
        int duration = Toast.LENGTH_SHORT;
        Toast toast = Toast.makeText(null, text, duration);
        toast.show();
        return false;
    }
}

Error:

E/AndroidRuntime: FATAL EXCEPTION: main
              Process: com.example.relatoriodeobras, PID: 2714
              android.database.sqlite.SQLiteException: no such column: COLUMN_USER_USERNAME (code 1): , while compiling: SELECT usuaid, username, password FROM usuario WHERE COLUMN_USER_USERNAME = ?
                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
                  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
                  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
                  at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)
                  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)
                  at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)
                  at com.example.relatoriodeobras.projeto_db.busca_acesso(projeto_db.java:173) <- ERROR LINE HERE, which is Cursor's
                  at com.example.relatoriodeobras.MainActivity$1.onClick(MainActivity.java:42)
                  at android.view.View.performClick(View.java:4780)
                  at android.view.View$PerformClick.run(View.java:19866)
                  at android.os.Handler.handleCallback(Handler.java:739)
                  at android.os.Handler.dispatchMessage(Handler.java:95)
                  at android.os.Looper.loop(Looper.java:135)
                  at android.app.ActivityThread.main(ActivityThread.java:5254)
                  at java.lang.reflect.Method.invoke(Native Method)
                  at java.lang.reflect.Method.invoke(Method.java:372)
                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

EDIT: SQLiteOpenHelper class. Just added the parts related to user management.

package com.example.relatoriodeobras;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

public final class projeto_db extends SQLiteOpenHelper{

public static final String LOG = "DatabaseHelper";
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME =  "projeto_db";

public static final String TABLE_USER = "usuario";

public static final String COLUMN_USER_ENTRY_ID = "usuaid";
public static final String COLUMN_USER_USERNAME = "username";
public static final String COLUMN_USER_PASSWORD = "password";

private static final String CREATE_TABLE_USER = "CREATE TABLE "
        + TABLE_USER + "(" + COLUMN_USER_ENTRY_ID + " INTEGER PRIMARY KEY, " + COLUMN_USER_USERNAME
        + " TEXT, " + COLUMN_USER_PASSWORD + " TEXT, " + KEY_CREATED_AT + " DATETIME" + ")";

public projeto_db(Context context) {

    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db){
    db.execSQL(CREATE_TABLE_USER);
}

 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // on upgrade drop older tables
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    onCreate(db);
}

 public boolean registra_usuario(user user){    //Registra fiscais que utilizarão o sistema.registrado = true;
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    try{
        values.put(COLUMN_USER_USERNAME, add_usuario.username);
        values.put(COLUMN_USER_PASSWORD, add_usuario.password);
        values.put(KEY_CREATED_AT      , add_usuario.created_at);
        db.insert(TABLE_USER, null, values);
        db.close();
        return true;
    }catch (Exception e){
        db.close();
        return false;
    }
}

public boolean busca_acesso(user user){
    SQLiteDatabase db = this.getReadableDatabase();
    String [] tabela_user = new String[]{"usuaid","username","password"};
    String [] whereargs   = new String[]{user.getUsername()};
    String compare_user = "COLUMN_USER_USERNAME = ?";

    Cursor cursor = db.query(TABLE_USER, tabela_user, compare_user, whereargs, null, null, null );

    cursor.moveToFirst();
    if(cursor.getString(0).equals(user.getPassword())) {
        return true;
    } else{
        CharSequence text = "Incorrect Username/Password!!";
        int duration = Toast.LENGTH_SHORT;
        Toast toast = Toast.makeText(null, text, duration);
        toast.show();
        return false;
    }
}
public static String getDateTime() {
    SimpleDateFormat dateFormat = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss", Locale.getDefault());
    Date date = new Date();
    return dateFormat.format(date);
}
}}

User class

package com.example.relatoriodeobras;

import android.database.sqlite.SQLiteDatabase;


public class user {
private static projeto_db dbHelper;
private static SQLiteDatabase database;

int id = 0;
private String username;
String password;
String created_at;
static boolean registrado = false;

public int getId() {

    return id;
}

public void setId() {
    this.id = id++;
}

public String getUsername() {

    return username;
}

public void setUsername(String username) {

    this.username = username;
}

public String getPassword() {

    return password;

}

public void setPassword(String password) {

    this.password = password;
}


public void setCreated_at(String created_at) {
    this.created_at = created_at;
}

public String getCreated_at() {

    return created_at;
}
}

I'd greatly appreciate some help with code organization, because I guess you guys might probably think that it's a mess.

Presumably, the error is correct, and that your table does not have a column named `COLUMN_USER_USERNAME`. You might consider posting your full `SQLiteOpenHelper`, or at least its `onCreate()` and `onUpgrade()` methods. — CommonsWare, Sep 26 '16 at 22:02

score 1 · Accepted Answer · answered Sep 26 '16 at 22:32

1

Your error doesn't lie. You don't have a column with the name COLUMN_USER_USERNAME.

You do have a variable with that name, though.

The string value for it is "username"

Option 1:

String compare_user = projeto_db.COLUMN_USER_USERNAME + " = ?";

Option 2:

String compare_user = "username = ?";

^{Aside: Do not store passwords in plain-text}

answered Sep 26 '16 at 22:32

OneCricketeer

179,855
19
132
245

I know that. This is just a test I'm doing. Since I didn't went that deep for cryptography yet. But thanks for the explanation. I'll look for a way to solve this. – HextechEnthusiast Sep 26 '16 at 22:46

android.database.sqlite.SQLiteException: no such column: COLUMN_USER_USERNAME

1 Answers1