vendredi 18 décembre 2015

Retrieve data based on UserID but error: No such column

I am a newbie for Android Studio. Now Im having problems that I would like to retrieve the data inserted by user using the user id but it cames with the error of "No such data"... I had tried all possible solutions but failed to proceed.. I would like to heard some explanation where am I wrong so that I can proceed.. Thank you.

Below are my code:

DatabaseHelper.java

package com.smartexamorganizer.database;

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

public class DatabaseHelper extends SQLiteOpenHelper {
    SQLiteDatabase db;
    public static final String DATABASE_NAME = "exam.db";
    public static final int DATABASE_VERSION = 1;

    public DatabaseHelper(Context context) {
        super(context,DATABASE_NAME, null, DATABASE_VERSION);
        Log.d("Database operations", "Database successfully created!");
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USER = "CREATE TABLE " +
                user.TABLE_USER + "(" +
                user.USER_ID + " INTEGER PRIMARY KEY NOT NULL," +
                user.USER_EMAIL + " VARCAHR NOT NULL," +
                user.USER_PASS + " VARCAHR NOT NULL," +
                user.USER_CPASS + " VARCAHR NOT NULL);";

        String CREATE_SUBJECT = "CREATE TABLE " +
                subject.TABLE_SUBJECT + "(" +
                subject.USER_ID + " INTEGER," +
                subject.SUBUSER_ID + " INTEGER PRIMARY KEY NOT NULL," +
                subject.SUBJECT_NAME + " TEXT NOT NULL," +
                subject.SUBJECT_RESULTS + " TEXT NOT NULL," +
                subject.SUBJECT_HOURS + " TEXT NOT NULL);";

        db.execSQL(CREATE_USER);
        db.execSQL(CREATE_SUBJECT);
        Log.d("Database operations", "Table created!");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + user.TABLE_USER);
        db.execSQL("DROP TABLE IF EXISTS " + subject.TABLE_SUBJECT);
        onCreate(db);
    }

    //insert User
    public boolean insertUser(String uid, String email, String pass, String cpass) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues cv = new ContentValues();

        cv.put(user.USER_ID, uid);
        cv.put(user.USER_EMAIL, email);
        cv.put(user.USER_PASS, pass);
        cv.put(user.USER_CPASS, cpass);
        long user_id =  db.insert(user.TABLE_USER, null, cv);

        if (user_id == -1){
            return false;
        }else
            return true;
    }

    //get User
    public Cursor getUser(){
        SQLiteDatabase db = this.getReadableDatabase();
        String selectQuery = "SELECT "+ user.USER_ID + ", " + user.USER_PASS + " FROM " + user.TABLE_USER;

        Cursor c = db.rawQuery(selectQuery, null);

        return c;
    }

    //insert Subjects
    public boolean insertSubjects(String uid, String sname, String sresults, String shours) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues cv = new ContentValues();

        cv.put(subject.SUBUSER_ID, uid);
        cv.put(subject.SUBJECT_NAME, sname);
        cv.put(subject.SUBJECT_RESULTS, sresults);
        cv.put(subject.SUBJECT_HOURS, shours);
        long sub_id =  db.insert(subject.TABLE_SUBJECT, null, cv);

        if (sub_id == -1){
            return false;
        }else
            return true;
    }

    public Cursor getSubjects(){
        SQLiteDatabase db = this.getReadableDatabase();
        String selectQuery = "SELECT "+ subject.SUBUSER_ID +
                             ", " + subject.SUBJECT_NAME +
                             ", " + subject.SUBJECT_RESULTS +
                             ", " + subject.SUBJECT_HOURS +
                             " FROM " + subject.TABLE_SUBJECT +
                             " WHERE " + subject.SUBUSER_ID +
                             " = " + subject.USER_ID;

        Cursor c = db.rawQuery(selectQuery, null);
        return c;
    }
}

subject.java

public class subject {
    public static final String SUBUSER_ID = "etUID";
    public static final String USER_ID = user.USER_ID;
    public static final String SUBJECT_NAME = "etSubjects";
    public static final String SUBJECT_RESULTS = "etResults";
    public static final String SUBJECT_HOURS = "etHours";
    public static final String TABLE_SUBJECT = "subjects";
}

user.java

public class user {
    public static final String USER_ID = "etID";
    public static final String USER_EMAIL = "etEmail";
    public static final String USER_PASS = "etPassword";
    public static final String USER_CPASS = "etCPassword";
    public static final String TABLE_USER = "user";
}

Aucun commentaire:

Enregistrer un commentaire