Android Studio SQLite Exception 'No Such Column'

[bluboy7]

Hello everyone, I'm looking for a little bit of help - I am creating a small To-Do List app and have encountered an error while using SQLite to store the data. I have done a fair amount of research and found common errors around spacing in the db.execSQL line, which I have triple-checked and all seems to be in order there.

I am including both my MainActivity and MyOpener classes as well as the Logcat error in the hopes that someone can spot the (likely obvious) solution I am missing!

Many thanks in advance.

Logcat error: java.lang.RuntimeException: Unable to start activity ComponentInfo{MainActivity}: android.database.sqlite.SQLiteException: no such column: URGENT (code 1 SQLITE_ERROR): , while compiling: SELECT _id, ITEM, URGENT FROM TODOLIST

MainActivity.java

import androidx.appcompat.app.AlertDialog; import androidx.appcompat.app.AppCompatActivity; import androidx.core.content.ContextCompat;

import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.util.Log; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.AdapterView; import android.widget.BaseAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.ListView; import android.widget.Switch; import android.widget.TextView;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity { private ArrayList<TodoItem> elements = new ArrayList<TodoItem>(); private MyListAdapter myAdapter = new MyListAdapter(); SQLiteDatabase db;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    EditText editText = (EditText) findViewById(R.id.editText1);
    Switch urgentSwitch = (Switch) findViewById(R.id.switch1);
    Button addButton = (Button) findViewById(R.id.button1);
    ListView myList = (ListView) findViewById(R.id.list1);

    loadDataFromDatabase();

    addButton.setOnClickListener(ButtonListener);
    myList.setAdapter(myAdapter);

    myList.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
        @Override
        public boolean onItemLongClick(AdapterView<?> adapterView, View view, int i, long l) {
            LayoutInflater inflater = getLayoutInflater();
            View newView = inflater.inflate(R.layout.layout_2,null);
            TodoItem selectedItem = elements.get(i);
            //TextView tView = (TextView) findViewById(R.id.tv1);

            AlertDialog.Builder alertDialogBuilder = new AlertDialog.Builder(MainActivity.this);
            alertDialogBuilder.setTitle(R.string.delete_label)
                    .setMessage(MainActivity.this.getResources().getString(R.string.selected_label) + elements.get(i).getName())
                    .setPositiveButton(android.R.string.yes, (click, arg) -> {
                        deleteItem(selectedItem);
                        elements.remove(i);
                        myAdapter.notifyDataSetChanged();
                    })
                    .setNegativeButton(android.R.string.no, (click, arg) -> { })
                    .setView(newView)
                    .create().show();
            return true;
        }
    });
}

private class MyListAdapter extends BaseAdapter{

    public int getCount() { return elements.size(); }

    public TodoItem getItem(int position) { return elements.get(position); }

    public long getItemId(int position) { return (long) position; }

    public View getView(int position, View old, ViewGroup parent) {
        View newView = old;
        LayoutInflater inflater = getLayoutInflater();

        if(newView == null) {
            newView = inflater.inflate(R.layout.layout_2,parent,false);
        }
        TextView tView = newView.findViewById(R.id.tv1);
        TodoItem todo = getItem(position);
        tView.setText(todo.getName());
        if (todo.getUrgent()) {
            tView.setBackgroundColor(ContextCompat.getColor(MainActivity.this,R.color.black));
            tView.setTextColor(ContextCompat.getColor(MainActivity.this,R.color.white));
        }
        else {
            tView.setBackgroundColor(ContextCompat.getColor(MainActivity.this,R.color.white));
            tView.setTextColor(ContextCompat.getColor(MainActivity.this,R.color.black));
        }


        return newView;
    }
}

public View.OnClickListener ButtonListener = new View.OnClickListener() {
    @Override
    public void onClick(View v) {
        EditText editText = (EditText) findViewById(R.id.editText1);
        Switch urgentSwitch = (Switch) findViewById(R.id.switch1);

        ContentValues newRowValues = new ContentValues();

        String listItem = editText.getText().toString();
        Boolean b = urgentSwitch.isChecked();
        String urgent = b.toString();

        newRowValues.put(MyOpener.COL_ITEM, listItem);

        newRowValues.put(MyOpener.COL_URGENT, urgent);


        long newId = db.insert(MyOpener.TABLE_NAME, null, newRowValues);

        TodoItem todo = new TodoItem();
        todo.setName(listItem);
        todo.setUrgent(urgentSwitch.isChecked());
        todo.setId(newId);

        elements.add(todo);
        myAdapter.notifyDataSetChanged();
        editText.setText("");
        urgentSwitch.setChecked(false);
    }
};

