Android Question [Solved] SQL Problems

cds-soft

Member
Licensed User
Well, I don't not why this project i'm building doesn't work.

So far, this project only have one table. Simple. You can see it in the project.

- What's suposed to do?
Insert 'Operario', 'Fecha' and 'Vehiculo' fields in table 'CPARTES' when you push 'Add' (if the registry already exists shows a message). And 'Count' the number of registries are in the table. All very simple, no?

When the activity starts the DataBase and the table are created (I can see it with the DDMS).

- What's not working?
The 'Add' button (to add new registry) and of course 'Count' button (to count registrys in table).
- Why?
Because 'Add' doesn't add anything.

If I edit db file with a record (in my descktop) and upload it to the device, 'Count' works, and 'Add' tell if the record alredy exists, but doesn't add new records.

Can anyone tell me what is wrong? Can be something stupid, but sorry I can't see it.

PD: sorry for my English...
 

Attachments

LucaMs

Expert
Licensed User
First "error" I have found is that if the db file does not exists you create it OR you open it.

Your code:
B4X:
    If FirstTime Then
        ' File.Delete(File.DirInternal, "persons.db") ' only for testing, removes the database
       
        'check if the database already exists
        If File.Exists(DBFileDir, DBFileName) = False Then
            'if not, initialize it
            oSQL.Initialize(DBFileDir, DBFileName, True)
            'and create it
            Dim sQuery As String
            sQuery = "CREATE TABLE CPARTES (ID INTEGER PRIMARY KEY, CodOperario TEXT, Fecha TEXT, CodVehiculo TEXT, " & _
                     "CodRemolque TEXT, " & _
                     "Estado TEXT, " & _
                     "Contador INTEGER, " & _
                     "Incidencias TEXT)"
        '             "Incidencias TEXT, "& _
        '             "PRIMARY KEY(CodOperario,Fecha,CodVehiculo))"
            oSQL.ExecNonQuery(sQuery)
        Else
            'if yes, initialize it
            oSQL.Initialize(DBFileDir, DBFileName, True)
        End If
    End If
B4X:
    If FirstTime Then
        ' File.Delete(File.DirInternal, "persons.db") ' only for testing, removes the database
       
        'check if the database already exists
        If File.Exists(DBFileDir, DBFileName) = False Then
            'if not, initialize it
            oSQL.Initialize(DBFileDir, DBFileName, True)
            'and create it
            Dim sQuery As String
            sQuery = "CREATE TABLE CPARTES (ID INTEGER PRIMARY KEY, CodOperario TEXT, Fecha TEXT, CodVehiculo TEXT, " & _
                     "CodRemolque TEXT, " & _
                     "Estado TEXT, " & _
                     "Contador INTEGER, " & _
                     "Incidencias TEXT)"
        '             "Incidencias TEXT, "& _
        '             "PRIMARY KEY(CodOperario,Fecha,CodVehiculo))"
            oSQL.ExecNonQuery(sQuery)
     End If
     ' initialize it
     oSQL.Initialize(DBFileDir, DBFileName, False)

    End If
 

LucaMs

Expert
Licensed User
You use ExecQuery2 to insert a new record instead of ExecNONQuery2 (sub pAddWork)

B4X:
    oSQL.BeginTransaction
    Try
        sQuery = "INSERT INTO CPARTES (ID, CodOperario, Fecha, CodVehiculo) VALUES (NULL, ?, ?, ?)"
        oSQL.ExecNonQuery2(sQuery,Array As String(sOperario,sFecha,sVehiculo))
    '    oSQL.ExecQuery2(sQuery,Array As String(sOperario,sFecha,sVehiculo,sRemolque,sEstado,sContador,sIncidencias))
        Log("Registry saved")
        ToastMessageShow("Registry saved", False)
        oSQL.TransactionSuccessful
    Catch
        Log("Error")
    End Try
    oSQL.EndTransaction
 
Top