can't insert values to a SQLite table

Question

I'm having hard time figuring out why im getting an error when I inserting values to the table. thanks for the help.

Basically,the code worked when it only with two column and one contentValues.put function but,when I added another column i keep getting error trying to inset values and i don't know why.

I have checked the SQlite query etc..

package com.avrahamzilberblat.battleshipfinal;

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 {

    private static final String TAG = "DatabaseHelper";
    private static final String TABLE_NAME = "people_table";
    private static final String COL1 = "ID";
    private static final String COL2 = "name";
    private static final String COL3 = "scoreRatio";

    public DatabaseHelper(Context context) {

        super(context, TABLE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +COL2 +" TEXT,"+ COL3 +"INTEGER"+");";
        db.execSQL("CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COL2 +" TEXT,"+COL3+"TEXT)");



    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        db.execSQL("DROP IF TABLE EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    public boolean addData(String item,int scoreRatio) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(COL2, item);
        contentValues.put(COL3, 5);


        long result = db.insert(TABLE_NAME, null, contentValues);

        //if date as inserted incorrectly it will return -1
        if (result == -1) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * Returns all the data from database
     * @return
     */
    public Cursor getData(){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_NAME;
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    /**
     * Returns only the ID that matches the name passed in
     * @param name
     * @return
     */
    public Cursor getItemID(String name,Double scoreRatio){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "SELECT " + COL1 + " FROM " + TABLE_NAME +
                " WHERE " + COL2 + " = '" + name + "'" + " AND " +COL3 + " = '" + scoreRatio+"'";
        Cursor data = db.rawQuery(query, null);
        return data;
    }

    /**
     * Updates the name field
     * @param newName
     * @param id
     * @param oldName
     */
    public void updateName(String newName, int id, String oldName){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "UPDATE " + TABLE_NAME + " SET " + COL2 +
                " = '" + newName + "' WHERE " + COL1 + " = '" + id + "'" +
                " AND " + COL2 + " = '" + oldName + "'";
        Log.d(TAG, "updateName: query: " + query);
        Log.d(TAG, "updateName: Setting name to " + newName);
        db.execSQL(query);
    }

    /**
     * Delete from database
     * @param id
     * @param name
     */
    public void deleteName(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();
        String query = "DELETE FROM " + TABLE_NAME + " WHERE "
                + COL1 + " = '" + id + "'" +
                " AND " + COL2 + " = '" + name + "'";
        Log.d(TAG, "deleteName: query: " + query);
        Log.d(TAG, "deleteName: Deleting " + name + " from database.");
        db.execSQL(query);
    }
    public void clearDatabase(String TABLE_NAME) {
        SQLiteDatabase db = this.getWritableDatabase();
        String clearDBQuery = "DELETE FROM "+TABLE_NAME;
        db.execSQL(clearDBQuery);
    }

}

--------------------This is the update i want a string and a double data type,still doesn't work

@Override
public void onCreate(SQLiteDatabase db) {
    //String createTable = "CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +COL2 +" TEXT,"+ COL3 +"INTEGER"+");";
    db.execSQL("CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL2 +" TEXT,"+COL3 +"REAL);");



}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("DROP IF TABLE EXISTS " + TABLE_NAME);
    onCreate(db);
}

public boolean addData(String item,int scoreRatio) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(COL2, "ood");
    contentValues.put(COL3, 6.6);

Answer 1

You should change :-

db.execSQL("CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COL2 +" TEXT,"+COL3+"TEXT)");

to :-

db.execSQL("CREATE TABLE " + TABLE_NAME + " (ID INTEGER PRIMARY KEY, " +
            COL2 +" TEXT,"+COL3+" TEXT)");

Then you should do one of the following :-

1. Delete(Clear) the App's data from settings/apps.

2. uninstall the App.

3. Increase the database version e.g. change super(context, TABLE_NAME, null, 1); to super(context, TABLE_NAME, null, 2);

   and then rerun the App.

The above is important as onCreate only ever runs automatically once, when the database is created, which is perhaps the root cause of you issue. You've added a column but it actually hasn't been added.

I have played with it a lot i actually want it to be a real number such as Double so in the beginning i wrote REAL instead of TEXT

SQlite allows any type of data to be stored in any type of column with one exception and that is an alias of the rowid (column defined with INTEGER PRIMARY KEY (with or without AUTOINCREMENT)). An alias of rowid can only store a integer (signed 64 bit) and it must be UNIQUE within the table.

• Note AUTOINCREMENT has been omitted. You very likely don't need it and it's got overheads

SQLite Autoincrement

Which includes :-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.