Why is android saying no column when i have a column?

Question

I have this database code:

 public static final String KEY_ROWID = "_id";
public static final String KEY_FNAME = "fname";
public static final String KEY_LNAME = "lname";
public static final String KEY_MNAME = "mname";
public static final String KEY_CONTACTNUM = "contactNum";
public static final String KEY_LICENSE = "license";
public static final String KEY_USER = "username";
public static final String KEY_PASS = "password";

private static final String DATABASE_NAME = "sampleForFinals";
private static final String DATABASE_TABLE = "drivers";
private static final int DATABASE_VERSION = 1;

private DBHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

private static class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(" CREATE TABLE " + DATABASE_TABLE + "(" +
                KEY_ROWID + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_FNAME + "TEXT NOT NULL, " +
                KEY_LNAME + "TEXT NOT NULL, " +
                KEY_MNAME + "TEXT NOT NULL, " +
                KEY_CONTACTNUM + "TEXT NOT NULL, " +
                KEY_LICENSE + "TEXT NOT NULL, " +
                KEY_USER + "TEXT NOT NULL, " +
                KEY_PASS + "TEXT NOT NULL);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXIST " + DATABASE_NAME);
        onCreate(db);
    }
}

public DatabaseHelper(Context c) {
    ourContext = c;
}

public DatabaseHelper open() {
    ourHelper = new DBHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;

}

public void close() {
    ourHelper.close();
}

public long savedata(String fname, String lname, String mname, String contactNum, String license, String user, String pass) {
    ContentValues cv = new ContentValues();
    cv.put(KEY_FNAME, fname);
    cv.put(KEY_LNAME, lname);
    cv.put(KEY_MNAME, mname);
    cv.put(KEY_CONTACTNUM, contactNum);
    cv.put(KEY_LICENSE, license);
    cv.put(KEY_USER, user);
    cv.put(KEY_PASS, pass);
    return ourDatabase.insert(DATABASE_TABLE, null, cv);
}

Here is my register.java

 register.setOnClickListener(new View.OnClickListener()
    {
        @Override
        public void onClick(View v)
        {
                String fname = firstname.getText().toString();
                String lname = lastname.getText().toString();
                String mname = middlename.getText().toString();
                String contact = contactNum.getText().toString();
                String plate = license.getText().toString();
                String user = username.getText().toString();
                String pass = password.getText().toString();

                DatabaseHelper save = new DatabaseHelper(Register.this);
                save.open();
                save.savedata(fname,lname, mname, contact, plate, user, pass);

                firstname.setText("");
                lastname.setText("");
                middlename.setText("");
                contactNum.setText("");
                license.setText("");
                username.setText("");
                password.setText("");
        }
    });

Basically, what the error says is:

table drivers has no column named mname
inserting mname=a username=a lname=a license=a password=a contactNum=4 fname=a
                                                                               android.database.sqlite.SQLiteException: table drivers has no column named mname (code 1): , while compiling: INSERT INTO drivers(mname,username,lname,license,password,contactNum,fname) VALUES (?,?,?,?,?,?,?)

So, the database i use is from FITACADEMY. It is an sqlite. I can see the database tables and the columns and yes mname column does exist in FITACADEMY. I can see it. However, the error says it doesnt have the column 'mname'. I tried to delete all the codes related to 'mname' and it proceeds to 'user' not having a column. So, i removed all the codes related to 'user', then it has an error again of not having the column 'fname'. Note: This is inserting data into the database.

What is wrong with my code? Ive been finding the error for 3 hours now. I still cant figure it out.

score 2 · Answer 1 · answered Mar 13 '17 at 16:55

2

You need to have whitespace in your CREATE TABLE SQL between column names and their types. For example:

KEY_MNAME + "TEXT NOT NULL, " +

needs to be

KEY_MNAME + " TEXT NOT NULL, " +

Same problem with all other columns. After updating the SQL, uninstall your app to make onCreate() execute again.

answered Mar 13 '17 at 16:55

laalto

150,114
66
286
303

