title: Android: ListView with CheckBox, populated from SQLite database not quite working
body: <p>As with several other posts here, I am trying to create a ListView that includes a CheckBox for each row, and use a SQLite database to store the current state of the selection.</p>
  
  <p>Starting with the example at <a href="http://appfulcrum.com/?p=351" rel="noreferrer">http://appfulcrum.com/?p=351</a>, which did not quite work as is, I created a simple app that creates the database, populates it with 20 items, and displays the list.  </p>
  
  <p>It successfully retrieves the state and stores the state of the selection.</p>
  
  <p>BUT, it does not correctly show the CheckBox state if I change it, scroll to the other end of the list, and scroll back.  e.g. if I select the first CheckBox, scroll to the bottom, and come back to the top, the CheckBox is no longer set.  This is being run on an Android 2.1 Samsung handset.</p>
  
  <p>If I return to the main screen, come back into the list, the CheckBox <strong>is</strong> correctly set, so the database has indeed been updated.</p>
  
  <p>The example extends SimpleCursorAdapter, and the getView() invokes setChecked() with true or false as appropriate based on the value of the selection column in the table.</p>
  
  <p>Below are all the sources.</p>
  
  <p>I'd certainly appreciate being told, "Duh, here's your problem..."</p>
  
  <p><strong>CustomListViewDB.java</strong></p>
  
  <pre><code>// src/CustomListViewDB.java
  package com.appfulcrum.blog.examples.listviewcustomdb;
  
  import android.app.ListActivity;
  import android.database.Cursor;
  import android.database.SQLException;
  import android.os.AsyncTask;
  import android.os.Bundle;
  import android.view.View;
  import android.view.View.OnClickListener;
  import android.widget.Button;
  import android.widget.ListView;
  import android.widget.Toast;
  
  public class CustomListViewDB extends ListActivity {
  
      private ListView mainListView = null;
      CustomSqlCursorAdapter adapter = null;
      private SqlHelper dbHelper = null;
      private Cursor currentCursor = null;
  
      private ListView listView = null;
  
      /** Called when the activity is first created. */
      @Override
      public void onCreate(Bundle savedInstanceState) {
          super.onCreate(savedInstanceState);
          setContentView(R.layout.simple);
  
          if (this.dbHelper == null) {
              this.dbHelper = new SqlHelper(this);
  
          }
  
          listView = getListView();
          listView.setItemsCanFocus(false);
          listView.setChoiceMode(ListView.CHOICE_MODE_MULTIPLE);
          //listView.setClickable(true);
  
          Button btnClear = (Button) findViewById(R.id.btnClear);
          btnClear.setOnClickListener(new OnClickListener() {
  
              public void onClick(View v) {
                  Toast.makeText(getApplicationContext(),
                          " You clicked Clear button", Toast.LENGTH_SHORT).show();
                  ClearDBSelections();
              }
          });
  
          new SelectDataTask().execute();
  
          this.mainListView = getListView();
  
          mainListView.setCacheColorHint(0);
  
      }
  
      @Override
      protected void onRestart() {
          super.onRestart();
          new SelectDataTask().execute();
      }
  
      @Override
      protected void onPause() {
  
          super.onPause();
          this.dbHelper.close();
      }
  
      protected void ClearDBSelections() {
  
          this.adapter.ClearSelections();
  
      }
  
      private class SelectDataTask extends AsyncTask&lt;Void, Void, String&gt; {
  
          protected String doInBackground(Void... params) {
  
              try {
  
                  CustomListViewDB.this.dbHelper.createDatabase(dbHelper.dbSqlite);
                  CustomListViewDB.this.dbHelper.openDataBase();
  
                  CustomListViewDB.this.currentCursor = CustomListViewDB.this.dbHelper
                          .getCursor();
  
              } catch (SQLException sqle) {
  
                  throw sqle;
  
              }
              return null;
          }
  
          // can use UI thread here
          protected void onPostExecute(final String result) {
  
              startManagingCursor(CustomListViewDB.this.currentCursor);
              int[] listFields = new int[] { R.id.txtTitle };
              String[] dbColumns = new String[] { SqlHelper.COLUMN_TITLE };
  
              CustomListViewDB.this.adapter = new CustomSqlCursorAdapter(
                      CustomListViewDB.this, R.layout.single_item,
                      CustomListViewDB.this.currentCursor, dbColumns, listFields,
                      CustomListViewDB.this.dbHelper);
              setListAdapter(CustomListViewDB.this.adapter);
  
          }
      }
  
  }
  </code></pre>
  
  <p><strong>CustomSqlCursorAdapter.java</strong></p>
  
  <pre><code>// src/CustomSqlCursorAdapter.java
  
  package com.appfulcrum.blog.examples.listviewcustomdb;
  
  import android.content.ContentValues;
  import android.content.Context;
  import android.database.Cursor;
  import android.database.SQLException;
  import android.util.Log;
  import android.view.LayoutInflater;
  import android.view.View;
  import android.view.ViewGroup;
  import android.widget.CheckBox;
  import android.widget.CompoundButton;
  import android.widget.CompoundButton.OnCheckedChangeListener;
  import android.widget.SimpleCursorAdapter;
  import android.widget.TextView;
  
  public class CustomSqlCursorAdapter extends SimpleCursorAdapter {
      private Context mContext;
  
      private SqlHelper mDbHelper;
      private Cursor mCurrentCursor;
  
      public CustomSqlCursorAdapter(Context context, int layout, Cursor c,
              String[] from, int[] to, SqlHelper dbHelper) {
          super(context, layout, c, from, to);
          this.mCurrentCursor = c;
          this.mContext = context;
          this.mDbHelper = dbHelper;
  
      }
  
      public View getView(int pos, View inView, ViewGroup parent) {
          View v = inView;
          if (v == null) {
              LayoutInflater inflater = (LayoutInflater) mContext
                      .getSystemService(Context.LAYOUT_INFLATER_SERVICE);
              v = inflater.inflate(R.layout.single_item, null);
          }
  
          if (!this.mCurrentCursor.moveToPosition(pos)) {
              throw new SQLException("CustomSqlCursorAdapter.getView: Unable to move to position: "+pos);
          }
  
          CheckBox cBox = (CheckBox) v.findViewById(R.id.bcheck);
  
          // save the row's _id value in the checkbox's tag for retrieval later
          cBox.setTag(Integer.valueOf(this.mCurrentCursor.getInt(0)));
  
          if (this.mCurrentCursor.getInt(SqlHelper.COLUMN_SELECTED_idx) != 0) {
              cBox.setChecked(true);
              Log.w("SqlHelper", "CheckBox true for pos "+pos+", id="+this.mCurrentCursor.getInt(0));
          } else {
              cBox.setChecked(false);
              Log.w("SqlHelper", "CheckBox false for pos "+pos+", id="+this.mCurrentCursor.getInt(0));
          }
          //cBox.setOnClickListener(this);
          cBox.setOnCheckedChangeListener(new OnCheckedChangeListener() {
  
              @Override
              public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {
  
                  Log.w("SqlHelper", "Selected a CheckBox and in onCheckedChanged: "+isChecked);
  
                  Integer _id = (Integer) buttonView.getTag();
                  ContentValues values = new ContentValues();
                  values.put(SqlHelper.COLUMN_SELECTED, 
                          isChecked ? Integer.valueOf(1) : Integer.valueOf(0));
                  mDbHelper.dbSqlite.beginTransaction();
                  try {
                      if (mDbHelper.dbSqlite.update(SqlHelper.TABLE_NAME, values, "_id=?",
                              new String[] { Integer.toString(_id) }) != 1) {
                          throw new SQLException("onCheckedChanged failed to update _id="+_id);
                      }
                      mDbHelper.dbSqlite.setTransactionSuccessful();
                  } finally {
                      mDbHelper.dbSqlite.endTransaction();
                  }
  
                  Log.w("SqlHelper", "-- _id="+_id+", isChecked="+isChecked);
              }
          });
  
          TextView txtTitle = (TextView) v.findViewById(R.id.txtTitle);
          txtTitle.setText(this.mCurrentCursor.getString(this.mCurrentCursor
                  .getColumnIndex(SqlHelper.COLUMN_TITLE)));
  
          return (v);
      }
  
      public void ClearSelections() {
          this.mDbHelper.clearSelections();
          this.mCurrentCursor.requery();
  
      }
  }
  </code></pre>
  
  <p><strong>ListViewWithDBActivity.java</strong></p>
  
  <pre><code>package com.appfulcrum.blog.examples.listviewcustomdb;
  
  import android.app.Activity;
  import android.os.Bundle;
  
  public class ListViewWithDBActivity extends Activity {
      /** Called when the activity is first created. */
      @Override
      public void onCreate(Bundle savedInstanceState) {
          super.onCreate(savedInstanceState);
          setContentView(R.layout.main);
      }
  }
  </code></pre>
  
  <p><strong>SqlHelper</strong></p>
  
  <pre><code>// SqlHelper.java
  
  package com.appfulcrum.blog.examples.listviewcustomdb;
  
  import android.content.ContentValues;
  import android.content.Context;
  import android.database.Cursor;
  import android.database.SQLException;
  import android.database.sqlite.SQLiteDatabase;
  import android.database.sqlite.SQLiteOpenHelper;
  import android.database.sqlite.SQLiteQueryBuilder;
  import android.database.sqlite.SQLiteStatement;
  import android.util.Log;
  
  public class SqlHelper extends SQLiteOpenHelper {
      private static final String DATABASE_PATH = "/data/data/com.appfulcrum.blog.examples.listviewcustomdb/databases/";
  
      public static final String DATABASE_NAME = "TODOList";
  
      public static final String TABLE_NAME = "ToDoItems";
      public static final int ToDoItems_VERSION = 1;
  
      public static final String COLUMN_ID = "_id";               // 0
      public static final String COLUMN_TITLE = "title";          // 1
      public static final String COLUMN_NAME_DESC = "description";// 2
      public static final String COLUMN_SELECTED = "selected";    // 3
      public static final int    COLUMN_SELECTED_idx = 3;
  
      public SQLiteDatabase dbSqlite;
      private Context mContext;
  
      public SqlHelper(Context context) {
          super(context, DATABASE_NAME, null, 1);
          mContext = context;
      }
  
      @Override
      public void onCreate(SQLiteDatabase db) {
          createDB(db);
      }
  
      @Override
      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
          Log.w("SqlHelper", "Upgrading database from version " + oldVersion
                  + " to " + newVersion + ", which will destroy all old data");
  
          db.execSQL("DROP TABLE IF EXISTS ToDoItems;");
  
          createDB(db);
      }
  
      public void createDatabase(SQLiteDatabase db) {
          createDB(db);
      }
  
      private void createDB(SQLiteDatabase db) {
          if (db == null) {
              db = mContext.openOrCreateDatabase(DATABASE_NAME, 0, null);
          }
  
          db.execSQL("CREATE TABLE IF NOT EXISTS ToDoItems (_id INTEGER PRIMARY KEY, title TEXT, "
                  +" description TEXT, selected INTEGER);");
          db.setVersion(ToDoItems_VERSION);
  
          //
          // Generate a few rows for an example
          //
          // find out how many rows already exist, and make sure there's some minimum
          SQLiteStatement s = db.compileStatement("select count(*) from ToDoItems;");
  
          long count = s.simpleQueryForLong();
          for (int i = 0; i &lt; 20-count; i++) {
              db.execSQL("INSERT INTO ToDoItems VALUES(NULL,'Task #"+i+"','Description #"+i+"',0);");
          }
      }
  
      public void openDataBase() throws SQLException {
          String myPath = DATABASE_PATH + DATABASE_NAME;
  
          dbSqlite = SQLiteDatabase.openDatabase(myPath, null,
                  SQLiteDatabase.OPEN_READWRITE);
      }
  
      @Override
      public synchronized void close() {
          if (dbSqlite != null)
              dbSqlite.close();
  
          super.close();
      }
  
      public Cursor getCursor() {
          SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
  
          queryBuilder.setTables(TABLE_NAME);
  
          String[] asColumnsToReturn = new String[] { COLUMN_ID, COLUMN_TITLE,
                  COLUMN_NAME_DESC, COLUMN_SELECTED };
  
          Cursor mCursor = queryBuilder.query(dbSqlite, asColumnsToReturn, null,
                  null, null, null, COLUMN_ID+" ASC");
  
          return mCursor;
      }
  
      public void clearSelections() {
          ContentValues values = new ContentValues();
          values.put(COLUMN_SELECTED, 0);
          this.dbSqlite.update(SqlHelper.TABLE_NAME, values, null, null);
      }
  }
  </code></pre>
  
  <p><strong>Start.java</strong></p>
  
  <pre><code>//src/Start.java
  package com.appfulcrum.blog.examples.listviewcustomdb;
  
  import android.app.Activity;
  import android.content.Intent;
  import android.os.Bundle;
  import android.view.View;
  import android.view.View.OnClickListener;
  import android.widget.Button;
  import android.widget.Toast;
  
  public class Start extends Activity {
      /** Called when the activity is first created. */
      @Override
      public void onCreate(Bundle savedInstanceState) {
          super.onCreate(savedInstanceState);
          setContentView(R.layout.main);
  
          Button btnSimple = (Button) findViewById(R.id.btnSimple);
          btnSimple.setOnClickListener(new OnClickListener() {
  
              public void onClick(View v) {
  
                  Toast.makeText(getApplicationContext(),
                          " You clicked ListView From DB button", Toast.LENGTH_SHORT).show();
  
                  Intent intent = new Intent(v.getContext(), CustomListViewDB.class);
                  startActivityForResult(intent, 0);
              }
          });
  
      }
  }
  </code></pre>
  
  <p><strong>layout/main.xml</strong></p>
  
  <pre><code>&lt;LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
      android:id="@+id/buttonlayout" android:orientation="vertical"
      android:layout_width="fill_parent" android:layout_height="fill_parent"
      android:gravity="left|top" android:paddingTop="2dp"
      android:paddingBottom="2dp"&gt;
  
      &lt;TextView android:id="@+id/txtTest" android:layout_width="fill_parent"
          android:layout_height="wrap_content" android:textStyle="bold"
          android:text="@string/app_name" android:textSize="15sp"
          android:textColor="#FF0000" android:gravity="center_vertical"
          android:paddingLeft="5dp"&gt;
      &lt;/TextView&gt;
  
      &lt;Button android:id="@+id/btnSimple" 
          android:layout_width="fill_parent"
          android:layout_height="wrap_content" 
          android:textSize="15sp"
          android:text="Listview from DB"
          android:textColor="#000000"
          &gt;
      &lt;/Button&gt;
  
  &lt;/LinearLayout&gt;
  </code></pre>
  
  <p><strong>layout/simple.xml</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
      android:orientation="vertical" android:layout_width="fill_parent"
      android:layout_height="fill_parent"&gt;
  
      &lt;LinearLayout android:id="@+id/buttonlayout"
          android:orientation="horizontal" android:layout_width="fill_parent"
          android:layout_height="wrap_content" android:height="32dp"
          android:gravity="left|top" android:paddingTop="2dp"
          android:paddingBottom="2dp"&gt;
  
          &lt;LinearLayout android:id="@+id/buttonlayout2"
              android:orientation="horizontal" android:layout_height="wrap_content"
              android:gravity="left|center_vertical" android:layout_width="wrap_content"
              android:layout_gravity="left|center_vertical"&gt;
  
              &lt;TextView android:id="@+id/txtTest"
                  android:layout_width="fill_parent"
                  android:layout_height="fill_parent" android:textStyle="bold"
                  android:text="@string/list_header" android:textSize="15sp"
                  android:gravity="center_vertical" android:paddingLeft="5dp"&gt;
              &lt;/TextView&gt;
  
              &lt;Button android:id="@+id/btnClear"
                  android:layout_width="wrap_content"
                  android:layout_height="wrap_content" android:text="Clear"
                  android:textSize="15sp" android:layout_marginLeft="10px"
                  android:layout_marginRight="10px"
                  android:layout_marginBottom="2px"
                  android:layout_marginTop="2px" android:height="15dp"
                  android:width="70dp"&gt;&lt;/Button&gt;
          &lt;/LinearLayout&gt;
      &lt;/LinearLayout&gt;
  
      &lt;TableLayout android:id="@+id/TableLayout01"
          android:layout_width="fill_parent" android:layout_height="fill_parent"
          android:stretchColumns="*"&gt;
          &lt;TableRow&gt;
              &lt;ListView android:id="@android:id/list"
                  android:layout_width="wrap_content"
                  android:layout_height="wrap_content"&gt;&lt;/ListView&gt;
          &lt;/TableRow&gt;
  
      &lt;/TableLayout&gt;
  
  &lt;/LinearLayout&gt;
  </code></pre>
  
  <p><strong>layout/single_item.xml</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
      android:layout_width="fill_parent" android:layout_height="wrap_content"
      android:orientation="horizontal" android:gravity="center_vertical"&gt;
  
      &lt;CheckBox android:id="@+id/bcheck"
                android:layout_width="wrap_content"
                android:layout_height="fill_parent" /&gt;
  
          &lt;TextView android:id="@+id/txtTitle"
              android:layout_width="wrap_content" android:gravity="left|center_vertical"
              android:layout_height="?android:attr/listPreferredItemHeight"
              android:layout_alignParentLeft="true" 
              android:textSize="20sp" android:text="Test"
              android:textStyle="bold" android:paddingLeft="5dp"
              android:paddingRight="2dp" android:focusable="false"
              android:focusableInTouchMode="false"&gt;&lt;/TextView&gt;
          &lt;LinearLayout android:layout_width="fill_parent"
              android:layout_height="wrap_content" android:orientation="horizontal"
              android:gravity="right|center_vertical"&gt;
          &lt;/LinearLayout&gt;
  
  &lt;/LinearLayout&gt;
  </code></pre>
  
  <p><strong>values/strings.xml</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;resources&gt;
      &lt;string name="hello"&gt;Hello World, ListViewWithDBActivity!&lt;/string&gt;
      &lt;string name="app_name"&gt;ListViewWithDB&lt;/string&gt;
      &lt;string name="list_header"&gt;List Headers&lt;/string&gt;
  &lt;/resources&gt;
  </code></pre>
  
  <p><strong>AndroidManifest.xml</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;manifest xmlns:android="http://schemas.android.com/apk/res/android"
      android:versionCode="1" android:versionName="1.0"    
      package="com.appfulcrum.blog.examples.listviewcustomdb"&gt;
  
      &lt;application android:icon="@drawable/icon"
          android:label="@string/app_name" 
          android:theme="@android:style/Theme.NoTitleBar"&gt;
          &gt;
          &lt;activity android:name=".Start" android:label="@string/app_name"&gt;
              &lt;intent-filter&gt;
                  &lt;action android:name="android.intent.action.MAIN" /&gt;
                  &lt;category android:name="android.intent.category.LAUNCHER" /&gt;
              &lt;/intent-filter&gt;
          &lt;/activity&gt;
          &lt;activity android:name=".CustomListViewDB"&gt;&lt;/activity&gt;
      &lt;/application&gt;
  
      &lt;uses-sdk android:minSdkVersion="7" /&gt; &lt;!-- android 1.6 --&gt;
  &lt;/manifest&gt;
  </code></pre>
  
  <p>If you want to build, throw some arbitrary icon.png into drawable.</p>
  
  <p>Thanks in advance.</p>