android.database.sqlite.SQLiteException: near ";": syntax error (code 1):

Question

I am getting this error when I am trying to delete a record from the database. Here is the error in full

FATAL EXCEPTION: main
                                                                              Process: itp231.dba.nyp.com.bloommain, PID: 12274
                                                                              android.database.sqlite.SQLiteException: near ";": syntax error (code 1): , while compiling: DELETE FROM events WHERE id= ;
                                                                                  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
                                                                                  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
                                                                                  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
                                                                                  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.executeSql(SQLiteDatabase.java:1674)
                                                                                  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
                                                                                  at itp231.dba.nyp.com.bloommain.EventInformationPage$1.onClick(EventInformationPage.java:135)
                                                                                  at com.android.internal.app.AlertController$ButtonHandler.handleMessage(AlertController.java:163)
                                                                                  at android.os.Handler.dispatchMessage(Handler.java:102)
                                                                                  at android.os.Looper.loop(Looper.java:148)
                                                                                  at android.app.ActivityThread.main(ActivityThread.java:5417)
                                                                                  at java.lang.reflect.Method.invoke(Native Method)
                                                                                  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
                                                                                  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)

Looking at the log, it directed me to this line of codes (my deleteRecord() method -

private void deleteRecord() {
    AlertDialog.Builder alertDialogBuilder = new AlertDialog.Builder(this);
    alertDialogBuilder.setMessage("Are you sure you want delete this person?");

    alertDialogBuilder.setPositiveButton("Yes",
            new DialogInterface.OnClickListener() {
                @Override
                public void onClick(DialogInterface arg0, int arg1) {
                    String id = editTextId.getText().toString().trim();

                    String sql = "DELETE FROM events WHERE id= " + id + ";";
                    db.execSQL(sql);
                    Toast.makeText(getApplicationContext(), "Record Deleted", Toast.LENGTH_LONG).show();
                    c = db.rawQuery(SELECT_SQL,null);
                }
            });

Read your stack trace. The String id is empty, which would mean that the EditText is empty, or just has whitespace. — Mike M.
– Mike M., Commented Aug 5, 2016 at 5:35
what is the type of id in database and also when you are passing value — Aditi
– Aditi, Commented Aug 5, 2016 at 5:43
I am not sure but maybe it could also be a problem with the Instant Run functionality if you are using Android Studio. — ninjaxelite
– ninjaxelite, Commented Aug 5, 2016 at 5:51

Phantômaxx · Accepted Answer · 2016-08-05 05:43:32Z

4

1 - Your id is a blank string, therefore it can't be parsed in your SQL command.
2 - If your id field is a TEXT (???), then you need to enclose it in single quotes.
3 - For SQL commands, use execSQL() instead of rawQuery() - rawQuery() only works on... queries (SELECT)
4 - And... prepared statements (or bound parameters) are a better choice. The placeholders (?) will be replaced automatically in their positional order and the quotes won't be a problem anymore (Android will handle that for you!).

edited Aug 5, 2016 at 5:43

answered Aug 5, 2016 at 5:38

Phantômaxx

38.1k21 gold badges88 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phantômaxx Over a year ago

@IntelliJAmiya Thank you, dear!

Gabe Sechan Over a year ago

In addition to this- NEVER WRITE SQL CODE THIS WAY. You're WIDE open to an SQL injection attack. ALWAYS use bind variables. Writing queries with concatenated parameters is a horrible, horrible mistake.

Sohail Zahid · Accepted Answer · 2016-08-05 05:58:54Z

1

You can use prepared statements

SQLiteStatement stmt = db.compileStatement("DELETE FROM events WHERE id = ?");
stmt.bindString(1, id);
stmt.execute();

edited Aug 5, 2016 at 5:58

answered Aug 5, 2016 at 5:33

Sohail Zahid

8,1893 gold badges27 silver badges42 bronze badges

1 Comment

Gabe Sechan Over a year ago

Has nobody hear heard of SQL injection and realize why this code is completely flawed? If you haven't go out and study. If you have, then why aren't you teaching him the right way to do this?

Vishwa · Accepted Answer · 2016-08-06 03:29:54Z

0

try this..

 db.delete("events","id=?",new String[]{Integer.toString(id)});

in which,

first paramater -> will be table name from where the deletion of data need to de done.
Second parameter -> Selection field in table.based on which field we are going to perform the deletion
Third parameter -> specifies the value,to be compare it with second paramters field if it is matched,then the particular column will be deleted.

edited Aug 6, 2016 at 3:29

answered Aug 5, 2016 at 6:26

Vishwa

1,1121 gold badge12 silver badges23 bronze badges

Collectives™ on Stack Overflow

android.database.sqlite.SQLiteException: near ";": syntax error (code 1):

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related