Android Question Error while trying to insert record

Makumbi

Well-Known Member
Licensed User
please help i get error wen i try to insert this comment :Tom’s book has been recovered but when i try inserting using this Toms book has been recovered is does the insertion please how can i solve this

B4X:
Starter.SQL1.ExecNonQuery("INSERT INTO SMSlist VALUES('" & quot.Get("Account") & "','" & quot.Get("Name") & "','" & CustID & "','" & sx & "','" & quot.Get("sms") & "','" & DateTime.Date(ltt) & "','" & quot.Get("atk")& "','" & quot.Get("Code")& "','" & quot.Get("Type")& "','" & 1 & "')")
                    'Log(quot.Get("Account"))
 

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

Makumbi

Well-Known Member
Licensed User
I did not see a error posted. I just see some bad Code. You should use parametrized Queries.
B4X:
Logger connected to: emulator-5554
--------- beginning of main
--------- beginning of system
** Receiver (s2) OnReceive **
Cannot start from a receiver in debug mode.
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
ExecuteHtml: SELECT DISTINCT Names,Class || Stream As CS,Amount As Balance FROM Balances
0
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Service (httputils2service) Start **
(JSONTokener)  at character 0 of [{"CustomerId":null,"Comments":"Interviews Will be Held On First Jan 2019","Dates":"\/Date(1548151785053)\/","Status":null,"Class":null,"Stream":null,"Term":null,"Years":null}]
Account: null
Events: Interviews Will be Held On First Jan 2019
Class: null
Stream: null
Years: null
Term: null
01/22/2019
22/01/2019
[{"CustomerId":null,"Comments":"Interviews Will be Held On First Jan 2019","Dates":"\/Date(1548151785053)\/","Status":null,"Class":null,"Stream":null,"Term":null,"Years":null}]
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","Stream":"A","Category":"Active","Country":"S2","Froms":"\/Date(1545426000000)\/","Tos":"\/Date(1555016400000)\/"}]
** Activity (main) Pause, UserClosed = false **
** Activity (simplechat) Create, isFirst = true **
** Activity (simplechat) Resume **
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"term one is starting on Monday","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"1","atk":1}]
Name: BUGEMBE YASIN ismail
Category: null
atk: 1
Code: 1
22/01/2019
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"term one is starting on Monday","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"1","atk":1}]
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"term one is starting on Monday","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"1","atk":1}]
Account: 05-00001
sms: term one is starting on Monday
Datesent: /Date(1548104400000)/
Status: New SMS
codes: 1
atk: 1
22/01/2019
** Activity (simplechat) Pause, UserClosed = false **
** Activity (sendsms) Create, isFirst = true **
1
** Activity (sendsms) Resume **
22/01/2019
1
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
ExecuteHtml: SELECT DISTINCT Names,Class || Stream As CS,Amount As Balance FROM Balances
0
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Service (httputils2service) Start **
(JSONTokener)  at character 0 of [{"CustomerId":null,"Comments":"Interviews Will be Held On First Jan 2019","Dates":"\/Date(1548151785053)\/","Status":null,"Class":null,"Stream":null,"Term":null,"Years":null}]
Account: null
Events: Interviews Will be Held On First Jan 2019
Class: null
Stream: null
Years: null
Term: null
01/22/2019
22/01/2019
[{"CustomerId":null,"Comments":"Interviews Will be Held On First Jan 2019","Dates":"\/Date(1548151785053)\/","Status":null,"Class":null,"Stream":null,"Term":null,"Years":null}]
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","Stream":"A","Category":"Active","Country":"S2","Froms":"\/Date(1545426000000)\/","Tos":"\/Date(1555016400000)\/"}]
** Activity (main) Pause, UserClosed = false **
** Activity (simplechat) Create, isFirst = true **
** Activity (simplechat) Resume **
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"term one is starting on Monday","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"1","atk":1}]
Account: 05-00001
sms: term one is starting on Monday
Datesent: /Date(1548104400000)/
Status: New SMS
codes: 1
atk: 1
22/01/2019
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"term one is starting on Monday","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"1","atk":1}]
Name: BUGEMBE YASIN ismail
Category: null
atk: 1
Code: 1
22/01/2019
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"term one is starting on Monday","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"1","atk":1}]
ExecuteHtml: SELECT DISTINCT Datesent,Sms as SentMessage,ID FROM Sentmessages
ExecuteHtml: SELECT DISTINCT Daterecieved,Name,Sms as RecievedMessage,ID FROM Recievedsms
ExecuteHtml: SELECT DISTINCT Datesent,Sms as SentMessage,ID FROM Sentmessages
** Activity (simplechat) Pause, UserClosed = false **
** Activity (sendsms) Create, isFirst = true **
1
** Activity (sendsms) Resume **
22/01/2019
1
** Service (starter) Destroy (ignored)**
** Activity (sendsms) Pause, UserClosed = true **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
ExecuteHtml: SELECT DISTINCT Names,Class || Stream As CS,Amount As Balance FROM Balances
0
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Service (httputils2service) Start **
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","Stream":"A","Category":"Active","Country":"S2","Froms":"\/Date(1545426000000)\/","Tos":"\/Date(1555016400000)\/"}]
(JSONTokener)  at character 0 of [{"CustomerId":null,"Comments":"Interviews Will be Held On First Jan 2019","Dates":"\/Date(1548151785053)\/","Status":null,"Class":null,"Stream":null,"Term":null,"Years":null}]
Account: null
Events: Interviews Will be Held On First Jan 2019
Class: null
Stream: null
Years: null
Term: null
01/22/2019
22/01/2019
[{"CustomerId":null,"Comments":"Interviews Will be Held On First Jan 2019","Dates":"\/Date(1548151785053)\/","Status":null,"Class":null,"Stream":null,"Term":null,"Years":null}]
** Activity (main) Pause, UserClosed = false **
** Activity (simplechat) Create, isFirst = true **
** Activity (simplechat) Resume **
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2}]
Name: BUGEMBE YASIN ismail
Category: null
atk: 2
Code: 2
22/01/2019
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2}]
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2}]
Account: 05-00001
sms: wen is term ending
Datesent: /Date(1548104400000)/
Status: New SMS
codes: 2
atk: 2
22/01/2019
** Activity (simplechat) Pause, UserClosed = false **
** Activity (sendsms) Create, isFirst = true **
1
** Activity (sendsms) Resume **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
ExecuteHtml: SELECT DISTINCT Names,Class || Stream As CS,Amount As Balance FROM Balances
0
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Service (httputils2service) Start **
ResponseError. Reason: java.net.SocketTimeoutException: connect timed out, Response:
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","Stream":"A","Category":"Active","Country":"S2","Froms":"\/Date(1545426000000)\/","Tos":"\/Date(1555016400000)\/"}]
** Activity (main) Pause, UserClosed = false **
** Activity (simplechat) Create, isFirst = true **
** Activity (simplechat) Resume **
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2}]
Account: 05-00001
sms: wen is term ending
Datesent: /Date(1548104400000)/
Status: New SMS
codes: 2
atk: 2
22/01/2019
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2}]
Name: BUGEMBE YASIN ismail
Category: null
atk: 2
Code: 2
22/01/2019
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2}]
** Activity (simplechat) Pause, UserClosed = false **
** Activity (sendsms) Create, isFirst = true **
1
** Activity (sendsms) Resume **
22/01/2019
sendsms_btnsend_click (java line: 510)
android.database.sqlite.SQLiteException: near "book": syntax error (code 1): , while compiling: INSERT INTO Sentmessages VALUES('22/01/2019','toms'book is here','18')
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    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:1677)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1608)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at de.amberhome.slidemenuexample.sendsms._btnsend_click(sendsms.java:510)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:191)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
    at android.view.View.performClick(View.java:5610)
    at android.view.View$PerformClick.run(View.java:22265)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6077)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
