Yash Joshi Yash Joshi - 1 year ago 308
Android Question

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

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

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?");

new DialogInterface.OnClickListener() {
public void onClick(DialogInterface arg0, int arg1) {
String id = editTextId.getText().toString().trim();

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

Answer Source

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!).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download