CODEWITHSUNDEEP
javaandroidsqldatabasedate
Gibran Gul
Gibran Gul

Reputation: 121

Saving sql database entry created date

I want to save and display the created date for an sql entry. But my app keeps crashing with this code. Any help would be appreciated!

Here is the Save State Method for saving the note: 

private void saveState() {
    String title = mTitleText.getText().toString();
    String body = mBodyText.getText().toString();
    String color = mColor;
    String date=(DateUtils.formatDateTime(this, System.currentTimeMillis(), DateUtils.FORMAT_SHOW_DATE | DateUtils.FORMAT_NUMERIC_DATE ));
    //String date = FastDateFormat.getInstance("dd-MM-yyyy").format(System.currentTimeMillis( ));

    if(title != null && !title.isEmpty() || body != null && !body.isEmpty()){
        if (mRowId == null) {
            long id = mDbHelper.createNote(title, body, color, date);
            if (id > 0) {
                mRowId = id;
            }
        } else {
            mDbHelper.updateNote(mRowId, title, body, color, date);
        }
    }

And here is the Database adapter:

public class NotesDbAdapter {

public static final String KEY_TITLE = "title";
public static final String KEY_BODY = "body";
public static final String KEY_ROWID = "_id";
public static final String KEY_COLOR = "color";
public static final String KEY_DATE = "date";

private static final String TAG = "NotesDbAdapter";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;

/**
 * Database creation sql statement
 */
private static final String DATABASE_CREATE =
    "create table notes (_id integer primary key autoincrement, "
    + "title text not null, body text not null, color text not null, date text not null);";

private static final String DATABASE_NAME = "data";
private static final String DATABASE_TABLE = "notes";
private static final int DATABASE_VERSION = 4;

private final Context mCtx;

private static class DatabaseHelper extends SQLiteOpenHelper {

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

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        onCreate(db);
    }
}

public NotesDbAdapter(Context ctx) {
    this.mCtx = ctx;
}

public NotesDbAdapter open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

public void close() {
    mDbHelper.close();
}

public long createNote(String title, String body, String color, String date) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_BODY, body);
    initialValues.put(KEY_COLOR, color);
    initialValues.put(KEY_DATE, date);

    return mDb.insert(DATABASE_TABLE, null, initialValues);
}

public boolean deleteNote(long rowId) {

    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}


public Cursor fetchAllNotes() {

    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
            KEY_BODY, KEY_COLOR, KEY_DATE}, null, null, null, null, null);
}


public Cursor fetchNote(long rowId) throws SQLException {

    Cursor mCursor =

        mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                KEY_TITLE, KEY_BODY, KEY_COLOR, KEY_DATE}, KEY_ROWID + "=" + rowId, null,
                null, null, null, null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;

}


public boolean updateNote(long rowId, String title, String body, String color, String date) {
    ContentValues args = new ContentValues();
    args.put(KEY_TITLE, title);
    args.put(KEY_BODY, body);
    args.put(KEY_COLOR, color);
    args.put(KEY_DATE, date);

    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}

}

UPDATE Okay after running it, this was the error log i got:

08-03 16:15:52.816: I/Process(29938): Sending signal. PID: 29938 SIG: 9 08-03 16:15:53.046: E/SQLiteLog(30048): (1) no such column: date 08-03 16:15:53.046: D/AndroidRuntime(30048): Shutting down VM 08-03 16:15:53.046: W/dalvikvm(30048): threadid=1: thread exiting with uncaught exception (group=0x40d23a08) 08-03 16:15:53.056: E/AndroidRuntime(30048): FATAL EXCEPTION: main 08-03 16:15:53.056: E/AndroidRuntime(30048): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.android.demo.notepad2/com.android.demo.notepad2.Notepadv2}: android.database.sqlite.SQLiteException: no such column: date (code 1): , while compiling: SELECT _id, title, body, color, date FROM notes 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2312) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2362) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.ActivityThread.access$600(ActivityThread.java:156) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1250) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.os.Handler.dispatchMessage(Handler.java:99) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.os.Looper.loop(Looper.java:137) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.ActivityThread.main(ActivityThread.java:5234) 08-03 16:15:53.056: E/AndroidRuntime(30048): at java.lang.reflect.Method.invokeNative(Native Method) 08-03 16:15:53.056: E/AndroidRuntime(30048): at java.lang.reflect.Method.invoke(Method.java:525) 08-03 16:15:53.056: E/AndroidRuntime(30048): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:799) 08-03 16:15:53.056: E/AndroidRuntime(30048): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:566) 08-03 16:15:53.056: E/AndroidRuntime(30048): at dalvik.system.NativeStart.main(Native Method) 08-03 16:15:53.056: E/AndroidRuntime(30048): Caused by: android.database.sqlite.SQLiteException: no such column: date (code 1): , while compiling: SELECT _id, title, body, color, date FROM notes 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200) 08-03 16:15:53.056: E/AndroidRuntime(30048): at com.android.demo.notepad2.NotesDbAdapter.fetchAllNotes(NotesDbAdapter.java:134) 08-03 16:15:53.056: E/AndroidRuntime(30048): at com.android.demo.notepad2.Notepadv2.fillData(Notepadv2.java:64) 08-03 16:15:53.056: E/AndroidRuntime(30048): at com.android.demo.notepad2.Notepadv2.onCreate(Notepadv2.java:58) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.Activity.performCreate(Activity.java:5108) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080) 08-03 16:15:53.056: E/AndroidRuntime(30048): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2266) 08-03 16:15:53.056: E/AndroidRuntime(30048): ... 11 more

Upvotes: 0

Views: 399

Answers (2)

Luca Basso Ricci
Luca Basso Ricci

Reputation: 18383

Error is in your query: change date (and _id too) with another name; usually date is a reserved keyword.

Upvotes: 1

Hjorthenify
Hjorthenify

Reputation: 197

I would have put this in the comment, but my rep is not high enough. I am pretty sure that there is a way to make the SQL table automatically put the current date and time whenever a new entry is created. Perhaps you could try that? It's been a while since I have worked with SQL though so I might be wrong.

Upvotes: 0

Related Questions