App Crash while using External Sqlite with SimpleCursor Adapter

Question

After Learning So Many Tutorials now i am creating an application

the Application contain an external sqlite database..now i am trying to display the first column in a listview when i am trying to display my app was crash..when i check in logcat it simply say's column '_id' does not exist but in my database i have no column like column_id plz help here is my code below

My Create Statement in SqliteManager

CREATE TABLE "Ayervedic" ("Item No" NUMERIC NOT NULL , "Title" VARCHAR NOT NULL , "Subcategory" VARCHAR NOT NULL , "Details" VARCHAR NOT NULL , "Images" VARCHAR NOT NULL , PRIMARY KEY ("Item No", "Title", "Subcategory", "Details", "Images"))

Database Class

public class SqlLiteDbHelper extends SQLiteOpenHelper {

// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "Ayervedic.sqlite";
private static final String DB_PATH_SUFFIX = "/databases/";
static Context ctx;

public SqlLiteDbHelper(Context context) {

    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    ctx = context;
}
public void CopyDataBaseFromAsset() throws IOException {

    InputStream myInput = ctx.getAssets().open(DATABASE_NAME);

    // Path to the just created empty db
    String outFileName = getDatabasePath();

    // if the path doesn't exist first, create it
    File f = new File(ctx.getApplicationInfo().dataDir + DB_PATH_SUFFIX);
    if (!f.exists())
        f.mkdir();

    // Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);


    // transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);

    }
    // Close the streams

    myOutput.flush();
    myOutput.close();
    myInput.close();

}

private static String getDatabasePath() {

    return ctx.getApplicationInfo().dataDir + DB_PATH_SUFFIX + DATABASE_NAME;

}

public SQLiteDatabase openDataBase() throws SQLException {

    File dbFile = ctx.getDatabasePath(DATABASE_NAME);
    if (!dbFile.exists()) {
        try {
            CopyDataBaseFromAsset();

            System.out.println("Copying sucess from Assets folder");

        } catch (IOException e) {

            throw new RuntimeException("Error creating source database", e);

        }

    }
    return SQLiteDatabase.openDatabase(dbFile.getPath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS | SQLiteDatabase.CREATE_IF_NECESSARY);

}

@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}
public Cursor gettitles(SQLiteDatabase db)
{
    db = this.getReadableDatabase();

    Cursor cursor;

    cursor = db.query(true, "Ayervedic", new String[]{"Title"}, null, null, null, null, null, null);
    return cursor;
}

Main Activity

public class MainActivity extends AppCompatActivity {

ListView listView;
String title;
SqlLiteDbHelper dbHelper;

SQLiteDatabase sqLiteDatabase;
Cursor cursor;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
   listView= (ListView) findViewById(R.id.listView);
    dbHelper = new SqlLiteDbHelper(this);
    try {
        dbHelper.openDataBase();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    sqLiteDatabase=dbHelper.getReadableDatabase();
    cursor=dbHelper.gettitles(sqLiteDatabase);
    String[] from = new String[] { "Title" };
    int[] to = new int[] {R.id.textView };
    SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,R.layout.row_title,cursor,from,to);
    adapter.notifyDataSetChanged();
    listView.setAdapter(adapter);
}

Logcat:

Process: com.example.ky.tamil, PID: 6286
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.ky.tamil/com.example.aeiltech.tamil.MainActivity}: java.lang.IllegalArgumentException: column '_id' does not exist
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2436)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2498)
        at android.app.ActivityThread.access$900(ActivityThread.java:179)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1324)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:146)
        at android.app.ActivityThread.main(ActivityThread.java:5641)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:515)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1288)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1104)
        at dalvik.system.NativeStart.main(Native Method)
 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist

Have you checked this? http://stackoverflow.com/questions/3192064/about-id-field-in-android-sqlite — mach, Oct 15 '15 at 11:10
Well your Table name and Database names are also same. That's not good practice. After changing it you can apply the given solutions. — MDroid, Oct 15 '15 at 12:03

bwt · Answer 1 · 2015-10-15T11:24:22.097

1

SimpleCursorAdapter is a subclass of CursorAdapter. The documentation states :

The Cursor must include a column named "_id" or this class will not work.

In your case you could just rename the primary key Item No as _id

Edit : You also need to select this column in the cursor, even it is not mapped to a view.

cursor = db.query(true, "Ayervedic", new String[]{"Title", "_id"}, null, null, null, null, null, null);

edited Oct 15 '15 at 11:24

answered Oct 15 '15 at 11:11

bwt

17,292
1
42
60