android.database.sqlite.SQLiteException: near "book": syntax error (code 1): , while compiling: INSERT INTO Sentmessages VALUES('22/01/2019','toms'book is here','18')
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (simplechat) Create, isFirst = true **
** Activity (simplechat) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
** Service (httputils2service) Start **
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2},{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"my lost his homework book","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"5","atk":5}]
Account: 05-00001
sms: wen is term ending
Datesent: /Date(1548104400000)/
Status: New SMS
codes: 2
atk: 2
22/01/2019
Account: 05-00001
sms: my lost his homework book
Datesent: /Date(1548104400000)/
Status: New SMS
codes: 5
atk: 5
22/01/2019
(JSONTokener)  at character 0 of [{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2},{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"my lost his homework book","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"5","atk":5}]
Name: BUGEMBE YASIN ismail
Category: null
atk: 2
Code: 2
22/01/2019
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2},{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"my lost his homework book","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"5","atk":5}]
Name: BUGEMBE YASIN ismail
Category: null
atk: 5
Code: 5
22/01/2019
[{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"wen is term ending","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"2","atk":2},{"Account":"05-00001","Name":"BUGEMBE YASIN ismail","sms":"my lost his homework book","Datesent":"\/Date(1548104400000)\/","Phone":"0782911364","Status":"New SMS","Type":"Mother","Code":"5","atk":5}]
** Activity (simplechat) Pause, UserClosed = false **
** Activity (sendsms) Create, isFirst = true **
2
** Activity (sendsms) Resume **
22/01/2019
sendsms_btnsend_click (java line: 510)
android.database.sqlite.SQLiteException: near "s": syntax error (code 1): , while compiling: INSERT INTO Sentmessages VALUES('22/01/2019','tom's book is here','18')
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    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:1677)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1608)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at de.amberhome.slidemenuexample.sendsms._btnsend_click(sendsms.java:510)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:191)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:175)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
    at android.view.View.performClick(View.java:5610)
    at android.view.View$PerformClick.run(View.java:22265)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6077)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
android.database.sqlite.SQLiteException: near "s": syntax error (code 1): , while compiling: INSERT INTO Sentmessages VALUES('22/01/2019','tom's book is here','18')
that is the error please
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Your problem is the single quote in Tom's. That is why manfred suggested parameterized query. The single quote will not become a problem if you use parameterized query. See this:
B4X:
Starter.SQL1.ExecNonQuery2("INSERT INTO SMSlist VALUES(?, ?, ?....)", Array As Object(quot.Get("Account"), quot.Get("Name"), CustID, .......))
The dots I have are for you to complete with the other items you have.
 
Upvote 0

Makumbi

Well-Known Member
Licensed User
Your problem is the single quote in Tom's. That is why manfred suggested parameterized query. The single quote will not become a problem if you use parameterized query. See this:
B4X:
Starter.SQL1.ExecNonQuery2("INSERT INTO SMSlist VALUES(?, ?, ?....)", Array As Object(quot.Get("Account"), quot.Get("Name"), CustID, .......))
The dots I have are for you to complete with the other items you have.
Thank you
 
Upvote 0
Top