SQLiteDatabase help

[BlueChinchillaEatingDorito]

Man I seriously hate this assignment. Causing nothing but problems and the instructor isn't helping one bit by constantly changing requirements last minute. Anyways, I cannot get any bit of my SQLiteDatabase to work properly. If I try to insert a user, it gives me a SQLiteException saying the table contains no column name lastName when the column names are defined and match. And when I try to use the login function, it just gives me an error near "get going" syntax error (code 1) while compiling the "get login information from loginData". Any ideas?

 

package ca.sfu.cs.cmpt276.gerlandl_as2;

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

public class Database extends SQLiteOpenHelper {
	
	SQLiteDatabase login_db;
	
	public static final String DATABASE_NAME = "login.db";
	public static final String TABLE_NAME = "loginData";
	public static final String COLUMN_STUDENT_ID ="ID";
	public static final String COLUMN_FIRST_NAME ="firstName";
	public static final String COLUMN_LAST_NAME ="lastName";
	public static final String COLUMN_PASSWORD ="password";
	
	public static final int COLUMN_ID = 0;
	public static final int COLUMN_FIRSTNAME = 1;
	public static final int COLUMN_LASTNAME = 2;
	public static final int COUMNL_PASSWORD = 3;
	
	public static final String[] KEYS = new String[] {COLUMN_STUDENT_ID, COLUMN_FIRST_NAME, COLUMN_LAST_NAME, COLUMN_PASSWORD};
	
	public Database(Context context) {
		super(context, DATABASE_NAME, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String exec = "CREATE TABLE " + TABLE_NAME + "(COLUMN_STUDENT_ID integer primary key ,COLUMN_FIRST_NAME text ,"
				+ "COLUMN_LAST_NAME text ,COLUMN_PASSWORD text)";
		db.execSQL(exec);
		this.login_db = db;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		this.onCreate(db);
	}
	
	public void insertRow(String ID, String firstName, String lastName, String password) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues contentvalues = new ContentValues();
		contentvalues.put(COLUMN_STUDENT_ID, ID);
		contentvalues.put(COLUMN_FIRST_NAME, firstName);
		contentvalues.put(COLUMN_LAST_NAME, lastName);
		contentvalues.put(COLUMN_PASSWORD, password);
		db.insert(TABLE_NAME, null, contentvalues);
		db.close();
	}
	
	public Boolean login(String first_Name, String last_Name, String entered_password, String student_ID){
		SQLiteDatabase db = this.getReadableDatabase();
		String where = null;
		Cursor data = db.query(true, TABLE_NAME, KEYS, 
				where, null, null, null, null, null);
		if (data.moveToFirst()){
			do{
				if(data.getString(0).equals((String) student_ID)){
					if(data.getString(1).equals((String) first_Name)){
						if(data.getString(2).equals((String) last_Name)){
							if(data.getString(3).equals((String) entered_password)){
								return true;
							}
						}
					}
				}
			}while(data.moveToNext());
		}
		data.close();
		return false;
	}
	
	public boolean checkExists(String studentID){
		SQLiteDatabase db = this.getReadableDatabase();
		String where = null;
		Cursor cursor = db.query(true, TABLE_NAME, KEYS, 
				where, null, null, null, null, null);
		if (cursor.moveToFirst()){
			do{
				if (cursor.getString(0).equals(studentID)){
					cursor.close();
					return true;
				}
			}while(cursor.moveToNext());
		}
		cursor.close();
		return false;
	}
}

 

 

 

[TheRandomness]

-Thread moved to Programming.

[Organized]

Check out the sqlite database with this tool: http://sqlitebrowser.org/

 

I think the problem is, that you define e.g. COLUMN_FIRST_NAME as a constant, but when you create the table, you are not referring to this constant but naming the corresponding column "COLUMN_FIRST_NAME":

 

