How to correct this updating database in Android SQL

Question

Im trying to update a specific user's information in SQL database when a user Login and I dont know why do I get this error. Please help :(

I check the record first. If there is none, I will add the user, and if it exist in my SQL. Not sure if im getting this correct.

 public String RecordCheck(){
    FirebaseAuth firebaseAuth = FirebaseAuth.getInstance();
    FirebaseUser user = firebaseAuth.getCurrentUser();
    userEmail = user.getEmail().toString();

    String RecordHolder = "";
    SQLiteDatabase db = getWritableDatabase();
    String query = "" + "SELECT * FROM " + TABLE_USERINFO + " WHERE " + COLUMN_USEREMAIL + " = '" + userEmail.trim() + "'";
    //Cursor point to a location in your results
    Cursor c = db.rawQuery(query, null);

    if(c.equals("")){
        RecordHolder = "adduser";
    }
    else
        RecordHolder = "updateuser";

    db.close();
    return RecordHolder;

I have this in my Welcome Back class to know if its going to update if user exist or add if user doesnt exist.

String Record = dbHandler.RecordCheck();



            if(Record.equals("adduser")){
                dbHandler.addUser(userdata);
            }
            else {
                dbHandler.updateUser(userdata);
            }

if update is called

public void updateUser(Users user){
    FirebaseAuth firebaseAuth = FirebaseAuth.getInstance();
    FirebaseUser userf = firebaseAuth.getCurrentUser();
    userEmail = userf.getEmail();

    ContentValues values = new ContentValues();
    values.put(MyDBHandler.COLUMN_USEREMAIL, user.get_useremail());
    values.put(MyDBHandler.COLUMN_NAME, user.getName());
    values.put(MyDBHandler.COLUMN_SEX, user.getSex());
    values.put(MyDBHandler.COLUMN_BDAY, user.getBday());
    values.put(MyDBHandler.COLUMN_HEIGHT, user.getHeight());
    values.put(MyDBHandler.COLUMN_MHEIGHT, user.getMheight());
    values.put(MyDBHandler.COLUMN_WEIGHT, user.getWeight());
    values.put(MyDBHandler.COLUMN_MWEIGHT, user.getMweight());
    values.put(MyDBHandler.COLUMN_USEREXPERIENCE, user.getUserexperience());
    values.put(MyDBHandler.COLUMN_FQUESTION, user.getFquestion());
    values.put(MyDBHandler.COLUMN_SQUESTION, user.getSquestion());
    values.put(MyDBHandler.COLUMN_TQUESTION, user.getTquestion());
    values.put(MyDBHandler.COLUMN_BODYSTATE, user.getBodystate());
    values.put(MyDBHandler.COLUMN_APPARENTLYHEALTHY, user.isApparentlyhealthy());
    values.put(MyDBHandler.COLUMN_HYPERTENSION, user.isHypertension());
    values.put(MyDBHandler.COLUMN_DIABETES, user.isDiabetes());
    values.put(MyDBHandler.COLUMN_ASTHMA, user.isAsthma());

    SQLiteDatabase db = getWritableDatabase();
    db.update(MyDBHandler.TABLE_USERINFO, values, userEmail, null);
    db.close();
}

--------- beginning of crash

11-22 12:44:13.179 17496-17496/com.example.abad.maxfitness2 E/AndroidRuntime: FATAL EXCEPTION: main
                                                                              Process: com.example.abad.maxfitness2, PID: 17496
                                                                              android.database.sqlite.SQLiteException: near "@gmail": syntax error (code 1): , while compiling: UPDATE users SET fquestion=?,mheight=?,tquestion=?,asthma=?,height=?,bodystate=?,diabetes=?,weight=?,bday=?,useremail=?,squestion=?,hypertension=?,name=?,healthy=?,mweight=?,userexp=?,sex=? WHERE testing8@gmail.com
                                                                                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
                                                                                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
                                                                                  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
                                                                                  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
                                                                                  at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                                  at android.database.sqlite.SQLiteDatabase.updateWithOnConflict(SQLiteDatabase.java:1577)
                                                                                  at android.database.sqlite.SQLiteDatabase.update(SQLiteDatabase.java:1525)
                                                                                  at com.example.abad.maxfitness2.LocalDatabase.MyDBHandler.updateUser(MyDBHandler.java:139)
                                                                                  at com.example.abad.maxfitness2.WelcomeBack$1.onDataChange(WelcomeBack.java:83)
                                                                                  at com.google.android.gms.internal.zzbmz.zza(Unknown Source)
                                                                                  at com.google.android.gms.internal.zzbnz.zzYj(Unknown Source)
                                                                                  at com.google.android.gms.internal.zzboc$1.run(Unknown Source)
                                                                                  at android.os.Handler.handleCallback(Handler.java:751)
                                                                                  at android.os.Handler.dispatchMessage(Handler.java:95)
                                                                                  at android.os.Looper.loop(Looper.java:154)
                                                                                  at android.app.ActivityThread.main(ActivityThread.java:6119)
                                                                                  at java.lang.reflect.Method.invoke(Native Method)
                                                                                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886)
                                                                                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)

