Problem with Android SQLite tutorials 49-54 -- cannot find "productname" column in table. :-(

0 Carl Mueller · January 22, 2015
I'm having trouble implementing the SQLite example as shown in tutorials 49-54.

No matter what I do, I can't insert a row in the table. I get an error message that the column "productname" does not exist. You would think it has something to do with the creation process, but my code is basically identical to Bucky's example code.

Can someone tell me what I'm doing wrong?

package com.carlmuellerjr.sqlexample;
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 MyDBHandler extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 5;
    private static final String DATABASE_NAME = "products.db";
    private static final String TABLE_PRODUCTS = "products";
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_PRODUCTNAME = "productname";
    public MyDBHandler(Context context, SQLiteDatabase.CursorFactory factory) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        String query =  "CREATE TABLE " + TABLE_PRODUCTS + "(" +
                COLUMN_ID + " INTEGER KEY AUTO INCREMENT " +
                COLUMN_PRODUCTNAME + " TEXT);";
        Log.i("", "query = " + query);
        db.execSQL(query);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
        onCreate(db);
    }
    public void addProduct(Product product) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        Log.i("", "productname  = " + product.get_productname());
        values.put(COLUMN_PRODUCTNAME, product.get_productname());
        db.insert(TABLE_PRODUCTS, null, values);
        db.close();
    }
    public void deleteProduct(String productname) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("DELETE FROM " + TABLE_PRODUCTS + " WHERE " +
            COLUMN_PRODUCTNAME + "=\"" + productname + "\";");
    }
    public String databaseToString() {
        Log.i("", "getWritableDatabase");
        SQLiteDatabase db = getWritableDatabase();
        Log.i( "", "rawQuery");
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_PRODUCTS + " WHERE 1", null);
        Log.i( "", "moveToFirst");
        cursor.moveToFirst();
        Log.i("", "getCount = " + cursor.getCount());
        Log.i("", "getColumnCount = " + cursor.getColumnCount());
        String results = "";
        while (!cursor.isAfterLast()) {
            String column = cursor.getString(cursor.getColumnIndex(COLUMN_PRODUCTNAME));
            Log.i("", "getColumnIndex = " + column);
            results += column == null ? "" : column + "\n";
            cursor.moveToNext();
        }
        db.close();
        return results;
    }
}

Post a Reply

Replies

Oldest  Newest  Rating
0 Troy Jones · January 22, 2015
db.execSQL("DELETE FROM " + TABLE_PRODUCTS + " WHERE " + COLUMN_PRODUCTNAME + "=\"" + productname + "\";");
0 Carl Mueller · January 22, 2015
Thanks for the formatting. How did you do it? Yes, I'm new around here. ;-)
0 Carl Mueller · January 22, 2015
I found a workaround that will create the table correctly. First of all, apparently "AUTOINCREMENT" is not accepted by SQLite; you must use "AUTO INCREMENT" with the space in between.

Secondly, the "productname" column will only be successfully created if you put it BEFORE the "_id" column. I suspect there's something about the whole "INTEGER KEY AUTO INCREMENT" suffix that causes it to eat the rest of the string, or at least the next column.

So, I don't know why yet, but try changing your query to:   

private static final String PRODUCTS_TABLE_CREATE = "CREATE TABLE " +
            PRODUCTS_TABLE_NAME + "(" +
            PRODUCTS_COLUMN_NAME + " TEXT " +
            PRODUCTS_COLUMN_ID + " INTEGER KEY AUTO INCREMENT " +
            ");";
0 Branislav Lazic · January 22, 2015
Multiple issues!
1) You forgot to put "," after TEXT,
2) Wrong auto increment syntax,
3) Primary key not declared properly.

Working solution:


CREATE TABLE 
PRODUCTS_TABLE_NAME (
PRODUCTS_COLUMN_NAME TEXT, 
PRODUCTS_COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT);

Here is a SQLFiddle:

http://sqlfiddle.com/#!5/5bd5f/1
0 Carl Mueller · January 22, 2015
Thanks a lot for the info!
  • 1

Java / Android Development

107,242 followers
About

Very popular language used to create desktop applications, website applets, and Android apps.

Links
Moderators
Bucky Roberts Administrator