"CREATE TABLE " + TABLE_NAME + "(COLUMN_STUDENT_ID integer primary key ,COLUMN_FIRST_NAME text ,"
                                 |||||||||||||||||                      |||||||||||||||||

I think what you really want to do is:

"CREATE TABLE " + TABLE_NAME + "(" + COLUMN_STUDENT_ID + " integer primary key ," + COLUMN_FIRST_NAME + " text ,"
                                 ||||                 ||||                      ||||                 ||||

Right?

[BlueChinchillaEatingDorito]

3 minutes ago, Organized said:

-snip-

I'll try that first!

[Organized]

You really should have a look into your sqlite database. I'm sure you will find out really quickly why it's not working.

[BlueChinchillaEatingDorito]

7 minutes ago, Organized said:

You really should have a look into your sqlite database. I'm sure you will find out really quickly why it's not working.

This is my first ever time using SQL and programming in Android. It's frustrating how I only have 2 weeks to do this and the instructor isn't even going to go over Android or Java programming.

 

 

Edit: Tried that and it didn't fix the issue. Posted images from LogCat

[P4ZD4]

6 hours ago, Organized said:

I think what you really want to do is:

"CREATE TABLE " + TABLE_NAME + "(" + COLUMN_STUDENT_ID + " integer primary key ," + COLUMN_FIRST_NAME + " text ,"
                                 ||||                 ||||                      ||||                 ||||

Right?

^ This should have worked.

 

You're column names should be firstName, lastName and password but instead are set to FIRST_NAME, LAST_NAME and PASSWORD. Debug onCreate() and seeing what String exec is set to.

 

edit-

On second thought maybe your column names are getting converted since column names aren't case sensitive. So everything is getting turned upper snakecase.

[Yamoto42]

That second line of the log appears to be correct, the table in your database has no field "firstName".

Double check you did implement the CREATE TABLE statement correctly.

[BlueChinchillaEatingDorito]

12 hours ago, P4ZD4 said:

^ This should have worked.

 

You're column names should be firstName, lastName and password but instead are set to FIRST_NAME, LAST_NAME and PASSWORD. Debug onCreate() and seeing what String exec is set to.

 

edit-

On second thought maybe your column names are getting converted since column names aren't case sensitive. So everything is getting turned upper snakecase.

Spoiler

	@Override
	public void onCreate(SQLiteDatabase db) {
		String exec = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN_STUDENT_ID +" integer primary key ," +
				COLUMN_FIRST_NAME + "text ," + COLUMN_LAST_NAME + "text ," + COLUMN_PASSWORD + " text" + ")" ;
		db.execSQL(exec);
		this.login_db = db;
	}

 

 

5 hours ago, Yamoto42 said:

That second line of the log appears to be correct, the table in your database has no field "firstName".

Double check you did implement the CREATE TABLE statement correctly.

Did that and it still doesn't seem to be working. Which statement defines the name of the columns?

public static final String COLUMN_FIRST_NAME ="firstName";

Is it FIRST_NAME from COLUMN_FIRST_NAME or the "firstName"?

[BlueChinchillaEatingDorito]

I seem to have solved the problem by deleting the old database after doing some changes to the code. It was the following lines that caused the issue. I originally did not have spaces before and after the text and comma after the column names which caused the column to be named "firstNametext," with no field type.

	@Override
	public void onCreate(SQLiteDatabase db) {
		String exec = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN_STUDENT_ID +" integer primary key, " +
				COLUMN_FIRST_NAME + " text, " + COLUMN_LAST_NAME + " text, " + COLUMN_PASSWORD + " text" + ")" ;
		db.execSQL(exec);
		this.login_db = db;
	}

The code above should produce the proper column names and field types. Thanks for the help everyone! Still need to test it more, but it managed to register and log in like a charm on the first run.

 

Edit: Would not recommend ID to be an integer if there is a possibility that that value may have leading 0s. If you input for example "00000", it'll be written as "0" on the database. Just something to watch out for.