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)

forpas · Accepted Answer · 2019-03-10 14:20:55Z

1

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 ?";

edited Mar 10, 2019 at 14:20

answered Mar 10, 2019 at 14:15

forpas

165k10 gold badges51 silver badges85 bronze badges

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

5 Comments

Abhishek P Over a year ago

Thanks for that, although this didn't solve the problem. The app is still crashing. But i changed the database version from 1 to 201 as i read it in some solution and it's working like charm now. But i don't understand how? Can you just brief me on what's this database version has to do with solution?

forpas Over a year ago

Nothing. You just recreated the database. Maybe you had in between changed the names of the columns in code and this change was never made actually in the db. All you had to do is uninstall the app and rerun.

MD Naseem Ashraf Over a year ago

@Abhishek Did that version change and reinstallation of app fix your problem or does it still remain?

Abhishek P Over a year ago

Actually, the error caused at my first build itself. I didn't changed anything in code. First build, when version was 1, the app was crashing.

Abhishek P Over a year ago

Naseem, yes it's working now after changing version number.

Abhishek P · Accepted Answer · 2019-03-10 14:31:37Z

0

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.

answered Mar 10, 2019 at 14:31

Abhishek P

581 silver badge12 bronze badges

2 Comments

forpas Over a year ago

A change in the version number executes onUpgrade() and as you can see inside that method, the table is deleted and recreated. You could have done this by just unnstalling the app since you only have 1 table. Nothing more.

Abhishek P Over a year ago

I tried it, keeping version number as 1 and uninstalled and re installed. But it crashes. So only i'm confused. I know how onupgrade works, but here i'm confused.

AllanRibas · Accepted Answer · 2020-06-12 14:24:39Z

0

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
}

edited Jun 12, 2020 at 14:24

answered Jun 12, 2020 at 3:21

AllanRibas

1,22214 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related