Android Question How insert row on db with table with many fields

amorosik

Expert
Licensed User
I'm trying to write from Android to a SqLite db
Following the advice of this post I used the following lines:

INSERT INTO - MANY FIELD PROBLEM:
Query = "INSERT INTO DOCUMENTIRIGHE VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Starter.oSQL.execNonQuery2(Query, Array As String(0,"","","","",Null,Null,"","","","",Null,Null,"","","","",0,0,modVaGlob.cliente_codice_attuale,0,0,0,0,0,0,modVaGlob.agente_codice_attuale,0,0,0,0,0,Null,Null,"C","A",Null,0,"",0,num_riga))

But when the fields to be inserted are several dozen or or hundreds, it is easy to confuse some values and write on nearby fields
The question is: how to understand if the value entered in the value map will exactly write on the desired field?
Is there an IntelliSense-like system or other tools that displays the field corresponding to a value in the value map?
 

Mahares

Expert
Licensed User
Longtime User
There are 3 ways I can think of:
1. Use DBUtils (need the library) to insert a list of maps
2. Use string builder to builf the string query with all the ? marks
3. Use String literals to line up the ? marks in the string query.

Using DBUtils:
B4X:
Dim ListOfMaps As List
    ListOfMaps.Initialize
        Dim m As Map
        m.Initialize
        m.Put("f1", 0)  'use the correct name of your column instead of f1
        m.Put("f2", 0)
        .
        m.Put("f16", "")
        m.Put("f26", null)
        m.Put("f27", num_riga)
        ListOfMaps.Add(m)
    DBUtils.InsertMaps(Starter.oSQL, "DOCUMENTIRIGHE", ListOfMaps)

You can build the query string with stringBuilder:
B4X:
Dim sb As StringBuilder
        sb.Initialize
        sb.Append("INSERT INTO DOCUMENTIRIGHE VALUES(")
        For i = 0 To 27
            sb=sb.Append("?,")
        Next        
        sb.Remove(sb.Length - 1, sb.Length)
        sb.Append(")")
        Dim Query As String = sb.ToString
        Starter.oSQL.execNonQuery2(Query, Array As..........

With string Literals something like this for the query string:
B4X:
Dim Query As String = $"INSERT INTO DOCUMENTIRIGHE VALUES (
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
    ?, ?, ?, ?, ?, ?, ?, ?
    )"$
 
Upvote 1
Top