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()

        }
    }
}

can you post your DatabaseHelper code ?

JDenais
– JDenais

2015-03-12 02:48:03 +00:00
Commented Mar 12, 2015 at 2:48 — JDenais
– JDenais, Commented Mar 12, 2015 at 2:48

JDenais · Accepted Answer · 2015-03-12 04:14:34Z

1

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.

edited Mar 12, 2015 at 4:14

answered Mar 12, 2015 at 4:08

JDenais

3,0162 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3314337 Over a year ago

Sorry mate. Uninstall/Reinstall, Version upgrade - I had tried all things already. No results. But could you have a look at logcat data? in the updated question?

JDenais Over a year ago

Did you see my edited answer with the error in your onUpgrade() method?

user3314337 Over a year ago

Sorry for getting back late. Made no changes as you mentioned. Changing column name to _id is producing results as expected. I replaced the following code public static final String CUSTOMER_ROWID = "customer_id"; with private static final String CUSTOMER_ROWID = "_id";

JDenais · Accepted Answer · 2015-03-16 21:50:18Z

0

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.

answered Mar 16, 2015 at 21:50

JDenais

3,0162 gold badges25 silver badges30 bronze badges

Collectives™ on Stack Overflow

Error in SQLite db creation and Inserting data

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related