Android Question SQLite error code 1 - field data contains '

[tunderin]

Hi everybody,

My code is throwing a SQLite compile error while it tries to add a record to the db because one of the data fields contains an apostrophe '

In this case, a name field contains the name O'Neil and my code throws the following error:

android.database.sqlite.SQLiteException: near "Neil": syntax error (code 1): , while compiling: INSERT INTO Claims('Endorsement4','EndorsDesc5','Agent_Phone_No', ...shortened for brevity
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1113)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:690)
    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:1788)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1719)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:67)
    at com.bcc.mobibcd.emailservices._attachmentstodb(emailservices.java:284)
    at com.bcc.mobibcd.emailservices._pop_downloadcompleted(emailservices.java:1015)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:174)
    at anywheresoftware.b4a.BA$3.run(BA.java:319)
    at android.os.Handler.handleCallback(Handler.java:733)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:157)
    at android.app.ActivityThread.main(ActivityThread.java:5356)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:515)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1265)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1081)
    at dalvik.system.NativeStart.main(Native Method)

The portion of the values section involving this name looks like this:

,'Dowling & O'Neil Insurance Agency',

I need to preserve the name as it is, so any suggestions as to how I would fix this problem would be appreciated.

 

[NJDude]

You have to "escape" the single quotes.

 

[tunderin]

You have to "escape" the single quotes.

Do you mean within the name or around the field?

 

[NJDude]

Within, for example, let's say that you have that name on a variable:

Name = "Dowling & O'Neil Insurance Agency"

So you do this:

Name = Name.Replace("'", "''")

After that, you add Name to the DB.

 

[Mahares]

if you want to keep the apostrophe in the name and not remove it you can do something like this example:

txt="INSERT INTO Claims  VALUES(?,?,?)"
    SQL1.ExecNonQuery2(txt, Array As String("Dowling & O'Neil Insurance Agency","76667455","New York"))
txt="INSERT INTO Claims  VALUES(?,?,?)"
    SQL1.ExecNonQuery2(txt, Array As String("Norah O'Donnel","551221176","Belfast"))

 

[tunderin]

Thanks NJDude & Mahares,

I'll experiment with what you've provided - I think that I can make this work for me.