SQLite not creating table. No such table is found and column name not forund

Question

I am trying to store data into sqlite db of android

DBHelper class

    public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHelper";

    private static final String  DATABASE_NAME   =   "DemoAppDB";
    private static final int DATABASE_VERSION    =   6;

    private static final String  TABLE_VISIT =   "visit";
    private static final String  TABLE_LIVE  =   "live";

    private static final String  COL_TIMESTAMP   =   "timestamp";
    private static final String  COL_LOCATION    =   "location";
    private static final String  COL_LATITUDE    =   "latitude";
    private static final String  COL_LONGITUDE   =   "longitude";

    private static final String  COL_VISIT_ID    =   "_id_visit";
    private static final String  COL_VISIT_DOC   =   "doctor";
    private static final String  COL_VISIT_REP   =   "representative";
    private static final String  COL_VISIT_TYPE  =   "vist_type";
    private static final String  COL_VISIT_ACCOMPLICE    =   "accomplice";
    private static final String  COL_VISIT_PRODUCT_NAME  =   "product";
    private static final String  COL_VISIT_GIVEAWAYS =   "giveaways";

    private static final String  COL_LIVE_ID     =   "_id_live";
    private static final String  COL_LIVE_REP    =   "representative";


    public DBHelper(Context context){
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.d(TAG, "onCreate: Creating dbs");
        createVisitTable(sqLiteDatabase);
        createLiveTable(sqLiteDatabase);
        Log.d(TAG, "onCreate: db created");
    }

    public void createVisitTable(SQLiteDatabase sqLiteDatabase){
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "   +   TABLE_LIVE);

//        this.getTables();
        sqLiteDatabase.execSQL(
                "create table " +   TABLE_VISIT +
                        " ( " +   COL_VISIT_ID    +   "   integer primary key autoincrement, " +
                        COL_TIMESTAMP   +   " text, " +
                        COL_VISIT_DOC   +   " text, " +
                        COL_VISIT_TYPE  +   " text, " +
                        COL_VISIT_ACCOMPLICE    +   " text, " +
                        COL_VISIT_PRODUCT_NAME  +   " text, " +
                        COL_VISIT_GIVEAWAYS +   " text, " +
                        COL_LATITUDE    +   " text, " +
                        COL_VISIT_REP   +   " text, " +
                        COL_LONGITUDE    +   " text )"
        );

    }

    public void createLiveTable(SQLiteDatabase sqLiteDatabase){
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "   +   TABLE_VISIT);
        sqLiteDatabase.execSQL(
                "create table " +   TABLE_LIVE +
                        " ( " +   COL_LIVE_ID    +   "   integer primary key autoincrement, " +
                        COL_TIMESTAMP   +   " text, " +
                        COL_LATITUDE    +   " text, " +
                        COL_LONGITUDE   +   " text, " +
                        COL_LIVE_REP    +   " text )"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        Log.d(TAG, "onUpgrade: old : ["+i+"] new : ["+i1+"]");
        if (i >= i1){
            return;
        }

        Log.d(TAG, "onUpgrade: Dropping [" + TABLE_LIVE + "]");
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "   +   TABLE_LIVE);
        Log.d(TAG, "onUpgrade: Dropping [" + TABLE_VISIT + "]");
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "   +   TABLE_VISIT);
        Log.d(TAG, "onUpgrade: CREATING [" + TABLE_VISIT + "]");
        createVisitTable(sqLiteDatabase);
        Log.d(TAG, "onUpgrade: CREATING [" + TABLE_LIVE + "]");
        createLiveTable(sqLiteDatabase);
        Log.d(TAG, "onUpgrade: SUCCESSFULLY RECREATED ALL");
    }

    public boolean  insertRowInVisit(VisitCall  visitCall){
        Log.d(TAG, "insertRowInVisit: inserting row in visit");
        SQLiteDatabase  db  =   this.getWritableDatabase();
        Log.d(TAG, "insertRowInVisit: Writable db fetched");

        ContentValues   contentValues   =   new ContentValues();
        contentValues.put(COL_TIMESTAMP, String.valueOf(visitCall.getTimestamp()));
        contentValues.put(COL_VISIT_DOC,String.valueOf(visitCall.getDoctorName()));
        contentValues.put(COL_VISIT_TYPE,visitCall.getVisitType());
        contentValues.put(COL_VISIT_ACCOMPLICE,visitCall.getAccompliceName());
        contentValues.put(COL_VISIT_PRODUCT_NAME,visitCall.getProductName());
        contentValues.put(COL_VISIT_GIVEAWAYS,visitCall.getGiveaways());
        contentValues.put(COL_VISIT_REP,visitCall.getRepresentativeName());
        contentValues.put(COL_LATITUDE,String.valueOf(visitCall.getLocation().getLatitude()));
        contentValues.put(COL_LONGITUDE,String.valueOf(visitCall.getLocation().getLongitude()));
        Log.d(TAG, "insertRowInVisit: visit call object :  ["+visitCall.toString()+"]");
        db.insert(TABLE_VISIT,null,contentValues);
        return true;
    }

I tried calling in on a button click

DBHelper dbHelper    =   new DBHelper(CallFormActivity.this);
dbHelper.insertRowInVisit(visitCall);

StackTrace :

07-31 20:16:49.047 25146-25146/com.example.omer.mnpandroidappdemo E/SQLiteLog: (1) no such table: visit
07-31 20:16:49.049 25146-25146/com.example.omer.mnpandroidappdemo E/SQLiteDatabase: Error inserting representative=QA giveaways=Guys longitude=67.0725465 accomplice= doctor=Dr. Abdullah latitude=24.8623643 timestamp=Mon Jul 31 20:16:33 GMT+05:00 2017 vist_type=Alone product=Augmentin
android.database.sqlite.SQLiteException: no such table: visit (code 1): , while compiling: INSERT INTO visit(representative,giveaways,longitude,accomplice,doctor,latitude,timestamp,vist_type,product) VALUES (?,?,?,?,?,?,?,?,?)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
at com.example.omer.mnpandroidappdemo.helper.DBHelper.insertRowInVisit(DBHelper.java:128)
at com.example.omer.mnpandroidappdemo.CallFormActivity$2.onClick(CallFormActivity.java:208)
at android.view.View.performClick(View.java:5207)
at android.view.View$PerformClick.run(View.java:21168)
at android.os.Handler.handleCallback(Handler.java:746)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:5491)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:728)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)

