Cannot read data from SQLite in android application

Question

I am new to Android and SQLite database. I have been following online examples for storing and retrieving data from database but I get error when reading. The code below is simple code for writing and reading data from database. When writing there is no error in application but when reading data, the application closes by displaying error. I am using Android studio 1.2.2 and testing it on Android version 5.0.1 Galaxy S4. Can any one tell me what is the problem here ?

/*********************activity_main.xml *************/


<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"     
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity">

    <TextView android:text="Hello Hello"
    android:id="@+id/msg"
    android:layout_width="wrap_content" android:layout_height="wrap_content" />

    <ListView android:id="@+id/contactListView"
        android:layout_width="match_parent"     android:layout_height="wrap_content"
        android:layout_below="@+id/msg"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_marginTop="48dp" />

</LinearLayout>

/*********************row.xml **************/
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="match_parent" android:layout_height="match_parent"
    android:padding="10dip" >

    <TextView android:id="@+id/Name"
        android:layout_height="wrap_content"     android:layout_width="match_parent"
        android:layout_marginLeft="25dp" />

</LinearLayout>

/****************** Main Activity *****************/

public class MainActivity extends ActionBarActivity {
    List<String> stringList = new ArrayList<String>();
    ListAdapter adapter = null;

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

        ListView listview = (ListView) findViewById(R.id.contactListView);
        adapter = new ListAdapter(this, stringList);
        listview.setAdapter(adapter);

        NameDatabase db = null;
        db = new NameDatabase(this);

        db.addContact(1, "Sujan Raj Shrestha");
        db.addContact(2, "Kim Sae Hyun");
        db.addContact(3, "Kim Jong Kwon");

        Cursor cr = db.getData(0);
        adapter.add(cr.getString(1).toString());
    }

    public class ListAdapter extends ArrayAdapter<String>{

        public ListAdapter(Context context, List<String> object){
            super(context, R.layout.row, object);
        }

    @Override 
    public View getView(int position, View convertView, ViewGroup parent){
        View row = convertView;
        String nm = stringList.get(position);

        if (row == null){
            LayoutInflater inflater = getLayoutInflater();
            row = inflater.inflate(R.layout.row, null);
        }
        ((TextView) row.findViewById(R.id.Name)).setText(nm);
        return row;
    }
}

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
    // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }
}

/ *************** NameDatabase.java ****************/

public class NameDatabase extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String NAMELIST = "nametable";

    public NameDatabase (Context context){
        super(context, NAMELIST, null, DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db){
        String CREATE_CONTACTS_TABLE = "CREATE TABLE contacts" +" (id INTEGER PRIMARY KEY, name TEXT NOT NULL )";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        db.execSQL("Drop TABLE IF EXISTS" + NAMELIST);
        onCreate(db);
    }

    public boolean addContact(int id, String name){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("id", id);
        values.put("name", name);
        db.insert(NAMELIST, null, values);
        db.close();
        return true;
    }

    public Cursor getData(int id){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from nametable where id=" + id + "", null);
        return res;
    }
}

Answer 1

You are trying to access the content of table using database name. Try using table name as "contacts". Replace NAMELIST = "contacts" , also Pass the Database name in Constructor like super(context, DATABASE_NAME, null, DATABASE_VERSION); and add variable

String DATABASE_NAME = "someName";

For getting Cursor results add

res.moveToNext();

below of Cursor res = db.rawQuery("select * from nametable where id=" + id + "", null);

Since you are querying the table but your cursor remains at -1 position by default. Therefore we have to move cursor to row 1 and so on.

Hope this works. :) Peace V

Answer 2

try this to fetch the data from database

cur = db.query(true, "contacts", new String[] {
        "id", "name"
}, "id = " + id, null, null, null, null, null);

Answer 3

I just figured out your issue the main issue is the table from where you are querying on is not correct, indeed it is your database name, They correct version of getData() is following:

public Cursor getData(int id){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("select * from contacts where id=" + id + "", null);
    return res;
}