the doubt is i am not declare ITEM No in my database class @bwt – Karthick Oct 15 '15 at 11:14
did i want to change in Database class or SqliteManager??@bwt – Karthick Oct 15 '15 at 11:16
`no such column: _id (code 1): , while compiling: SELECT DISTINCT Title, _id FROM Ayervedic` now i got an error like this – Karthick Oct 15 '15 at 11:29
@karthickYadav use a projection columns like: `new String[]{"Title", "rowid as _id"}` – pskink Oct 15 '15 at 11:33
Both, the primary key should be named `_id` in the create statement and referenced in the cursor creation. You could use an alias like suggested by @pskink but I think that column name containing space like `"Item No"` are rarely a good idea – bwt Oct 15 '15 at 11:41
`no such column: _id (code 1): , while compiling: SELECT DISTINCT Title, _id FROM Ayervedic` i got this error when i try projection `new String[] { "Title", "Item No as _id" };` – Karthick Oct 15 '15 at 11:48
no such column: _id (code 1): , while compiling: SELECT DISTINCT Title, _id FROM Ayervedic – Karthick Oct 15 '15 at 12:49
@karthickYadav what projection did you use? – pskink Oct 15 '15 at 12:51
in main activity above cursoradpter i use this projection `String[] from = new String[] { "Title", "rowid as _id" };` and i got a error like this no such column: _id (code 1): , while compiling: SELECT DISTINCT Title, _id FROM Ayervedic – Karthick Oct 15 '15 at 12:54
` String[] columns = new String[]{"Title", "rowid as _id"}; cursor = db.query(true, "Ayervedic", columns , null, null, null, null, null, null);` i try this in database class @pskink – Karthick Oct 15 '15 at 13:01
ok try to call `DatabaseUtils.dumpCursor(c)` where c is a `Cursor` returned by `db.query` call – pskink Oct 15 '15 at 13:14
in mainActivity or in Database Class? @pskink – Karthick Oct 15 '15 at 13:17
and what columns do you see? – pskink Oct 15 '15 at 13:18
**Title** want to display – Karthick Oct 15 '15 at 13:20

mach · Answer 2 · 2015-10-15T11:48:36.163

1

Your SimpleCursor adapter needs the row _id for usage.

CREATE TABLE "Ayervedic" (
   "_id" INT AUTOINCREMENT,
   "Item No" NUMERIC NOT NULL,
   "Title" VARCHAR NOT NULL,
   "Subcategory" VARCHAR NOT NULL,
   "Details" VARCHAR NOT NULL ,
   "Images" VARCHAR NOT NULL ,
   PRIMARY KEY ("_id")
);

edited Oct 15 '15 at 11:48

answered Oct 15 '15 at 11:11

mach

8,315
3
33
51

thanks But i am using External Database how can we write CREATE_TABLE in Oncreate??is it possible? @mach – Karthick Oct 15 '15 at 13:31

score 1 · Answer 3 · answered Oct 15 '15 at 11:52

1

Try This

Cursor c = db.rawQuery(" SELECT "+ Title + " AS _id from Ayervedic");

means that you Select Title and create an alias of it using AS into _id, and you are selecting this id from Ayervedic table. so now you will be able to access the result from this query from the column name _id, and in order to access the result use:

c.moveToFirst();    
    while (c.moveToNext())
    {
     System.out.println(c.getString(c.getColumnIndex("_id")); 
    }

answered Oct 15 '15 at 11:52

MDroid

550
4
22

thx but in that field the column names have duplication how can we avoid that – Karthick Oct 15 '15 at 12:49
means ? I don't get you. which column names have duplication? – MDroid Oct 15 '15 at 12:58
that means in my Title Colum i have a fields in a repated time (ie) Apple,orange,grapes,Apple,orange,grapes how to avoid this duplication i need only one Apple,orange,grapes – Karthick Oct 15 '15 at 13:00
use "distinct". Select distinct Title as _id from Ayervedic – MDroid Oct 15 '15 at 13:02
" SELECT Title AS _id from Ayervedic" this line also shows error – Karthick Oct 15 '15 at 13:02
Have you change the Table name and your database name? if you cant solve the error then you can create Table as @mach said in his solution – MDroid Oct 15 '15 at 13:05
are you using sqLiteDatabase.rawQuery() ? – MDroid Oct 15 '15 at 13:07
`public Cursor gettitles(SQLiteDatabase db) { db = this.getReadableDatabase(); Cursor cursor; cursor=db.rawQuery(" Select distinct Title as _id from Ayervedic "); return cursor;` – Karthick Oct 15 '15 at 13:07
look, Now these errors you have to solve. these are syntactical errors. like if "SELECT Title AS _id from Ayervedic" showing error – MDroid Oct 15 '15 at 13:10
How ??what i need to write there – Karthick Oct 15 '15 at 13:13

App Crash while using External Sqlite with SimpleCursor Adapter

3 Answers3