How do I fix the syntax error on an INSERT INTO command?

Question

Inside an onClick() event, I am trying to increment a counter value by 1 in my CARDNUM column within a SQLite database table (TOTALCOUNT) using the INSERT INTO command.

The error occurs when trying to run:

 db.execSQL(INCREMENT_COUNT2);

Android Studio gives the following error message

"SQLiteException: near "cardnum": syntax error (code 1): , while compiling: INSERT INTO totalcount(cardnum) VALUES cardnum+1"

What am I missing here?
Is there a better way to achieve the increment upon INSERTion?

DatabaseHelper.java file:

// Increment the counter in the TOTALCOUNT Table in the database.
public void insertIntoTableTOTALCOUNT() {

    // Get a reference to a writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    db.beginTransaction();

    db.execSQL(INCREMENT_COUNT1);
    db.execSQL(INCREMENT_COUNT2);

    db.setTransactionSuccessful();
    db.endTransaction();

    if(db.isOpen())
        db.close();
    }

// Set the counter to a default value of zero.
public static final String INCREMENT_COUNT1 = " INSERT OR IGNORE INTO " + TABLE_NAME_TOTALCOUNT +
    "(" + COLUMN_NAME_CARDNUM+")" + " VALUES (0)";

// Increment the Cardnum count by +1 for each onClick.
public static final String INCREMENT_COUNT2 = "INSERT INTO " + TABLE_NAME_TOTALCOUNT + "(" + COLUMN_NAME_CARDNUM+") VALUES " + COLUMN_NAME_CARDNUM +"+1";

score 2 · Answer 1 · answered Jun 16 '16 at 07:52

2

This is the correct SQL command:

public static final String INCREMENT_COUNT2 = "INSERT INTO " + TABLE_NAME_TOTALCOUNT + "(" + COLUMN_NAME_CARDNUM + ") VALUES (" + (COLUMN_NAME_CARDNUM + 1) + ")";

But using bound parameters would be a better choice.

answered Jun 16 '16 at 07:52

I will try that. I haven't ever used bound parameters. Is that to minimize SQL injection? Can you point to any good links that will help educate me on the advantages of using them? – AJW Jun 16 '16 at 16:06
1

Yes, but not only. It also voids you the need to enclose strings in quotes or to double apostrophes. For reference: http://stackoverflow.com/questions/9857073/queries-with-prepared-statements-in-android – Jun 16 '16 at 21:26

score 1 · Answer 2 · answered Jun 16 '16 at 07:10

1

If I understood your question correctly, I think you want to UPDATE the value of COLUMN_NAME_CARDNUM with +1 and not INSERT a new row. If that is correct, you statement should be

UPDATE TABLE_NAME_TOTALCOUNT SET COLUMN_NAME_CARDNUM=COLUMN_NAME_CARDNUM+1 WHERE <filter criteria>

If you indeed want to INSERT a new row with COLUMN_NAME_CARDNUM+1, then you have to fetch the MAX of COLUMN_NAME_CARDNUM from DB and then increment it and insert

SELECT MAX(COLUMN_NAME_CARDNUM) FROM TABLE_NAME_TOTALCOUNT

Then increment it by 1 and insert.

answered Jun 16 '16 at 07:10

Supratim Haldar

2,376
3
16
26

I am looking to insert a new row with the +1. So I want to take the value of the last previously inserted row and add one. So do you recommend I use your SELECT MAX example? – AJW Jun 16 '16 at 07:14
Yes correct. I don't the exact logic of COLUMN_NAME_CARDNUM, but if it is supposed to the +1 than the last inserted row (and thus unique), then you should select MAX first and then increment that value and then insert. – Supratim Haldar Jun 16 '16 at 07:41
Ok I will give that a try. – AJW Jun 16 '16 at 07:42
Also, if CARDNUM is supposed to be unique, you can consider making it a PK with auto-increment - CREATE TABLE TABLE_NAME_TOTALCOUNT( COLUMN_NAME_CARDNUM INTEGER PRIMARY KEY AUTOINCREMENT); – Supratim Haldar Jun 16 '16 at 07:44

score 1 · Answer 3 · answered Jun 16 '16 at 07:15

Make a Method pass the Cardnum value by adding or incrementing 1. Then run the insert Query. There are two types of SQL query for Insertion.

Raw Query i.e String insertdata= "INSERT INTO " + TABLE_NAME_TOTALCOUNT + "(" + COLUMN_NAME_CARDNUM+") VALUES " + COLUMN_NAME_CARDNUM +"+1";
Another is by using ContentValues:

public void insertDatatoDatabase(String data1,String data2){ ContentValues values = new ContentValues(); values.put(COLUMN1, data1); values.put(COLUMN2, data2);db1.insert(TABLE_DETAIL, null, values); }

You are adding +1 in String " " where it is a Integer. Try adding them in bracket or Add the number before inserting and send it from onclick only.

score 0 · Answer 4 · answered Jun 16 '16 at 07:10

0

 public static final String INCREMENT_COUNT2 = "INSERT INTO " + TABLE_NAME_TOTALCOUNT + "(" + COLUMN_NAME_CARDNUM+") VALUES (" + (COLUMN_NAME_CARDNUM=COLUMN_NAME_CARDNUM+1)+")";

//use this may be it will be worked

answered Jun 16 '16 at 07:10

Madhav Gor

211
2
12

Error message: "Cannot assign a value to final variable 'COLUMN_NAME_CARDNUM'. – AJW Jun 16 '16 at 07:26
"Cannot assign a value to final variable 'COLUMN_NAME_CARDNUM'. – AJW Jun 16 '16 at 07:35

score 0 · Answer 5 · answered Jun 16 '16 at 07:11

0

shouldn't it be

INCREMENT_COUNT2 = "INSERT INTO " + TABLE_NAME_TOTALCOUNT + "(" + COLUMN_NAME_CARDNUM+") VALUES (" + COLUMN_NAME_CARDNUM +"+1)"

answered Jun 16 '16 at 07:11

Tushar Saha

1,978
24
29

app crashes with error message: "no such column: cardnum (code 1): , while compiling: INSERT INTO totalcount(cardnum) VALUES (cardnum+1)" – AJW Jun 16 '16 at 07:23

How do I fix the syntax error on an INSERT INTO command?

5 Answers5