Error while inserting data into a database

Question

I am getting error while inserting data in to sqlite data base.This is my query. What is the problem.

I am not able get out of this. I think error because of space in creating table, where i need to make changes.

@Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_PH_NO + " TEXT," + KEY_MACID + " TEXT" + ")";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

This is the error i am getting.

Error inserting macid=benbena phone_number=01-04-2016 17:32:31 PM name=testlock1@acttv.in android.database.sqlite.SQLiteException: table contacts has no column named macid (code 1): , while compiling: INSERT INTO contacts(macid,phone_number,name) VALUES (?,?,?)

no what i am asking is you would have somewhere decalared variable KEY_MACID like String KEY_MACID = ?; what value did you gave it there? — Ankit Aggarwal, Apr 01 '16 at 13:00
Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID, KEY_NAME, KEY_PH_NO, KEY_MACID }, KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null, null, null); — benarjee bojja, Apr 01 '16 at 13:04
your statement looks fine. After running the app for the first time, have you made any changes in the create table command. If yes, try to uninstall and install the app again — Ankit Aggarwal, Apr 01 '16 at 13:10
You could check your table schema with this example http://stackoverflow.com/questions/14390331/android-sqlite-database-schema — Miguel Benitez, Apr 01 '16 at 13:12
Can you show us the rest of the code that is storing the values of your columns and table name please? — blueprintchris, Apr 01 '16 at 13:49

blueprintchris · Answer 1 · 2016-04-01T14:08:22.307

It is advised to create a Contact Class to handle the data easily first. However, without that, here is how I would accomplish the database entry task:

Custom SQLiteOpenHelper Class:

public class DBHandlerTest extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "database.db";

    public static final int DATABASE_VERSION = 1;

    public static final String TABLE_CONTACTS = "contacts";

    public static final String KEY_ID = "id";
    public static final String KEY_NAME = "name";
    public static final String KEY_PH_NO = "phone_number";
    public static final String KEY_MACID = "macid";


    public static final String CREATE_CONTACTS_TABLE =
            "CREATE TABLE " + TABLE_CONTACTS + "("
                    + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + KEY_NAME + " TEXT(255) NOT NULL, "
                    + KEY_PH_NO + " TEXT(255) NOT NULL, "
                    + KEY_MACID + " TEXT(255) NOT NULL" + ")";


    public DBHandlerTest(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
        //re-create db
        onCreate(db);
    }

    public void insertContact(String name, String phoneNumber, String macID) {

        //create db instance
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contactValues = new ContentValues();

        //get the values
        contactValues.put(KEY_NAME, name);
        contactValues.put(KEY_PH_NO, phoneNumber);
        contactValues.put(KEY_MACID, macID);

        //insert row into db
        db.insert(TABLE_CONTACTS, null, contactValues);
        db.close();

    }

}

Implementing the method:

DBHandlerTest db = new DBHandlerTest(this);
db.insertContact("test@test.com", "0800-123456", "test_id"); 
db.close();

Error while inserting data into a database

1 Answers1