I have tried changing versions of DB also, tried changing format of sql and still error remains same initially it gave error for no such table found and I have tried every possible solution given on this forum

settings->go to app--> clear data and run ur application give better solution — Bhuvaneshwaran Vellingiri, Aug 01 '17 at 07:33
You might be using a pre database of a previous version i.e. you created a database and changed db without changing the version. — Abbas, Aug 01 '17 at 07:33
@IntelliJAmiya already tried that and error remains same. Do I need to add any dependancy in my gradle file or app manifest — Omer Haqqani, Aug 01 '17 at 07:37

Nitesh Tiwari · Accepted Answer · 2017-08-01T07:44:51.113

1

You are dropping 'TABLE_VISIT' while creating 'TABLE_LIVE'. Causing this issue. Try below code and remove both DROP commands from the createVisitTable() and createLiveTable()

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
  Log.d(TAG, "onCreate: Creating dbs");
  sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "   +   TABLE_LIVE);
  sqLiteDatabase.execSQL("DROP TABLE IF EXISTS "   +   TABLE_VISIT);

  createVisitTable(sqLiteDatabase);
  createLiveTable(sqLiteDatabase);

  Log.d(TAG, "onCreate: db created");
}

edited Aug 01 '17 at 07:44

answered Aug 01 '17 at 07:40

Nitesh Tiwari

4,742
3
28
46

1

@ModularSynth Yes.Updated my answer :-P – Nitesh Tiwari Aug 01 '17 at 07:48
@OmerHaqqani Awesome. – Nitesh Tiwari Aug 01 '17 at 07:48

SQLite not creating table. No such table is found and column name not forund

1 Answers1