Android Question It seems that my db in sqlite has not been created or I can't add any record...

[Luis Felipe]

Hello everybody.

My main problem right now is that my app crash when the app read that line with the Select:

Sub ContarEncuestas
    Dim SQLResult As Object
    SQLResult = SQL1.ExecQuerySingleResult("SELECT COUNT(*) FROM viajeros WHERE ca_enviado = 'N'")
    Log(SQLResult)
    If SQLResult = Null Then                        'si hay encuestas sin enviar
        LblNumEnc.Text = "0"                            'set the row count variable
    Else
        LblNumEnc.Text = SQLResult                                       
    End If
End Sub

Before that I do a test to know if there is a DB and if not create it:

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    If FirstTime Then
        ' File.Delete(File.DirInternal, "latbus.db") ' only for testing, removes the database
        'check if the database already exists
        If File.Exists(File.DirInternal, "latbus.db") = False Then
            'if not, initialize it
            SQL1.Initialize(File.DirInternal, "latbus.db", True)
            'and create it
            CreateDataBase
        End If
    End If
    Activity.LoadLayout("MenuPrincipal")
    'scvTest.Panel.LoadLayout("MenuPrincipal")
    ContarEncuestas
End Sub

Here the code to create the table after that:

Sub CreateDataBase
    Dim Query As String
    Query = "CREATE TABLE viajeros (ca_id TEXT Not Null UNIQUE, ca_empresa TEXT Not Null, ca_ntrabaj TEXT Not Null, "
    Query = Query & "ca_fecha TEXT Not Null, ca_hora TEXT Not Null, ca_sexo TEXT Not Null, ca_edad TEXT Not Null, "
    Query = Query & "ca_perfil TEXT Not Null, ca_postal TEXT Not Null, ca_nacionalidad TEXT Not Null, "
    Query = Query & "ca_veces TEXT Not Null, ca_tipousu TEXT Not Null, ca_linea TEXT Not Null, ca_usobus TEXT Not Null, "
    Query = Query & "ca_bus TEXT, ca_liencu TEXT, ca_puntimp TEXT Not Null, ca_punserv TEXT Not Null, "
    Query = Query & "ca_frecimp TEXT Not Null, ca_frecsuf TEXT Not Null, ca_buscon TEXT Not Null, ca_busade TEXT Not Null, "
    Query = Query & "ca_conpre TEXT Not Null, ca_conade TEXT Not Null, ca_infemp TEXT Not Null, ca_infade TEXT Not Null, "
    Query = Query & "ca_valglo TEXT Not Null, ca_enviado TEXT Not Null, PRIMARY KEY(ca_id));"
    SQL1.ExecNonQuery(Query)
End Sub

This happen in the first activity.
In the second I try to add a record like that:

Sub AddEntry
    Dim Query As String
        'add the entry
        'a NULL for the ID column increments the primary key automatically by one
        'we use ExecNonQuery2 because it's easier, we don't need to take care of the data types
        Query = "INSERT INTO viajeros VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        SQL1.ExecNonQuery2(Query, Array As String(ID_BD_Viajeros, dbEmpInspector, dbCodigoInspector, Fecha, Hora, Sexo, Edad, Perfil, dbCPostal, dbNacionalidad, edtFreqUso.Text, BonoBillete, dbLineaHabitual, Motivo, edtCodigoBus.Text, edtCodigoLinea.Text, ImportPuntual, ServicioPuntual, ImportFrec, FrecSufi, CondiAuto, AutoAdec, ComportCond, CondAten, ValorInform, InformAdec, ValGlobal, Enviado))
        Log(SQL1)       
        Msgbox("Encuesta grabada", "Enhorabuena")    ' confirmation for the user
        StartActivity("Main")
End Sub

