Android SQLite Create table statement

Question

I have a problem where this table is not being created at all in android.
Here is the whole MyDBHandler:

package com.example.plrardiakao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDBHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "MudeDB.db";
    public static final String TABLE_ITEMS = "Items";

    //helper for types
    public static final String VARCHAR_TYPE = " VARCHAR(50)";
    public static final String BOOL_TYPE = " BOOLEAN";
    public static final String INT_TYPE = " INTEGER";

    //helper for column names
    public static final String COLUMN_ENTRY_ID = "id"; //integer
    public static final String COLUMN_TITLE = "itemtitle"; //varchar(50)
    public static final String COLUMN_AUTHOR = "author"; //varchar(50)
    public static final String COLUMN_CATEGORY = "category"; //varchar(50)
    public static final String COLUMN_DATE = "date"; //int
    public static final String COLUMN_TYPE = "type"; //varchar(50)
    public static final String COLUMN_COUNTRY = "country"; //varchar(50)
    public static final String COLUMN_COLOUR = "colour"; //varchar(50)
    public static final String COLUMN_MATERIAL = "material"; //varchar(50)
    public static final String COLUMN_FAVOURITE = "is_favourite"; //boolean
    public static final String COLUMN_IMGRES = "imgres"; //varchar(50)
    public static final String COLUMN_NUMBER_OF_PICS = "nr_of_pics"; // integer

    public MyDBHandler(Context context, String name, 
            CursorFactory factory, int version) {
        super(context, DATABASE_NAME, factory, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_ITEMS_TABLE = "CREATE TABLE " + TABLE_ITEMS + 
                "("
                     + COLUMN_ENTRY_ID      + INT_TYPE +" PRIMARY KEY AUTOINCREMENT," 
                     + COLUMN_TITLE         + VARCHAR_TYPE  + ","
                     + COLUMN_AUTHOR        + VARCHAR_TYPE  + ","
                     + COLUMN_CATEGORY      + VARCHAR_TYPE  + ","
                     + COLUMN_DATE          + INT_TYPE      + ","
                     + COLUMN_TYPE          + VARCHAR_TYPE  + ","
                     + COLUMN_COUNTRY       + VARCHAR_TYPE  + ","
                     + COLUMN_COLOUR        + VARCHAR_TYPE  + ","
                     + COLUMN_MATERIAL      + VARCHAR_TYPE  + ","
                     + COLUMN_FAVOURITE     + BOOL_TYPE     + ","
                     + COLUMN_IMGRES        + VARCHAR_TYPE  + ","
                     + COLUMN_NUMBER_OF_PICS + INT_TYPE + 
                ")";
          db.execSQL(CREATE_ITEMS_TABLE);
    }
    //CREATE TABLE Items(id INTEGER PRIMARY KEY AUTOINCREMENT, itemtitle VARCHAR(50),author)
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEMS);
          onCreate(db);
    }

    public void addItem(Item item) {

        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, item.getItemTitle());
        values.put(COLUMN_AUTHOR, item.getAuthor());
        values.put(COLUMN_CATEGORY, item.getCategory());
        values.put(COLUMN_DATE, item.getDate());
        values.put(COLUMN_TYPE, item.getType());
        values.put(COLUMN_COUNTRY, item.getCountry());
        values.put(COLUMN_COLOUR, item.getColour());
        values.put(COLUMN_MATERIAL, item.getMaterial());
        values.put(COLUMN_FAVOURITE, item.getFavourite());
        values.put(COLUMN_IMGRES, item.getImgres());
        values.put(COLUMN_NUMBER_OF_PICS, item.getNumberOfPics());

        SQLiteDatabase db = this.getWritableDatabase();

        db.insert(TABLE_ITEMS, null, values);
        db.close();

    }

    public int findPictureNumber(String itemtitle) {
        String query = "SELECT nr_of_pics FROM " + TABLE_ITEMS + " WHERE " +COLUMN_TITLE + "=" + "'"+itemtitle+"'" ;
        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cursor = db.rawQuery(query, null);


        int PicsNumber=0;

        if (cursor.moveToFirst()) {
            cursor.moveToFirst();
            PicsNumber=Integer.parseInt(cursor.getString(0));
            cursor.close();
            return PicsNumber;
        } else {
            //wtf?
        }
            db.close();
            return 0;
    }

    public void changeFavourite(String itemtitle, boolean isFavourite)
    {

        SQLiteDatabase db = this.getWritableDatabase();
        String strSQL = "UPDATE "+TABLE_ITEMS+" SET "+COLUMN_FAVOURITE +"="+ isFavourite +" WHERE columnId = "+ "'"+itemtitle+"'";

        db.execSQL(strSQL);

    }

    public String AuthorName(String itemtitle) {
        String query = "SELECT " +COLUMN_AUTHOR+ " FROM " + TABLE_ITEMS + " WHERE " +COLUMN_TITLE + "=" + "'"+itemtitle+"'" ;

        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cursor = db.rawQuery(query, null);

        String authorname="error1";

        if (cursor.moveToFirst()) {
            cursor.moveToFirst();
            authorname=cursor.getString(0);
            cursor.close();
        } else {
            //wtf?
        }
            db.close();
        return authorname;
    }

}

There is an error, probably somewhere in Create_table, but I have no idea where. I know that the table is not created, previous placeholder table is there, and upon execution it shows error that some of the columns don;'t exist. What's wrong?

score 0 · Answer 1 · answered Dec 14 '14 at 19:46

0

Goto App manager of your android phone. Delete all the data related to your app. Then try to run your app agian. And also in your CREATE TABLE statement you need spaces between your column names and column types.

answered Dec 14 '14 at 19:46

Praveena

6,340
2
40
53

I do it on a Virtual Machine from Genymotion. How to do it there? I have spaces in helper strings already. – Sheenah Dec 14 '14 at 19:48
Have a look at this http://stackoverflow.com/questions/22835372/clean-genymotion-virtual-devices-local-cache – Praveena Dec 14 '14 at 19:51
Thanks, it was the DB Version. Thank you so much anyways for taking your time. – Sheenah Dec 14 '14 at 19:58

Eleftherios · Accepted Answer · 2014-12-14T20:00:45.287

0

Why you didn't post the error? Can you post it? Also if you did changes in the db table then you have to increase the db version

edited Dec 14 '14 at 20:00

answered Dec 14 '14 at 19:52

Eleftherios

186
1
13

DB Version! That was that! THANK YOU SO MUCH! – Sheenah Dec 14 '14 at 19:58

Android SQLite Create table statement

2 Answers2