CODEWITHSUNDEEP
androidsqliteandroid-activityandroid-sqlite
Nikhil Kumar
Nikhil Kumar

Reputation: 141

Retrieve data from SQLite database and update it

I am trying to retrieve a table and update one of its columns later on.

I got most of the part like inserting some data and retrieving it back, but I am not able to update one of the column.

DBhelper.class:

 String CREATE_USER_CONTACTS_TABLE = "CREATE TABLE " + TABLE_USER + "("
            + USER_ID + " INTEGER PRIMARY KEY   AUTOINCREMENT," + USER_NAME 
       + " TEXT,"
            + USER_PH_NO + " TEXT" + USER_DISPLAY_NAME + " TEXT" +  
            USER_DISPLAY_PICTURE + " TEXT" + ")";
    db.execSQL(CREATE_USER_CONTACTS_TABLE);


public void addUser(Database_Contact contact) {
    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(USER_NAME, contact.get_owner_name());
    values.put(USER_PH_NO, contact.get_owner_phone_number());

    // Inserting Row
    db.insert(TABLE_USER, null, values);
    db.close(); // Closing database connection
}
public List<Database_Contact> getAllDatabase_Users() {
    List<Database_Contact> userList = new ArrayList<Database_Contact>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_USER;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            Database_Contact contact = new Database_Contact();
            contact.set_user_id(Integer.parseInt(cursor.getString(0)));
            contact.set_owner_name(cursor.getString(1));
            contact.set_owner_phone_number(cursor.getString(2));
            // Adding contact to list
            userList.add(contact);
        } while (cursor.moveToNext());
    }

    // return contact list
    return userList;
}
public int setDisplayImage(long id,String path) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(USER_DISPLAY_PICTURE, path);
    // updating row
    return db.update(TABLE_USER, values, USER_ID +"="+id,null);
}

DatabaseContact.class:

public class Database_Contact {

//private variables


int _user_id;
String _display_name;
String _display_picture;
String _owner_phone_number;
String _owner_name;



public int get_user_id() {
    return _user_id;
}

public void set_user_id(int _user_id) {
    this._user_id = _user_id;
}




public String get_display_name() {
    return _display_name;
}

public void set_display_name(String _display_name) {
    this._display_name = _display_name;
}

public String get_display_picture() {
    return _display_picture;
}

public void set_display_picture(String _display_picture) {
    this._display_picture = _display_picture;
}

public String get_owner_phone_number() {
    return _owner_phone_number;
}

public void set_owner_phone_number(String _owner_phone_number) {
    this._owner_phone_number = _owner_phone_number;
}

public String get_owner_name() {
    return _owner_name;
}

public void set_owner_name(String _owner_name) {
    this._owner_name = _owner_name;
}

}

Mainactivity.class:

mydb = new DBHelper(this);
    List<Database_conytact> images = mydb.getAllDatabase_Users();
    mydb.setDisplayImage(images.get(0).get_user_id(), "String");

I am getting the following error:

android.database.sqlite.SQLiteException: near "Image": syntax error (code 1): , while compiling: UPDATE User SET Display Image=? WHERE user_id='1' at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:891) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:502) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteStatement.(SQLiteStatement.java:31) at android.database.sqlite.SQLiteDatabase.updateWithOnConflict(SQLiteDatabase.java:1574) at android.database.sqlite.SQLiteDatabase.update(SQLiteDatabase.java:1522) at com.example.gnaveen.securityapp.DBHelper.setDisplayImage(DBHelper.java:296) at com.example.gnaveen.securityapp.DisplayPicture$1.onClick(DisplayPicture.java:61) at android.view.View.performClick(View.java:4789) at android.view.View$PerformClick.run(View.java:19881) at android.os.Handler.handleCallback(Handler.java:739) at android.os.Handler.dispatchMessage(Handler.java:95) at android.os.Looper.loop(Looper.java:135) at android.app.ActivityThread.main(ActivityThread.java:5293) at java.lang.reflect.Method.invoke(Native Method) at java.lang.reflect.Method.invoke(Method.java:372) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:904) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:699)

Upvotes: 0

Views: 1910

Answers (6)

ann
ann

Reputation: 1

public Profile.Users readfor(String userName){

    SQLiteDatabase db = this.getWritableDatabase();

    String readSingleQuery = "SELECT * FROM "+ Profile.Users.T_NAME+" WHERE "+ Profile.Users.COL2_UNAME + " =  '"+ userName+"'";

    Cursor cursor = db.rawQuery(readSingleQuery,null);

    if(cursor.moveToFirst()){

        Profile.Users users = Profile.getProfile().getUser();

        users.setId(Integer.parseInt(cursor.getString(0)));
        users.setUsername(cursor.getString(1));
        users.setPassword(cursor.getString(2));

        return users;
    }

    return null;
}

Upvotes: 0

k3b
k3b

Reputation: 14755

SQL-syntax error: there is a blank in the fieldname in the stacktrace which is not ok for sql.

 "SET Display Image=?"

Upvotes: 0

Phant&#244;maxx
Phant&#244;maxx

Reputation: 38098

Your table creation is wrong

 String CREATE_USER_CONTACTS_TABLE = "CREATE TABLE " + TABLE_USER + "("
        + USER_ID + " INTEGER PRIMARY KEY   AUTOINCREMENT," + USER_NAME 
   + " TEXT,"
        + USER_PH_NO + " TEXT" + USER_DISPLAY_NAME + " TEXT" +  
        USER_DISPLAY_PICTURE + " TEXT" + ")";

You are missing some commas. Try this

 String CREATE_USER_CONTACTS_TABLE = "CREATE TABLE " + TABLE_USER + " (" +
        USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + USER_NAME +
        " TEXT, " + USER_PH_NO + " TEXT, " + USER_DISPLAY_NAME + " TEXT, " +  
        USER_DISPLAY_PICTURE + " TEXT)";

You will need to uninstall ans reinstall your app, in order for the table to be re-created

Upvotes: 0

Sara Tirmizi
Sara Tirmizi

Reputation: 1

  1. What error are you getting actually ?? Null pointer exception?

  2. From the above code I am getting that every time you execute your code it creates a table so rather use.

    String CREATE_USER_CONTACTS_TABLE = "CREATE TABLE IF NOT EXISTS " +     TABLE_USER + "("
    + USER_ID + " INTEGER PRIMARY KEY   AUTOINCREMENT," + USER_NAME +" TEXT,"
    + USER_PH_NO + " TEXT" + USER_DISPLAY_NAME + " TEXT" +  
    USER_DISPLAY_PICTURE + " TEXT" + ")";

This will create table only if it doesn't exists!

Upvotes: 0

Rustam
Rustam

Reputation: 6515

try this:

 String where="USER_ID =?";
 return db.update(TABLE_USER,values,where,new String[]{""+id});

Upvotes: 0

Kaveesh Kanwal
Kaveesh Kanwal

Reputation: 1773

Why don't you try this:

return db.update(TABLE_USER, values, USER_ID  + "='" + id +"'", null);

Hope this works for you

Upvotes: 0

Related Questions