I test all that in a tablet (Huawei Mediapad M1 with an 8" screen 1280x800 pixels) with Android 4.2.2 without root and without (external) sdcard.

What am I missing ?
I'm stucked right now. Any help apreciated.
Thank you...

Lou

 

[klaus]

When you ask questions you must also give the error message !
What error do you get ?
I was about updating my answer in your previous thread when I saw this one.
The first time you the program when there is no database the program runs OK.
The second time when the database does exist, you don't initialize the database !
I suppose that you copied the initialization of the database from the SQLiteLit1 project, but you didn't copy all you need.
You missed a part of the code in Activity_Resume (did it right in EncuestaViajeros).
Add in Activity_Resume:

Sub Activity_Resume
   ' If the database is not initialized we initialize it
   If SQL1.IsInitialized = False Then
     SQL1.Initialize(File.DirInternal, "persons.db", True)
   End If
   ContarEncuestas
End Sub

If you modify the database in another activity I suggest you to move ContarEncuestas from Activity_Create to Activity_Resume to make sure that the display is updated.

You must initialize the database only one time in Main !

I changed in your project:
- The SQL1 initialization
- The Package name, you kept the name of the ScrollViewBigPanel project. You can modify it to your needs.
- The program name.
- The top of the code in Main with the more recent regions layout.
- The Application label from LATBus Menu Principal to LATBus.
- In the Manifest I changed <uses-sdk android:minSdkVersion="4" /> to <uses-sdk android:minSdkVersion="4" android:targetSdkVersion="14"/> to target more recent Android versions. If you don't like it you can change it back.

Attached a modified version.

The latbus logo doesn't look OK in the main screen, probably a Gravity problem.

 

[mc73]

You are not initializing the database after creation. As an example, you could write

If File.Exists(File.DirInternal, "latbus.db") = False Then
       'if not, initialize it
       SQL1.Initialize(File.DirInternal, "latbus.db", True)
       'and create it
       CreateDataBase
     else
        SQL1.Initialize(File.DirInternal, "latbus.db", True)
end if

Another way, is to always initialize the db, without checking its existence, and then alter your createDatabase sub, using 'create table if not exists'.

 

[Luis Felipe]

When you ask questions you must also give the error message !
What error do you get ?
I was about updating my answer in your previous thread when I saw this one.
The first time you the program when there is no database the program runs OK.
The second time when the database does exist, you don't initialize the database !
I suppose that you copied the initialization of the database from the SQLiteLit1 project, but you didn't copy all you need.
You missed a part of the code in Activity_Resume (did it right in EncuestaViajeros).
Add in Activity_Resume:

Sub Activity_Resume
   ' If the database is not initialized we initialize it
   If SQL1.IsInitialized = False Then
     SQL1.Initialize(File.DirInternal, "persons.db", True)
   End If
   ContarEncuestas
End Sub

If you modify the database in another activity I suggest you to move ContarEncuestas from Activity_Create to Activity_Resume to make sure that the display is updated.

You must initialize the database only one time in Main !

I changed in your project:
- The SQL1 initialization
- The Package name, you kept the name of the ScrollViewBigPanel project. You can modify it to your needs.
- The program name.
- The top of the code in Main with the more recent regions layout.
- The Application label from LATBus Menu Principal to LATBus.
- In the Manifest I changed <uses-sdk android:minSdkVersion="4" /> to <uses-sdk android:minSdkVersion="4" android:targetSdkVersion="14"/> to target more recent Android versions. If you don't like it you can change it back.

Attached a modified version.

The latbus logo doesn't look OK in the main screen, probably a Gravity problem.

Thank you Klaus. The app does not launch.
No error message...

 

[Luis Felipe]

Thank you Klaus. The app does not launch.
No error message...

Klaus it seems to be working now. I changed that targetSdkVersion="14" to 19 because the tablet is working under Android 4.2.2.
The only problem left is that the counter of my db is now showing 0 when before I got 4 : is it normal ? we're using the same db as before, no ?
Thank you so much for your time Klaus...

 

[klaus]

I don't have a database with data, I haven't entered any.
It takes its time to fill a few datasets.
I now tried it once and noticed:
- You are checking all missing data once going through all messageboxes. If there are many, at the end, you don't even remember which ones are missing.
- If there are missing data the program crashes in line 212 because edtEmpInspector.Text is empty!
dbEmpInspector = edtEmpInspector.Text.SubString2(0,1)
You need to exit the routine before this line when there are missing data.

 

[Luis Felipe]

I don't have a database with data, I haven't entered any.
It takes its time to fill a few datasets.
I now tried it once and noticed:
- You are checking all missing data once going through all messageboxes. If there are many, at the end, you don't even remember which ones are missing.
- If there are missing data the program crashes in line 212 because edtEmpInspector.Text is empty!
dbEmpInspector = edtEmpInspector.Text.SubString2(0,1)
You need to exit the routine before this line when there are missing data.

You're right Klaus : I did like that because I did not know how to do in another way...
The fact is that every field has to have a value, that's why I'm checking every field.
How can I exit the routine ?
That's true that a lot o MsgBox are shown on screen but at the end, the AddEntry sub(routine) is called anyway : how can I change that in order to call AddEntry only if all the checks are Ok ?
Thank you again...

Lou

 

[Luis Felipe]

You're right Klaus : I did like that because I did not know how to do in another way...
The fact is that every field has to have a value, that's why I'm checking every field.
How can I exit the routine ?
That's true that a lot o MsgBox are shown on screen but at the end, the AddEntry sub(routine) is called anyway : how can I change that in order to call AddEntry only if all the checks are Ok ?
Thank you again...

Lou

I solve one problem using Return in the If Then statement.
But there is one left : when I clicked on the "Validar" button in order to record all the form values into the db, I go back to the first screen.
And then on this first screen when I click to go to the second in order to fulfill a new survey, then I can see the last survey completed with all the radiobuttons selected !
How can I solve that ?
Thank you

 

[klaus]

Try to add Activity.Finish in Activity_Pause.

 

[Luis Felipe]

Try to add Activity.Finish in Activity_Pause.

Merci Klaus : it seems to be working...
I tried to put it after the StartActivity but the app exits.

 

[klaus]

No it must be in Activity_Pause of the EncuestaViajeros Activity!

 

[Luis Felipe]

No it must be in Activity_Pause of the EncuestaViajeros Activity!

It solved the problem.
But now my boss wants a different workflow :-(.
When I click the "validate" button in order to record the survey in the db, instead of going back to the first screen (The Menu/home page) he wants that the same screen of the survey appears again but empty that time : the same as a Reload if it were a web page.
How can I do that ?
Merci Klaus...

 

[barx]

Just thinking out loud. Shouldn't SQLResult be a cursor object?

 

[klaus]

How can I do that ?
Merci Klaus..

You must first think yourself on how it could be done, try it and then ask for help if you have trouble. That's the only good way to get experience. We won't write the program for you.

Just thinking out loud. Shouldn't SQLResult be a cursor object?

Sure ! I missed that one.