Android Question [Solved] SQL Problems

[cds-soft]

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...

 

[LucaMs]

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

Your code:

    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

    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]

forget it. you initialize it in the Resume, ok

 

[LucaMs]

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

    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

 

[cds-soft]

Sometimes can't see the forest for the trees

A millions thanks for your help but specially for your time.

 

[LucaMs]

Who has never hit a tree?