Error in SQLite db creation and Inserting data

Question

When trying to create a database with a single table I encountered the following error. Not able to point the issue causing the error. Although have created the table column, still responding with no such column

Logcat data:

    03-16 12:08:35.954    1249-1249/com.example.bharathduraiswamy.comboedittext E/AndroidRuntime﹕ FATAL EXCEPTION: main
    java.lang.RuntimeException: Unable to start activity ComponentInfo

{com.example.bharathduraiswamy.comboedittext/com.example.bharathduraiswamy.comboedittext.AddSupplier}: android.database.sqlite.SQLiteException: no such 

column: _id (code 1): , while compiling: SELECT _id, supplier_name, supplier_contact_number, supplier_address FROM SUPPLIER
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2313)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2365)
            at android.app.ActivityThread.access$600(ActivityThread.java:156)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:153)
            at android.app.ActivityThread.main(ActivityThread.java:5336)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:833)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:600)
            at dalvik.system.NativeStart.main(Native Method)
     Caused by: android.database.sqlite.SQLiteException: no such column: _id (code 1): , while compiling: SELECT _id, supplier_name, supplier_contact_number, 

supplier_address FROM SUPPLIER
            at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
            at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:886)
            at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:497)
            at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
            at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
            at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
            at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
            at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
            at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1161)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1032)
            at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1200)
            at com.example.bharathduraiswamy.comboedittext.VivzDatabaseAdapter.getAllRows(VivzDatabaseAdapter.java:78)
            at com.example.bharathduraiswamy.comboedittext.AddSupplier.populateListView(AddSupplier.java:271)
            at com.example.bharathduraiswamy.comboedittext.AddSupplier.onCreate(AddSupplier.java:71)
            at android.app.Activity.performCreate(Activity.java:5122)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1081)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2277)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2365)
            at android.app.ActivityThread.access$600(ActivityThread.java:156)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1344)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:153)
            at android.app.ActivityThread.main(ActivityThread.java:5336)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:833)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:600)
            at dalvik.system.NativeStart.main(Native Method)

MainActivity.java data:

DBAdapter myDb;
AutoCompleteTextView customerName;
EditText customerNumber, customerAddress;

    customerName = (AutoCompleteTextView) findViewById(R.id.addCustomerName);
    customerNumber = (EditText) findViewById(R.id.addCustomerNumber);
    customerAddress = (EditText) findViewById(R.id.addCustomerAddress);
    openDB();

private void openDB() {
    myDb = new DBAdapter(this);
    myDb.open();
}

public void addCustomer(MenuItem item) {
    if (!TextUtils.isEmpty(customerName.getText().toString()) &&
            !TextUtils.isEmpty(customerNumber.getText().toString())) {
        myDb.insertCustomer(
                customerName.getText().toString(),
                customerNumber.getText().toString(),
                customerAddress.getText().toString());}
}

DbHelper data: (Edited)

