Kevik Kevik - 1 month ago 5
Android Question

SQLite two tables, one works, the other does not

I have an SQLite database in an Android app. One database with two tables. simple read in some text and read it out, however, the first of two tables works perfectly and the second table does not and gives errors. I have looked at my code and it seems all correct. I dare anyone to find an error in my code or SQL statements below.

Especially interested in the SQL statements, because my SQL code is PERFECT as far as I know, for both tables, however in the LOGCAT says that a there is no table that I am reading into for table two.

Why would one of my tables work and the other not? Yet they are in the same database and written the same way.

DATABASE OPERATION ON FIRST TABLE; (WORKS PERFECTLY)

ourHelper = new DbHelper(ourContext);
ourDatabase = ourHelper.getWritableDatabase();

ContentValues cv = new ContentValues();
cv.put(KEY_NAME, name);
cv.put(KEY_HITS, hits);
ourDatabase.insert(DATABASE_TABLE_1, null, cv);

public String getData() {
String[] columns = new String[] { KEY_ROWID, KEY_NAME, KEY_HITS };
Cursor c = ourDatabase.query(DATABASE_TABLE_1, columns, null, null, null,
null, null);
String result = "";

int iRow = c.getColumnIndex(KEY_ROWID);
int iName = c.getColumnIndex(KEY_NAME);
int iHits = c.getColumnIndex(KEY_HITS);

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
result = result + c.getString(iRow) + " " + c.getString(iName)
+ " " + c.getString(iHits) + "\n";
}

return result;
}


ourHelper.close();


DATABASE OPERATION ON SECOND TABLE; (DOES NOT WORK, ERRORS)

ourHelper = new DbHelper(ourContext);
ourDatabase = ourHelper.getWritableDatabase();

ContentValues cv = new ContentValues();
cv.put( KEY_RESULT, result);
return ourDatabase.insert(DATABASE_TABLE_2, null, cv);

public String getData2() {
// TODO Auto-generated method stub
String[] columns = new String[] { KEY_ROWID, KEY_RESULT, KEY_TABLET, KEY_DATE };
Cursor c = ourDatabase.query(DATABASE_TABLE_2, columns, null, null, null,
null, null);
String result = "";

int iRow = c.getColumnIndex(KEY_ROWID);
int iResult = c.getColumnIndex(KEY_RESULT);
int iTablet = c.getColumnIndex(KEY_TABLET);
int iDate = c.getColumnIndex(KEY_DATE);

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
result = result + c.getString(iRow) + " " + c.getString(iResult)
+ " " + c.getString(iTablet) + " " + c.getString(iDate) + "\n";
}
return result;
}

ourHelper.close();


LOGCAT OUTPUT;

01-31 19:33:01.670: E/AndroidRuntime(6420): FATAL EXCEPTION: main

01-31 19:33:01.670: E/AndroidRuntime(6420):
java.lang.RuntimeException: Unable to start activity ComponentInfo{DBView}:
android.database.sqlite.SQLiteException: no such column: date: , while compiling:
SELECT _id, game_result, tablet_winner, date FROM prizeTable


MORE CODE FOR DETAILS;

public class PlayGame {

public static final String KEY_ROWID="_id";

// for table 1 gameTable
public static final String KEY_NAME="persons_name";
public static final String KEY_HITS="persons_hits";

// for table 2 prizesTable
public static final String KEY_RESULT="game_result";
public static final String KEY_TABLET="tablet_winner";
public static final String KEY_DATE="date";


private static final String DATABASE_NAME="PlayGamesdb";
private static final String DATABASE_TABLE_1="gameTable";
private static final String DATABASE_TABLE_2="prizeTable";
private static final int DATABASE_VERSION = 1;

private static final String CREATE_TABLE_1 = "CREATE TABLE " + DATABASE_TABLE_1 + " (" + KEY_ROWID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME + " TEXT NOT NULL, " + KEY_HITS + " TEXT NOT NULL);";

private static final String CREATE_TABLE_2 = "CREATE TABLE " + DATABASE_TABLE_2 + " (" + KEY_ROWID
+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_RESULT + " TEXT NOT NULL, " + KEY_TABLET
+ " TEXT NOT NULL, " + KEY_DATE + "TEXT NOT NULL);";

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

private static class DbHelper extends SQLiteOpenHelper{

public DbHelper(Context context){
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

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

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_1 + "AND" + DATABASE_TABLE_2);
onCreate(db);
}
}


<<< EDIT >>>

Safime's suggestion fixed the crashing, that was adding a space between KEY_DATE and TEXT in the creation of the second table.

However still a problem, no more crashing, but the
insert()
method is still not working. Getting a -1 return shows that it is not inserting anything, and the the table 2 is still empty after inserting a new row to the table. Got to find out why it is failing to create any new rows in the table. Just like earlier, table one works fine but table two is still not working yet.

Answer

You are using Constraint NOT NULL and you are inserting only in one column. You must be getting SQLiteConstraintexception Exception. Try inserting in all columns.