what is the output of this ? `user.get_useremail()` , it seems there is some problem in syntax — Sara Tirmizi, Nov 22 '17 at 04:39
output is the user's Email from firebase database. In my case it is "testing8@gmail.com" — Vince Abad, Nov 22 '17 at 04:42
try this: `db.update(MyDBHandler.TABLE_USERINFO, values,"coulmnId= ' "+ userEmail + " ' ", null);` because whereClause String: the optional WHERE clause to apply when updating. Passing null will update all rows. — Sara Tirmizi, Nov 22 '17 at 04:46
I tried, and i still get that error :( Oh so does my code update all rows? I wanted to update a specific Row base from the user email — Vince Abad, Nov 22 '17 at 04:53
No It won't,. see the problem is your where clause: `UPDATE users SET fquestion=?,mheight=?,tquestion=?,asthma=?,height=?,bodystate=?,diabetes=?,weight=?,bday=?,useremail=?,squestion=?,hypertension=?,name=?,healthy=?,mweight=?,userexp=?,sex=? WHERE testing8@gmail.com` here in update method you need to properly identify the the columnId against which you are running your update for specific row .. paste this and let me know whether it works `db.update(MyDBHandler.TABLE_USERINFO, values,"coulmnId= ' "+ userEmail + " ' ", null);` Note: The columnId should be same as in your db for email — Sara Tirmizi, Nov 22 '17 at 04:55
Hi, I updated my question, and I think its from my String RecordCheck() in where I get the error. — Vince Abad, Nov 22 '17 at 05:12
For record check iteration result follow: https://stackoverflow.com/questions/10723770/whats-the-best-way-to-iterate-an-android-cursor — Sara Tirmizi, Nov 22 '17 at 05:16
My detection of user records already correct. But when updating, still, i get that error. — Vince Abad, Nov 22 '17 at 06:03
Have tried replacing your update query from `db.update(MyDBHandler.TABLE_USERINFO, values, userEmail, null);` to `db.update(MyDBHandler.TABLE_USERINFO, values, "columnId ' = "+userEmail+" ' ", null);` also remove space I have added for code readibility Note: The columnId i mentioned should be the id of column in table of db e.g email etc — Sara Tirmizi, Nov 22 '17 at 06:07
this one worked for me db.update(MyDBHandler.TABLE_USERINFO, values,"useremail= ' "+ userEmail + " ' ", null); THANK YOU SO MUCHH!!! Now its perfectly working yay. — Vince Abad, Nov 22 '17 at 06:18
Okay I am posting it as answer kindly mark that one correct :) so that others can get benefit from it :) #HappyCoding :) — Sara Tirmizi, Nov 22 '17 at 06:21

score 0 · Answer 1 · answered Nov 22 '17 at 06:16

0

You have to pass the where argument in the below format

db.update(MyDBHandler.TABLE_USERINFO,values,MyDBHandler.COLUMN_USEREMAIL+"='"+userEmail+"'", null);

answered Nov 22 '17 at 06:16

ChandraShekhar Kaushik

377
1
9

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

You need to update query from

db.update(MyDBHandler.TABLE_USERINFO, values, userEmail, null);

to:

db.update(MyDBHandler.TABLE_USERINFO, values, "columnId ='"+userEmail+"'", null);

Note: The columnId mentioned should be the id of column in table of db e.g email etc

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 22 '17 at 06:23

Sara Tirmizi

417
4
10

How to correct this updating database in Android SQL

2 Answers2