SQLite - Table not updating when inserting a new row

sqlite update from select
sqlite update from another table
sqlite insert or update
sqlite update multiple tables
sqlite update multiple columns
python sqlite update multiple columns
sqlite insert query android
sqlite upsert

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.

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();
    }

SQLite REPLACE: Insert or Replace The Existing Row, Suppose, you want to add a position into the positions table if it does not exist, in case the position exists, update the current one. The following REPLACE  For example, if you are trying to update 10 rows, and the fifth row has a value that conflict with a constraint, then no rows will be updated, the 10 rows will stay the same. An error will be thrown. ABORT – this will abort (cancel) the current SQLite statement only that has the conflict and the transaction won't be cancelled.

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;
}

Learn SQLite UPDATE Statement with Examples, Second, set new value for each column of the table in the SET clause. If you skip it, the UPDATE statement will update data in all rows of the table. Notice that if use a negative value in the LIMIT clause, SQLite assumes that there are no limit and updates all rows that meet the condition in the Previous SQLite Insert. At the time of inserting a new row or updating row, check the value for the specific column whether it is violating the condition, and if the condition evaluates false no new row will be inserted or no row will be updated due to the violation of the constraint.

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.

SQLite INSERT, UPDATE, DELETE Query with Example, It is used for inserting new rows, updating existing values, or deleting rows from the database. SQLite INSERT is used to insert records into a specified table of the This should run successfully and there is no output for this:. The SELECT statement shows that the last two statements did not modify the fourth row. Since SQLite version 3.7.11 it is possible to insert multiple rows using one INSERT statement. sqlite> CREATE TEMP TABLE Ints(Id INTEGER PRIMARY KEY, Val INTEGER); We will use a one-column Ints table to show a multi-row INSERT statement. The table's lone

How to Write Flexible INSERT and UPDATE Statements in MySQL, How do you insert and update in a single query? Next, we prepared the SQLite UPDATE query to update a table row. In the update query, We mentioned the column name we want to update and its new value. This table contains five columns. In this example, we modified only one column i.e. salary. cursor. execute We executed the operation stored in the SQLite UPDATE query using a execute() method

SQL UPDATE Statement - Updating Data in a Table, Table columns that do not appear in the column list are populated with the default column A new entry is inserted into the table for each row of data returned by an INSERT to behave as an UPDATE if the INSERT would otherwise violate a  To insert data into a table, you use the INSERT statement. SQLite provides various forms of the INSERT statements that allow you to insert a single row, multiple rows, and default values into a table. In addition, you can insert a row into a table using data provided by a SELECT statement. SQLite INSERT – inserting a single row into a table

insert, How do I add or delete columns from an existing table in SQLite. It causes every row of the table to be returned, not just the rows where column1 has The SQLITE_MASTER table cannot be modified using UPDATE, INSERT, or DELETE​  The INSERT works on a single table, and can both insert one row at a time or many rows at once using a SELECT command. This statement is a part of the SQL Data Manipulation Language, DML. Syntax: INSERT [OR conflict-algorithm] INTO [database-name .] table-name [(column-list)] VALUES (value-list) Here we will create a new table prod_mast:

Comments
  • Use insertOrThrow() rather than insert() to detect insertion problems.