Error who using SQL database

Question

I am working on a simple note taking app using SQL database. I have coded the app half way through and when I want to test it by running the app it crashes saying unfortunately app has stopped. Don't know what the problem is? Need help

main activity.java

package com.mycompany.myapp;

import android.app.*;
import android.os.*;
import android.widget.*;
import android.database.*;
Import android.content.*;
Import android.net.*;
import android.util.*;

public class MainActivity extends Activity 
{
@Override
protected void onCreate(Bundle savedInstanceState)
{
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    insertNote("New Note");

    Cursor cursor = getContentResolver().query(NotesProvider.CONTENT_URI,DBOpenHelper.ALL_COLUMNS, null, null, null, null);

    String[] from ={DBOpenHelper.NOTE_TEXT};
    int[] to = {android.R.id.text1};
    CursorAdapter cursorAdapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, cursor, from, to,0);

    ListView noteslist = (ListView) findViewById(android.R.id.list);
    noteslist.setAdapter(cursorAdapter);
}


private void insertNote(String noteText)
{
    ContentValues values = new ContentValues();
    values.put(DBOpenHelper.NOTE_TEXT, noteText);
    Uri noteUri = getContentResolver().insert(NotesProvider.CONTENT_URI, values);

    Log.d("NOTESACTIVITY", "INSERTED NOTE" + noteUri.getLastPathSegment());
}

}

dbopenhelper.java

package com.mycompany.myapp;


import android.database.sqlite.*;
import android.content.*;

public class DBOpenHelper extends SQLiteOpenHelper
{

//Constants for db name and version
private static final String DATABASE_NAME = "notes.db";
private static final int DATABASE_VERSION = 1;

//Constants for identifying table and columns

public static final String TABLE_NOTES = "notes";
public static final String NOTE_ID = "_id";
public static final String NOTE_TEXT = "noteText";
public static final String NOTE_CREATED = "noteCreated";

public static final String[] ALL_COLUMNS ={NOTE_ID, NOTE_TEXT, NOTE_CREATED};

//SQL to create table

private static final String TABLE_CREATE =
"CREATE TABLE " + TABLE_NOTES + " (" +
NOTE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
NOTE_TEXT + " TEXT, " +
NOTE_CREATED + " TEXT default CURRENT_TIMESTAMP" +
")";
public DBOpenHelper(Context context){
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

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

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

}

notesprovider?.java

package com.mycompany.myapp;


import android.content.*;
import android.net.*;
import android.database.*;
import android.database.sqlite.*;

public class NotesProvider extends ContentProvider
{

private static final String AUTHORITY = "com.mycompany.myapp.notesprovider";
private static final String BASE_PATH = "notes";
public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + BASE_PATH );

// Constant to identify the requested operation
private static final int NOTES = 1;
private static final int NOTES_ID = 2;

private static final UriMatcher uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);


static{
    uriMatcher.addURI(AUTHORITY, BASE_PATH, NOTES);
    uriMatcher.addURI(AUTHORITY,BASE_PATH + "/#", NOTES_ID);

}
private SQLiteDatabase database;

@Override
public boolean onCreate()
{
    DBOpenHelper helper = new DBOpenHelper(getContext());
    database = helper.getWritableDatabase();
    return true;
}

@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder)
{
    // TODO: Implement this method
    return database.query(DBOpenHelper.TABLE_NOTES, DBOpenHelper.ALL_COLUMNS, selection, null, null, null, DBOpenHelper.NOTE_CREATED + "DESC");
}

@Override
public String getType(Uri uri)
{
    // TODO: Implement this method
    return null;
}

@Override
public Uri insert(Uri uri, ContentValues values)
{
    // TODO: Implement this method
    long id = database.insert(DBOpenHelper.TABLE_NOTES, null, values);
    return Uri.parse(BASE_PATH + "/" + id);
}

@Override
public int delete(Uri uri, String selection, String[] selectionArgs)
{
    // TODO: Implement this method
    return database.delete(DBOpenHelper.TABLE_NOTES, selection, selectionArgs);
}

@Override
public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs)
{
    // TODO: Implement this method
    return database.update(DBOpenHelper.TABLE_NOTES, values, selection, selectionArgs);
}

}

main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:background="#ffffff">

<ListView
    android:id="@+id/android:list"
    android:layout_height="match_parent"
    android:layout_width="match_parent"/>

android manifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.mycompany.myapp" >

<application
    android:allowBackup="true"
    android:icon="@drawable/ic_launcher"
    android:label="@string/app_name"
    android:theme="@style/AppTheme" >
    <activity
        android:name=".MainActivity"
        android:label="@string/app_name" >
        <intent-filter>
            <action android:name="android.intent.action.MAIN" />

            <category android:name="android.intent.category.LAUNCHER" />
        </intent-filter>
    </activity>
    <provider
        android:authorities="com.mycompany.myapp.notesprovider"
        android:name=".NotesProvider"
        android:exported="false"/>
</application>

</manifest>

logcat

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at com.android.internal.os ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime Caused by: android.database.sqlite.SQLiteException: no such column: noteCreatedDESC (code 1): , while compiling: SELECT _id, noteText, noteCreated FROM notes ORDER BY noteCreatedDESC

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1163)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1034)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1202)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at com.enlightenme.pac.NotesProvider.query(NotesProvider.java:40)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.content.ContentProvider.query(ContentProvider.java:966)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.content.ContentProvider$Transport.query(ContentProvider.java:211)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.content.ContentResolver.query(ContentResolver.java:478)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at com.enlightenme.pac.NotesActivity.onCreate(NotesActivity.java:24)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.app.Activity.performCreate(Activity.java:5990)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)

11-09 11:07:42.904 8351 8351 E     AndroidRuntime  ... 10 more

11-09 11:28:19.561 14991 14991 E   AndroidRuntime FATAL EXCEPTION: main
11-09 11:28:19.561 14991 14991 E   AndroidRuntime Process: com.enlightenme.pac, PID: 14991

11-09 11:28:19.561 14991 14991 E   AndroidRuntime java.lang.RuntimeException: Unable to start activity ComponentInfo{com.enlightenme.pac/com.enlightenme.pac.NotesActivity}: android.database.sqlite.SQLiteException: no such column: noteCreatedDESC (code 1): , while compiling: SELECT _id, noteText, noteCreated FROM notes ORDER BY noteCreatedDESC

11-09 11:28:19.561 14991 14991 E   AndroidRuntime at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2325)

Answer 1

Use noteCreated DESC instead of noteCreatedDESC

Try this:

        @Override 
      public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) 
        {
         // TODO: Implement this method return 
         database.query(DBOpenHelper.TABLE_NOTES, DBOpenHelper.ALL_COLUMNS, selection, null, null, null, DBOpenHelper.NOTE_CREATED + " DESC");
         }