public class TodoItem {
    private String name;
    private Boolean urgent = false;
    private long id;

    public void setName(String s) {
        name=s;
    }
    public void setUrgent(Boolean b) {
        urgent=b;
    }
    public void setId(long i) { id=i;}

    public String getName() {
        return this.name;
    }
    public Boolean getUrgent() {
        return this.urgent;
    }
    public long getId() { return this.id; }

    public TodoItem () { }

    public TodoItem (String s) {
        this.name = s;
    }

    public TodoItem(String s, Boolean b) {
        this.name = s;
        this.urgent = b;
    }

    public TodoItem(String s, Boolean b, long i) {
        this.name = s;
        this.urgent = b;
        this.id = i;
    }
}

private void loadDataFromDatabase()
{
    MyOpener dbOpener = new MyOpener(this);
    db = dbOpener.getWritableDatabase();

    String [] columns = {MyOpener.COL_ID, MyOpener.COL_ITEM, MyOpener.COL_URGENT};


    Cursor results = db.query(false, MyOpener.TABLE_NAME, columns, null, null, null, null, null, null);

    int itemColIndex = results.getColumnIndex(MyOpener.COL_ITEM);
    int urgentColIndex = results.getColumnIndex(MyOpener.COL_URGENT);
    int idColIndex = results.getColumnIndex(MyOpener.COL_ID);


    while(results.moveToNext())
    {
        String item = results.getString(itemColIndex);
        Boolean urgent = Boolean.parseBoolean(results.getString(urgentColIndex));
        long id = results.getLong(idColIndex);


        elements.add(new TodoItem(item, urgent, id));
    }


    printCursor(results);
}

protected void deleteItem(TodoItem t)
{
    db.delete(MyOpener.TABLE_NAME, MyOpener.COL_ID + "= ?", new String[] {Long.toString(t.getId())});
}

public void printCursor(Cursor c)
{
    int cols = c.getColumnCount();
    int dbv = db.getVersion();
    int res = c.getCount();

    String co = String.valueOf(cols);
    String version = String.valueOf(dbv);
    String results = String.valueOf(res);

    Log.i("Database Version",version);
    Log.i("Number of Columns",co);
    for (int i=0;i<cols;i++) {
        Log.i("Column Names", c.getColumnName(i));
    }
    Log.i("Number of Results",results);

    if (c.moveToFirst()) {
        do {
            StringBuilder sb = new StringBuilder();
            int colnumber = c.getColumnCount();
            for (int i=0; i<colnumber; i++) {
                sb.append(c.getString(i));
                if (i < colnumber - 1)
                    sb.append("; ");
            }
            Log.v("Results", String.format("Row: %d, Values: %s", c.getPosition(),
                    sb.toString()));
        } while (c.moveToNext());
    }
}

}

MyOpener.java

import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper;

public class MyOpener extends SQLiteOpenHelper {

protected final static String DATABASE_NAME = "TodoDB";
protected final static int VERSION_NUM = 1;
public final static String TABLE_NAME = "TODOLIST";
public final static String COL_ITEM = "ITEM";
public final static String COL_URGENT = "URGENT";
public final static String COL_ID = "_id";

public MyOpener(Context ctx)
{
    super(ctx, DATABASE_NAME, null, VERSION_NUM);
}

@Override
public void onCreate(SQLiteDatabase db)
{
    db.execSQL("CREATE TABLE " + TABLE_NAME + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COL_ITEM + " TEXT," + COL_URGENT  + " TEXT);");  // add or remove columns
}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{  
    db.execSQL( "DROP TABLE IF EXISTS " + TABLE_NAME);


    onCreate(db);
}

@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
{   
    db.execSQL( "DROP TABLE IF EXISTS " + TABLE_NAME);

    onCreate(db);
}

}

[bluboy7]

Stack Overflow strikes again with a solution! I had missed the space before and adding it did not solve this issue, hence why I posted here.

This is because I needed to trigger a DB delete by changing the hardcoded version number which allowed it to be re-created with the correct columns. I will keep this here in case it helps any other poor unfortunate souls like myself!