android.database.sqlite.SQLiteException: no such column: name (code 1)

Question

I was working on this project where data will be saved to SQLite if the app is offline. But i get this error when i build and run. I re checked with the SQL query too. But not able to get what is the exact problem.

MainActivity.java

public class MainActivity extends AppCompatActivity {


    RecyclerView recyclerView;
    EditText name;
    RecyclerView.LayoutManager layoutManager;
    RecyclerAdapter recyclerAdapter;
    ArrayList<Contact> arrayList=new ArrayList<>();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        recyclerView=findViewById(R.id.recyclerView);
        name=findViewById(R.id.name);
        layoutManager=new LinearLayoutManager(this);
        recyclerView.setLayoutManager(layoutManager);
        recyclerView.setHasFixedSize(true);
        recyclerAdapter=new RecyclerAdapter(arrayList);
        recyclerView.setAdapter(recyclerAdapter);
        readfromlite();
    }
private void readfromlite()
    {
        arrayList.clear();
        DbHelper dbHelper=new DbHelper(this);
        SQLiteDatabase database=dbHelper.getReadableDatabase();
        Cursor cursor=dbHelper.readlite(database);
         while (cursor.moveToNext())
         {

             String name=cursor.getString(cursor.getColumnIndex(DbContract.NAME));
             int sync_status= cursor.getInt(cursor.getColumnIndex(DbContract.sync_status));
             arrayList.add(new Contact(name,sync_status));
         }

        recyclerAdapter.notifyDataSetChanged();
         cursor.close();
         dbHelper.close();
}

DBHelper.java

public class DbHelper extends SQLiteOpenHelper {
    private static final int dbversion=1;
    private static final String ctable= " create TABLE if not EXISTS " + DbContract.tname + " (id integer primary key autoincrement, " + DbContract.NAME + " text , " + DbContract.sync_status + " integer ) ; " ;
    private static final String dtable= " drop table if EXISTS " + DbContract.tname ;

    public DbHelper(Context context){
    super(context,DbContract.dbname,null,dbversion);


    }
    @Override
    public void onCreate(SQLiteDatabase db) {
    db.execSQL(ctable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL(dtable);
    onCreate(db);

    }
    public void savelite(String name,int sync_status,SQLiteDatabase database){
        ContentValues contentValues=new ContentValues();
        contentValues.put(DbContract.NAME,name);
        contentValues.put(DbContract.sync_status,sync_status);
        database.insert(DbContract.tname,null,contentValues);
    }

    public Cursor readlite(SQLiteDatabase database){

        String[] resultset={DbContract.NAME,DbContract.sync_status};
    return (database.query(DbContract.tname,resultset,null,null,null,null,null));
    }

    public void updatelite(String name,int sync_status,SQLiteDatabase database){

        ContentValues contentValues=new ContentValues();
        contentValues.put(DbContract.sync_status,sync_status);
        String selection=DbContract.NAME+"LIKE?";
        String[] selection_args={name};
        database.update(DbContract.tname,contentValues,selection,selection_args);

    }
}

DbContract.java

public class DbContract {

    public static final int SYNC_STATUS_OK=0;
    public static final int SYNC_STATUS_FAILED=1;
    public static final String dbname="contactdb";
    public static final String tname="contactinfo";
    public static final String NAME="name";
    public static final String sync_status="syncstatus";

}

Logcat

2019-03-10 19:22:31.438 2946-2946/com.app.datamanager E/AndroidRuntime: FATAL EXCEPTION: main
    Process: com.app.datamanager, PID: 2946
    java.lang.RuntimeException: Unable to start activity ComponentInfo{com.app.datamanager/com.app.datamanager.MainActivity}: android.database.sqlite.SQLiteException: no such column: name (code 1): , while compiling: SELECT name, syncstatus FROM contactinfo
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2947)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3012)
        at android.app.ActivityThread.-wrap12(ActivityThread.java)
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1716)
        at android.os.Handler.dispatchMessage(Handler.java:110)
        at android.os.Looper.loop(Looper.java:232)
        at android.app.ActivityThread.main(ActivityThread.java:6802)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1103)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:964)
     Caused by: android.database.sqlite.SQLiteException: no such column: name (code 1): , while compiling: SELECT name, syncstatus FROM contactinfo
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:965)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:576)
        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:1353)
        at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1200)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1071)
        at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1239)
        at com.app.datamanager.DbHelper.readlite(DbHelper.java:41)
        at com.app.datamanager.MainActivity.readfromlite(MainActivity.java:52)
        at com.app.datamanager.MainActivity.onCreate(MainActivity.java:36)
        at android.app.Activity.performCreate(Activity.java:6974)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1118)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2900)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3012) 
        at android.app.ActivityThread.-wrap12(ActivityThread.java) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1716) 
        at android.os.Handler.dispatchMessage(Handler.java:110) 
        at android.os.Looper.loop(Looper.java:232) 
        at android.app.ActivityThread.main(ActivityThread.java:6802)

Answer 1

I was having the same problem, but it was with the deletion of a column from the table, so when I changed it

db.delete(cons.tableNames[3], Cart.KEY_f+"=$valueToChange", null)

for this

db.delete(cons.tableNames[3], Cart.KEY_f + "  LIKE  '%" + valueToChange + "%' ", null)

And I do the update like this

fun updateCart(id: Int, mBusiness: Business) {
    val db = dbHelper.writableDatabase
    // New value for one column
    val valueToChange = mBusiness.e
    val values = ContentValues().apply {
        put(Business.KEY_e, valueToChange)
    }

    db.update(cons.tableNames[p.mReturnIntSP(meuContexto, cons.tablePosition)], values, "id=$id", null)
    db.close() // Closing database connection
}

Answer 2

Changed the database version from 1 to 201.

It's working now. I actually don't know what version number has to do with the solution. Hope someone gives an explanation. Cheers.

Answer 3

This line produces syntactically wrong sql statement:

String selection=DbContract.NAME+"LIKE?";

If you pass a value like 'Bob' as a parameter, the result would be:
nameLIKEBob (instead of name LIKE 'Bob'), and nameLIKEBob will be considered as a column name.

You need to insert spaces like this:

String selection=DbContract.NAME+" LIKE ?";