Did you also see the "uninstall" part? http://stackoverflow.com/questions/21881992/when-is-sqliteopenhelper-oncreate-onupgrade-run – laalto Mar 13 '17 at 17:05
Do you have more than one `SQLiteOpenHelper` operating on the same database file? (Hint: Don't. See the link in my previous comment for explanation for that one, too.) – laalto Mar 13 '17 at 17:09
no i only have one. Also, i have to tell you that, when i tried to uninstall it and build again. I click the button to insert the data however, it doesnt say any error anymore but the logcat keeps on running like its on a loop indicating "process incoming, handlepacket, sendBufferedRequest" – Mar 13 '17 at 17:14
handlePacket : cmd=0x1, cmdSet=0xC7, len=0x14, id=0x40001AB4, flags=0x0, dataLen=0x9 03-14 01:13:42.448 2661-2669/com.lorraine.lizzie.getaxi_driver D/jdwp: sendBufferedRequest : len=0x34 03-14 01:13:42.949 2661-2669/com.lorraine.lizzie.getaxi_driver D/jdwp: processIncoming – Mar 13 '17 at 17:14

score 0 · Answer 2 · answered Mar 13 '17 at 16:55

0

Have you updated your database scheme? In this case, you need to increment the database version number here:
private static final int DATABASE_VERSION = 1;

answered Mar 13 '17 at 16:55

Konstantin Buinak

11
3

increment by 1? – Mar 13 '17 at 17:16
Yes. But I guess it doesn't matter as long as you just increment the number. – Konstantin Buinak Mar 13 '17 at 17:19
if i increment it, the app will stop and it crashes. – Mar 13 '17 at 17:19
Can you post stacktrace of exception when it crashes? – Konstantin Buinak Mar 13 '17 at 17:22
AndroidRuntime: FATAL EXCEPTION: main android.database.sqlite.SQLiteException: near "EXIST": syntax error (code 1): , while compiling: DROP TABLE IF EXIST sampleForFinals – Mar 13 '17 at 17:24
Change "EXIST" to "EXISTS" in the query. – Konstantin Buinak Mar 13 '17 at 17:27
When the database version is 2. it crashes but then i add S for EXISTS. It crashes when i click on the button. – Mar 13 '17 at 17:33
ERROR SAYS: AndroidRuntime: FATAL EXCEPTION: main android.database.sqlite.SQLiteException: table drivers already exists (code 1): , while compiling: CREATE TABLE drivers(_id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT NOT NULL, lname TEXT NOT NULL, mname TEXT NOT NULL, contactNum TEXT NOT NULL, license TEXT NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL); – Mar 13 '17 at 17:33
This is really odd. Maybe try uninstalling the app and installing again? – Konstantin Buinak Mar 13 '17 at 17:36
I already did. still not working. :( Im crying already. Its been 4 hours now. – Mar 13 '17 at 17:41
You also need to close "ourDatabase" variable. Use .close() method. – Konstantin Buinak Mar 13 '17 at 17:42
I am also not sure, but my databases in apps have names like "ERate.db", so I have .db in the end. Maybe that would also help. – Konstantin Buinak Mar 13 '17 at 17:44
where should i put ourDatabase.close(); ? – Mar 13 '17 at 17:46
On your main activity's onDestroy(), I guess? I am not really good with it either, but that's where I have it. – Konstantin Buinak Mar 13 '17 at 17:54

Nikhil Sharma · Answer 3 · 2017-03-13T17:57:13.160

0

You have syntax error on your creating table query so i have correct your query try this

" CREATE TABLE " + DATABASE_TABLE + "( " +
                    KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    KEY_FNAME + " TEXT NOT NULL, " +
                    KEY_LNAME + " TEXT NOT NULL, " +
                    KEY_MNAME + " TEXT NOT NULL, " +
                    KEY_CONTACTNUM + " TEXT NOT NULL, " +
                    KEY_LICENSE + " TEXT NOT NULL, " +
                    KEY_USER + " TEXT NOT NULL, " +
                    KEY_PASS + " TEXT NOT NULL);"

Increment DB version by 1. Enjoy :)

And one more thing onUpgrade method your dropping Database instead you should drop table:

db.execSQL("DROP TABLE IF EXIST " + DATABASE_TABLE);

edited Mar 13 '17 at 17:57

answered Mar 13 '17 at 17:29

Nikhil Sharma

593
7
23

that is exactly the code i revised a minute ago. still not working :( – Mar 13 '17 at 17:37
if i increment db, it crashes – Mar 13 '17 at 17:37
onUpgrade instead Database Name put table name on query and please attach crash report coz its working fine with me – Nikhil Sharma Mar 13 '17 at 17:39
when i dont increment and i copied your query above. it will not crash but at the same time, it doesnt insert a record from user input into the database. – Mar 13 '17 at 17:48
and the logcat is in a loop. – Mar 13 '17 at 17:49
processIncoming handlePacket : cmd=0x1, cmdSet=0xC7, len=0x14, id=0x40002066, flags=0x0, dataLen=0x9 sendBufferedRequest : len=0x34 processIncoming handlePacket : cmd=0x1, cmdSet=0xC7, len=0x14, id=0x40002067, flags=0x0, dataLen=0x9 sendBufferedRequest : len=0x34 processIncoming – Mar 13 '17 at 17:49
can you put your code on bitbucket and you can email me so that i can see the problem. – Nikhil Sharma Mar 13 '17 at 17:52

Why is android saying no column when i have a column?

3 Answers3