Android Question Inserting Json Record into Table

Makumbi

Well-Known Member
Licensed User
Please help iam trying to insert a record into my table but iam getting this error
B4X:
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
ExecuteHtml: SELECT Names,Class As CS,Stream As Str,Amount FROM student
** Activity (main) Pause, UserClosed = false **
** Activity (linkupdates) Create, isFirst = true **
** Activity (linkupdates) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
(JSONTokener)  at character 0 of [{"CustomerId":"05-00001","Name":"Makumbi Sulaiman","Stream":"N","Amount":45000,"Dates":"\/Date(1542834000000)\/","Term":"Term1     ","Years":"2018","Class":"S2"},{"CustomerId":"05-00002","Name":"Nkabirwa Rayan","Stream":"S","Amount":5000,"Dates":"\/Date(1542574800000)\/","Term":"Term1     ","Years":"2018","Class":"P4"}]
Customerid: null
Name: null
Class: S2
Stream: null
Amount: 45000
Dates: /Date(1542834000000)/
Years: 2018
Term: Term1     
Customerid: null
Name: null
Class: P4
Stream: null
Amount: 5000
Dates: /Date(1542574800000)/
Years: 2018
Term: Term1     
Error occurred on line: 93 (Linkupdates)
android.database.sqlite.SQLiteException: near "2018": syntax error (code 1): , while compiling: INSERT INTO student VALUES('null','null','P4','S','5000','/Date(1542574800000)/','1'','2018','Term1     ')
    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 java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:778)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:358)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:22)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:245)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:137)
    at anywheresoftware.b4a.BA$2.run(BA.java:365)
    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)

Below is my code for inserting record
B4X:
If File.Exists(File.DirInternal,"studentdbt.sql") = False Then
        File.Copy(File.DirAssets,"studentdbt.sql",File.DirInternal,"studentdbt.sql")
    End If
    
    If SQL1.IsInitialized = False Then
        SQL1.Initialize(File.DirInternal, "studentdbt.sql", False)
    End If
    cursor1 = SQL1.ExecQuery("SELECT Phone FROM Register")
    If cursor1.RowCount > 0 Then
        For i = 0 To cursor1.RowCount - 1
            cursor1.Position = i
            Dim phone As String
            phone = cursor1.Getstring("Phone")
        Next
    End If
    Dim CustID As String = phone' Customer ID
    Dim j As HttpJob
    j.Initialize("", Me)
    'j.Download("http://192.168.1.239/WebServicesula/Service.asmx/Get")
    j.Download("http://192.168.1.239/Generic_Handler_JSON/HandlerVBPay.ashx?customerid=" & CustID)
    'j.Download("http://192.168.1.239/Generic_Handler_JSON/HandlerVB.ashx")
    'j.PostString("http://192.168.1.239/Webandroid//VB.aspx?customerid=" & CustID)
    
    j.GetRequest.Timeout = 10000 ' 10 seconds
    Wait For (j) JobDone(j As HttpJob)
    If j.Success Then ' if job is success (http status code 200)
        Dim RetVal As String
        RetVal = j.GetString
        
        Dim jp As JSONParser
        jp.Initialize(j.GetString)
        Log(jp) ' will pr
        Dim quotes As List = jp.NextArray
        For Each quot As Map In quotes
            Log("Customerid: " & quot.Get("Account"))
            Log("Name: " & quot.Get("Names"))
            Log("Class: " & quot.Get("Class"))
            Log("Stream: " & quot.Get("stream"))
            Log("Amount: " & quot.Get("Amount"))
            Log("Dates: " & quot.Get("Dates"))
            Log("Years: " & quot.Get("Years"))
            Log("Term: " & quot.Get("Term"))
        Next
    End If
    'Grab the last ID number which is the highest number
    cursor1 = SQL1.ExecQuery("SELECT id FROM student")
    'cursor1 = SQL1.ExecQuery("SELECT Phone FROM Register")
    If cursor1.RowCount > 0 Then
        For i = 0 To cursor1.RowCount - 1
            cursor1.Position = i
                
            Dim NewID As Int
            NewID = cursor1.GetInt("id")
        Next

    End If
    NewID = NewID +1 ' add 1 to the ID number to make a new ID field
    SQL1.ExecNonQuery("INSERT INTO student VALUES('" & quot.Get("Account") & "','" & quot.Get("Names") & "','" & quot.Get("Class") & "','" & quot.Get("Stream") & "','" & quot.Get("Amount") & "','" &  quot.Get("Dates") & "','" & NewID & "'','" & quot.Get("Years") & "','" & quot.Get("Term") & "')")
    
    Log(RetVal) ' will print in log value returned from the server
    j.Release
 

nibbo

Active Member
Licensed User
Longtime User
You have a two single quotes after NewID.
Also, not sure what will happen with the null, putting that in quotes will probably store null as a string in the table column rather than a null value (feel free to correct me, I have never tried it in SQLite...)
 
Upvote 0
Top