SQLite Data insertion error Android studio

Question

This is my activity class.when i press the data button data should be inserted into database.But every time "Not inserted" comes up

 my = new DatabaseHandler(this);
        data=(Button)findViewById(R.id.data);

        data.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                time=clockTimeTextView.getText().toString();
                repeat=Repeat_optionTextview.getText().toString();
                ringtone=Ringtone_optionTextview.getText().toString();
                if(chkBox.isChecked()){
                    vibrate="true";
                }else{
                    vibrate="false";
                }
                method=Method_optionTextview.getText().toString();

                //Toast.makeText(getApplicationContext(),method + ringtone ,Toast.LENGTH_LONG).show();

                boolean checkers = my.addtotable(et.getText().toString(),time,repeat,ringtone,vibrate,method);
                if (checkers == true) {
                    Toast.makeText(AddAlarmActivity.this, "Successfully Inserted", Toast.LENGTH_LONG).show();
                } else {
                    Toast.makeText(AddAlarmActivity.this, "Not Inserted", Toast.LENGTH_LONG).show();
                }

            }
        });

This is the DatabaseHandler class

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class DatabaseHandler extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "database5.db";
    private static final String TABLE_NAME = "mytable10";
    private static final String COLUMN1 = "ID";
    private static final String COLUMN2 = "TIME";
    private static final String COLUMN3 = "REPEAT";
    private static final String COLUMN4 = "RINGTONE";
    private static final String COLUMN5 = "VIBRATE";
    private static final String COLUMN6 = "METHOD";



    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, 2);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {

        String query;
        query = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN1 + "  INTEGER PRIMARY KEY , "
                                                   + COLUMN2 + " TEXT , "
                                                   + COLUMN3 + " TEXT , "
                                                   + COLUMN4 + " TEXT , "
                                                   + COLUMN5 + " TEXT , "
                                                   + COLUMN6 + "TEXT " + ")";
        db.execSQL(query);

    }

    @Override


    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL(" DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }


    public boolean addtotable(String id, String time, String repeat, String ringtone,String vibrate,String method) {

        SQLiteDatabase sqLiteDatabase = getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN1, id);
        contentValues.put(COLUMN2, time);
        contentValues.put(COLUMN3, repeat);
        contentValues.put(COLUMN4, ringtone);
        contentValues.put(COLUMN5, vibrate);
        contentValues.put(COLUMN6, method);

        long checker = sqLiteDatabase.insert(TABLE_NAME, null, contentValues);
        if (checker == -1) {
            return false;
        } else {
            return true;
        }
    }

    public Cursor display() {

        SQLiteDatabase sqLiteDatabase = getReadableDatabase();
        Cursor res;
        res = sqLiteDatabase.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        return res;
    }
}

when i make two column(column_1 & column_2) it works fine.But i when i make more than two column "Not Inserted" message comes.i can't find where the error is.help please

MikeT · Accepted Answer · 2018-02-04T19:47:22.610

At a guess the reason is that the text passed to the addtotable method that is retrieved via et.getText().toString() is not a valid integer and thus fails the special constraint of a rowid value as per :-

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer. SQLite Autoincrement

Note by a valid integer, it would not matter if the value were a String e.g. 1,2,3......9223372036854775807 as SQLite will convert this accordingly.

I would suggest that the easiest fix would be by commenting out or deleting the line :-

    contentValues.put(COLUMN1, id);

An alternative solution would be to ensure that the value retrieved from what would appear to be an EditText is an integer. However I don't believe that would be at all good for a user interface.

Another alternative would to change the column so that it is not an alias of rowid, perhaps by specifying TEXT as the column type or even INT PRIMARY KEY (note you will have to DROP the table and redefine it. Increasing the database version number should do this or you can delete the App's data or uninstall the App, the latter two delete the database and thus the onCreate method is run).

You should perhaps review your requirements of the id column. Typically an id column is used to uniquely identify a row and is typically of little, if any value to a user.

it helped by commenting the line you said.can you tell me why this works? — Md Faysal Ahmed Akash, Feb 04 '18 at 20:06
@MdAhmedAkash because ?? INTEGER PRIMARY KEY is special it's saying ?? (where ?? is a valid column name) is an alias for a special index (rowid) that for efficiency MUST be an integer. So the row will fail that special constraint. Not supplying a value lets SQlite work-out a unique integer value, it's what rowid is designed for. You can override SQLite and provide a value but it MUST be an integer. Although it's special it is also very common and very useful. — MikeT, Feb 04 '18 at 20:10

SQLite Data insertion error Android studio

1 Answers1