package com.example.bharathduraiswamy.comboedittext;

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 DBAdapter {

    private static final String TAG = "DBAdapter"; //used for logging database version changes

/////////////////
//START : CUSTOMER DATA
/////////////////

    // Field Names:
    public static final String CUSTOMER_ROWID = "customer_id";
    public static final String CUSTOMER_NAME = "customer_name";
    public static final String CUSTOMER_CONTACT_NUMBER = "customer_contact_number";
    public static final String CUSTOMER_CONTACT_ADDRESS = "customer_contact_address";

    public static final String[] CUSTOMER_KEYS = new String[] {CUSTOMER_ROWID, CUSTOMER_NAME, CUSTOMER_CONTACT_NUMBER, CUSTOMER_CONTACT_ADDRESS};

    // Column Numbers for each Field Name:
    public static final int COL_CUSTOMER_ROWID = 0;
    public static final int COL_CUSTOMER_NAME = 1;
    public static final int COL_CUSTOMER_CONTACT_NUMBER = 2;
    public static final int COL_CUSTOMER_CONTACT_ADDRESS = 3;

    // DataBase info:
    public static final String DATABASE_NAME = "dbLeder";
    public static final String CUSTOMER_TABLE = "CUSTOMERLIST";
    public static final int DATABASE_VERSION = 3; // The version number must be incremented each time a change to DB structure occurs.

    //SQL statement to create database
    private static final String DATABASE_CREATE_SQL =
            "CREATE TABLE " + CUSTOMER_TABLE 
            + " (" 
            + CUSTOMER_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + CUSTOMER_NAME + " VARCHAR(255), "
            + CUSTOMER_CONTACT_NUMBER + " VARCHAR(255), "
            + CUSTOMER_CONTACT_ADDRESS + " VARCHAR(255));";

    public final Context context;
    public DatabaseHelper myDBHelper;
    public SQLiteDatabase db;

/////////////////
//END : CUSTOMER DATA
/////////////////

    public DBAdapter(Context ctx) {
        this.context = ctx;
        myDBHelper = new DatabaseHelper(context);
    }

    // Open the database connection.
    public DBAdapter open() {
        db = myDBHelper.getWritableDatabase();
        return this;
    }

    // Close the database connection.
    public void close() {
        myDBHelper.close();
    }

    //onClick Method for Check - addCustomer
    public long insertCustomer(String custName, String custContactNumber, String custContactAddress) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(CUSTOMER_NAME, custName);
        initialValues.put(CUSTOMER_CONTACT_NUMBER, custContactNumber);
        initialValues.put(CUSTOMER_CONTACT_ADDRESS, custContactAddress);

        // Insert the data into the database.
        return db.insert(CUSTOMER_TABLE, null, initialValues);
    }

    // Delete a row from the database, by rowId (primary key)
    public boolean deleteRow(long rowId) {
        String where = CUSTOMER_ROWID + "=" + rowId;
        return db.delete(CUSTOMER_TABLE, where, null) != 0;
    }

    public void deleteAll() {
        Cursor c = getAllRows();
        long rowId = c.getColumnIndexOrThrow(CUSTOMER_ROWID);
        if (c.moveToFirst()) {
            do {
                deleteRow(c.getLong((int) rowId));
            } while (c.moveToNext());
        }
        c.close();
    }

    // Return all data in the database.
    public Cursor getAllRows() {
        String where = null;
        Cursor c =  db.query(true, CUSTOMER_TABLE, CUSTOMER_KEYS, where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    // Get a specific row (by rowId)
    public Cursor getRow(long rowId) {
        String where = CUSTOMER_ROWID + "=" + rowId;
        Cursor c =  db.query(true, CUSTOMER_TABLE, CUSTOMER_KEYS,
                        where, null, null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;
    }

    // Change an existing row to be equal to new data.
    public boolean updateRow(long rowId, String custName, String custContactNumber, String custContactAddress) {
        String where = CUSTOMER_ROWID + "=" + rowId;
        ContentValues newValues = new ContentValues();
        newValues.put(CUSTOMER_NAME, custName);
        newValues.put(CUSTOMER_CONTACT_NUMBER, custContactNumber);
        newValues.put(CUSTOMER_CONTACT_ADDRESS, custContactAddress);
        // Insert it into the database.
        return db.update(CUSTOMER_TABLE, newValues, where, null) != 0;
    }


    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_SQL);
        }

        @Override
        public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading application's database from version " + oldVersion
                    + " to " + newVersion + ", which will destroy all old data!");

            // Destroy old database:
                _db.execSQL("DROP TABLE IF EXISTS" + CUSTOMER_TABLE);
                onCreate(_db); // Recreates the onCreate()

        }
    }
}

Answer 1

The new Logcat shows that you are having a different error. I think what it tells you is that you should have at least one column called "_id" in your table.

This is sort of a must have when working in SQLite on Android because some of the convenience methods look for this column name.

If you search StackOverflow, you will find some answers tell you that you can Alias this row and don't have to change the design of your table, but I'd say go ahead and change your design.

Answer 2

Nothing strikes me as wrong in your code.

Did you increment the DATABASE_VERSION field to make sure your onUpgradee() method is called ?

If you did that already, maybe try to uninstall the app from your device. That will erase the existing database. If this work, that would probably mean that your onUpgrade() method does not work.

Good luck.

EDIT : Check your onUpgrade() method. You don't have a space after "EXISTS". You need one otherwise the query won't work.