-1

I'm getting the following error when I am trying to update my table using a bigger number.

SQLiteLog: (1) variable number must be between ?1 and ?999 W/System.err: android.database.sqlite.SQLiteException: variable number must be between ?1 and ?999 (code 1): , while compiling: UPDATE LoginTable SET image=?,alternate_contact=?,alternate_email=? WHERE consumer_id=?74123

The code is:-

public static void updateProfileInfo(Context context, Consumer userModel, String consumerno) {
        SQLiteDatabase db = DatabaseProvider.dbHelper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put(LoginTable.Cols.ALTERNATE_EMAIL_ID, userModel.alternate_email != null ? userModel.alternate_email : "");
        values.put(LoginTable.Cols.PROFILE_IMAGE, userModel.profileImage != null ? userModel.profileImage : "");
        values.put(LoginTable.Cols.ALTERNATE_CONTACT_NO, userModel.alternateContact != null ? userModel.alternateContact : "");
        db.update(LoginTable.TABLE_NAME, values, LoginTable.Cols.CONSUMER_ID + "=?" + userModel.consumer_no, null);
        if (db.isOpen()) {
            db.close();
        }
}

where the consumer number is declared as VARCHAR

consumer number might be a number of length 1 to 999999999999

should I change the type to something else??

forpas
  • 160,666
  • 10
  • 38
  • 76
rishi lara
  • 3
  • 3
  • Please format your code correctly, using the code blocks. Also, the error is telling you exactly what's wrong. You have some restriction, meaning the maximum number (I'm assuming it means the ID) must be greater than 1 AND less than (or equal to) 999. Please post some more example code from your project, e.g. how the database/table is created – SimonC Apr 05 '19 at 08:28
  • `?74123` is the problem, and the reason for it is in the code that produced this SQL. Please post that code. – laalto Apr 05 '19 at 08:37
  • See https://www.sqlite.org/lang_expr.html#varparam for details on what `?NNN` means, btw - explicitly numbered parameters. – Shawn Apr 05 '19 at 09:18

1 Answers1

0

This line:

db.update(LoginTable.TABLE_NAME, values, LoginTable.Cols.CONSUMER_ID + "=?" + userModel.consumer_no, null);

is incorrect.
You have 2 choices.
The 1st is to concatenate the value passed to LoginTable.Cols.CONSUMER_ID like this:

db.update(LoginTable.TABLE_NAME, values, LoginTable.Cols.CONSUMER_ID + " = '" + userModel.consumer_no + "'", null);

if userModel.consumer_no is a string, or:

db.update(LoginTable.TABLE_NAME, values, LoginTable.Cols.CONSUMER_ID + " = " + userModel.consumer_no, null);

if userModel.consumer_no is an integer value.
The 2nd choice is better and safer:

db.update(LoginTable.TABLE_NAME, values, LoginTable.Cols.CONSUMER_ID + " = ?", new String[] { String.valueOf(userModel.consumer_no) });

You can omit String.valueOf() if userModel.consumer_no is a string.

The error in your code is that you mixed somehow the above 2 ways of passing the argument userModel.consumer_no to the update() method.

forpas
  • 160,666
  • 10
  • 38
  • 76