SQLite - Table not updating when inserting a new row

Question

I'm trying to make an application that allow users to add contacts, then preview them. I'm using SQLite to save the data locally. I have created an SQLHelper for my database, but it is showing any data when I try to SELECT all.

SQLHelper

package com.freelancer.camelo.contacts;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.HashMap;

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "MyContactsDb.db";
    public static final String CONTACTS_TABLE_NAME = "contacts";
    public static final String CONTACTS_COLUMN_ID = "id";
    public static final String CONTACTS_COLUMN_NAME = "name";
    public static final String CONTACTS_COLUMN_SURNAME = "surname";
    public static final String CONTACTS_COLUMN_ADDRESS = "address";
    public static final String CONTACTS_COLUMN_POSTAL = "postal";
    public static final String CONTACTS_COLUMN_PHONE = "phone";
    private HashMap hp;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME , null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL(
                "create table contacts " +
                        "(id integer primary key, name text,surname text,phone text, address text,postal text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS contacts");
        onCreate(db);
    }

    public boolean insertContact (String name, String surname, String phone, String address,String postal) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(CONTACTS_COLUMN_NAME, name);
        contentValues.put(CONTACTS_COLUMN_SURNAME, surname);
        contentValues.put(CONTACTS_COLUMN_PHONE, phone);
        contentValues.put(CONTACTS_COLUMN_ADDRESS, address);
        contentValues.put(CONTACTS_COLUMN_POSTAL, postal);
        db.insert("contacts", null, contentValues);
        return true;
    }

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

    public int numberOfRows(){
        SQLiteDatabase db = this.getReadableDatabase();
        int numRows = (int) DatabaseUtils.queryNumEntries(db, CONTACTS_TABLE_NAME);
        return numRows;
    }

    public boolean updateContact (Integer id, String name, String surname, String phone, String address,String postal) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(CONTACTS_COLUMN_NAME, name);
        contentValues.put(CONTACTS_COLUMN_SURNAME, surname);
        contentValues.put(CONTACTS_COLUMN_PHONE, phone);
        contentValues.put(CONTACTS_COLUMN_ADDRESS, address);
        contentValues.put(CONTACTS_COLUMN_POSTAL, postal);
        db.update("contacts", contentValues, "id = ? ", new String[] { Integer.toString(id) } );
        return true;
    }

    public Integer deleteContact (Integer id) {
        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete("contacts",
                "id = ? ",
                new String[] { Integer.toString(id) });
    }

    public ArrayList<String> getAllContacts() {
        ArrayList<String> array_list = new ArrayList<>();

        SQLiteDatabase db = this.getReadableDatabase();
        String selectQuery = "SELECT * FROM "+CONTACTS_TABLE_NAME;
        Cursor res =  db.rawQuery( selectQuery, null );
        res.moveToFirst();

        while(!res.isAfterLast()){
            array_list.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_NAME)));
            res.moveToNext();
        }
        return array_list;
    }
}

I'm using this bunch of code to insert into the db and it actually toasts "added"

if (mydb.insertContact("one", "two", "three", "foir", "five")) {
                    Toast.makeText(AddContactActivity.this, "added", Toast.LENGTH_SHORT).show();
                }

This is when I'm trying to retrieve data

ArrayList<String> contactsArrayList = mydb.getAllContacts();

        if (contactsArrayList.size() == 0) {
            Toast.makeText(this, "DB is empty", Toast.LENGTH_SHORT).show();
        } else {
            for (int i = 0; i < contactsArrayList.size(); i++) {
                Toast.makeText(this, contactsArrayList.get(i).toString(), Toast.LENGTH_SHORT).show();
            }
        }

This simply keeps toasting "DB is empty" everytime I run the activity.

Use `insertOrThrow()` rather than `insert()` to detect insertion problems. — laalto, Jan 17 '19 at 07:58

score 0 · Accepted Answer · answered Jan 17 '19 at 03:49

I've modified your code a little bit:

public ArrayList<String> getAllContacts() {
    ArrayList<String> array_list = new ArrayList<>();

    SQLiteDatabase db = this.getReadableDatabase();
    String selectQuery = "SELECT * FROM "+CONTACTS_TABLE_NAME;
    Cursor res =  db.rawQuery( selectQuery, null );

    //check if data exist, proceed
    if (res.moveToNext()){
        do{
            array_list.add(res.getString(res.getColumnIndex(CONTACTS_COLUMN_NAME)));
        }while(res.moveToNext());
    }

    //remember to close cursor after use
    res.close();

    return array_list;
}


//void instead of boolean
public void insertContact (String name, String surname, String phone, String address,String postal) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();
    contentValues.put(CONTACTS_COLUMN_NAME, name);
    contentValues.put(CONTACTS_COLUMN_SURNAME, surname);
    contentValues.put(CONTACTS_COLUMN_PHONE, phone);
    contentValues.put(CONTACTS_COLUMN_ADDRESS, address);
    contentValues.put(CONTACTS_COLUMN_POSTAL, postal);

    //throw if not inserted
    db.insertOrThrow("contacts", "", contentValues);
}

then you insert into your table like this:

try {
    mydb.insertContact("one", "two", "three", "foir", "five");
}catch(Exception e){
    Toast.makeText(context, "Insert Failed: " + e.toString(), Toast.LENGTH_SHORT).show();
    }

score 0 · Answer 2 · answered Jan 17 '19 at 06:53

Here's a sample, hope your db schemas are correct. This method belongs to your class which extends SqkiteDatabase.

    //-----------------------------------------------------------------------------------------------------
public boolean insertNote(NoteModel noteModel) {
    if (LOG_DEBUG) UtilLogger.showLogInsert(TAG, noteModel);

    try {
        ContentValues contentValues = new ContentValues();
        contentValues.put(DBSchema.DB_TITLE, noteModel.getTitle());
        contentValues.put(DBSchema.DB_IMAGE_PATH, noteModel.getImgUriPath());
        contentValues.put(DBSchema.DB_SUB_TEXT, noteModel.getSub_text());
        contentValues.put(DBSchema.DB_CREATE_DATE, noteModel.getCreateDate());
        contentValues.put(DBSchema.DB_UPDATE_DATE, noteModel.getUpdateDate());
        contentValues.put(DBSchema.DB_SCHEDULED_TIME_LONG, noteModel.getScheduleTimeLong());
        contentValues.put(DBSchema.DB_SCHEDULED_TIME_WHEN, noteModel.getScheduledWhenLong());
        contentValues.put(DBSchema.DB_SCHEDULED_TITLE, noteModel.getScheduledTitle());
        contentValues.put(DBSchema.DB_IS_ALARM_SCHEDULED, noteModel.getIsAlarmScheduled());
        contentValues.put(DBSchema.DB_IS_TASK_DONE, noteModel.getIsTaskDone());
        contentValues.put(DBSchema.DB_IS_ARCHIVED, noteModel.getIsArchived());

        long rowId = mSqLiteDatabase.insert(DBSchema.DB_TABLE_NAME, null, contentValues);
        if (LOG_DEBUG) Log.w(TAG, " insert Done :  at row Id: " + rowId);

        return true;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

score 0 · Answer 3 · answered Jan 17 '19 at 07:50

0

I think you need to check your table is created and your data is exist then you can retrieve. For this you just follow this link and see data in emulator.

If it's working then I think there is no issue in your retrieve data code.

answered Jan 17 '19 at 07:50

Nitish

995
7
17

SQLite - Table not updating when inserting a new row

3 Answers3