Android Question ¿How to solve "Missing Column" Problem?

[Jausa]

Hello.
I'm developing an app that is intended to hide a button if the user is not an administrator. But it throws this exception:

Error occurred on line: 109 (main)
android.database.sqlite.SQLiteException: no such column: isadmin: , while compiling: SELECT isadmin FROM users WHERE login = ?

This is the code:

Sub CheckPassword(Password As String, Login As String)
    Dim Checkme As Boolean = False
    oCursor = oSql.ExecQuery2("SELECT password FROM users WHERE login = ?", Array As String(Login))
    If oCursor.RowCount <> 0 Then
        oCursor.Position = 0
        Dim Returnme As String = oCursor.GetString2(0)
        If Password = Retorno Then
            Checkme = True
            Loggeduser = Login
            oCursor = oSql.ExecQuery2("SELECT isadmin FROM users WHERE login = ?", Array As String(Login))
            oCursor.Position = 0
            Dim NoBool As Short = oCursor.GetInt2(0)
            If NoBool = 0 Then
                Isanadmin = 0
            Else
                Isanadmin = 1
            End If
        Else
            Checkme = False
        End If
    Else
        Checkme = False
    End If
    Return Checkme
End Sub

This is the data stored on the database:

[isadmin] [name] [login] [password] [lastlogin]
1 Default admin ****** 12/06/2014
0 Default exec ****** 12/06/2014

The Schema:
CREATE TABLE users (isadmin NUMERIC, name TEXT, login TEXT, password TEXT, lastlogin TEXT)

 

[Erel]

Can you post the code that creates the database?

My guess is that you are not opening the correct database file.

 

[klaus]

You'd better use this to define the database:

CREATE TABLE users (isadmin INTEGER, name TEXT, login TEXT, password TEXT, lastlogin TEXT)

The data types in SQLite are NULL, INTEGER, REAL, TEXT and BLOB.
Look here and here.

 

[Jausa]

@Erel:
I'm actually using SQLite Database Browser.

This is the SQL generated by the program:

BEGIN TRANSACTION;
CREATE TABLE order (...)
CREATE TABLE parameters (...)
INSERT INTO parameters VALUES(...);
CREATE TABLE test (...);
CREATE TABLE users (isadmin NUMERIC, name TEXT, login TEXT, password TEXT, lastlogin TEXT);
INSERT INTO users VALUES(1,'Default','admin','******','12/06/2014');
INSERT INTO users VALUES(0,'Default',' exec ','******','16/06/2014');
COMMIT;
@klaus:

I'm actually using the data types provided by the SQLite Database Browser, i'm going to try to switch the NUMERIC fields to INTEGER.

 

[Jausa]

Partially solved.

I don't know what was happening.
I had to rebuild the database and switch the NUMERIC fields to INTEGER (Using replace on Notepad, lol)
And then execute the query.

This problem is rare because i tried to rebuild the database several times and also i tried to wipe user data from the Device.
But this time i renamed the database and also switched the NUMERIC fields to INTEGER.

Sub Activity_Create(FirstTime As Boolean)
    If File.ExternalWritable Then
        Route = File.DirDefaultExternal
    Else
        Route = File.DirInternal
    End If
    If File.Exists(Route,DBName) = False
        File.Copy(File.DirAssets, DBName, Route, DBName)
    End If
    Activity.LoadLayout("Main")
    Clock.Initialize("Clock", 1000)
    If FirstTime = True Then
        oSql.Initialize(Route, DBName, False)
    Else
        oSql.Close
        oSql.Initialize(Route, DBName, False)
    End If
End Sub

 

[Jausa]

I don't know what is happening. It seems like the Database is not being updated when i modify it....
¿How to solve it?

 

[Erel]

Note that you can use DBUtils.CopyDBFromAssets to copy the file.

Why do you close the database and then initialize it again? It will not cause any issue however it is not needed.

Are you testing it on Android 2.2?

 

[Jausa]

Yeah, i'm testing it on Android 2.1
And i don't know, i thought that restarting the database would help me solving the problem.

 

[Erel]

There is a bug in Android 2.2 that causes the files under File.DirDefaultExternal to be deleted when the app is reinstalled (on some devices). I don't know if this is the cause here however it is worth testing it with a different folder.

 

[Jausa]

Okay, i'm going to test it on the internal dir, Thank you.