Inserting data into SQLite database, datatype mismatch

Question

I wanted to insert data into the database using SQLite but i fail to do it. I pressed on the button I've created to input but the data could not be sent into the database. I've read through the LogCat and it says something like data type mismatch, I have no idea which data type it means.

Here is my code :

database.java

public class database {

    public static final String MOVIE_NAME = "mName";

    private static final String DATABASE_NAME = "Movie";
    private static final String DATABASE_TABLE = "mList";
    private static final int DATABASE_VERSION = 1;

    private Database db;
    private final Context cont;
    private SQLiteDatabase moviedatabase;

    public static class Database extends SQLiteOpenHelper{

        public Database(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
            // TODO Auto-generated constructor stub
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                    MOVIE_NAME + " TEXT NOT NULL);"
            );
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_NAME);
        }

    }

    public database(Context con){
        cont = con;
    }

    public database open() throws Exception{
        db = new Database(cont);
        moviedatabase = db.getWritableDatabase();
        return this;
    }
    public void close(){
        db.close();
    }

    public void createEntry(String input) {
        // TODO Auto-generated method stub
        ContentValues content = new ContentValues();
        content.put(MOVIE_NAME, input);
        moviedatabase.insert(DATABASE_TABLE, null, content);
    }

    public String getData() {
        // TODO Auto-generated method stub


        String[] col = new String[]{MOVIE_NAME};
        Cursor curs = moviedatabase.query(DATABASE_TABLE, col, null, null, null, null, null);
        String res = "";
        int rowNum = curs.getColumnIndex(MOVIE_NAME);

        for(curs.moveToFirst(); !curs.isAfterLast(); curs.moveToNext()){
            res = res + curs.getString(rowNum) + "\n";
        }

        return res;
    }

insert.java

public class insert extends Activity{

    Button insBtn;
    EditText insET;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.insert);

        insET = (EditText) findViewById(R.id.insertET);
        insBtn = (Button) findViewById(R.id.insertBtn);

        insBtn.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub

                try{
                    String input = insET.getText().toString();

                    database newEntry = new database(insert.this);
                    newEntry.open();
                    newEntry.createEntry(input);
                    newEntry.close();
                }catch(Exception e){
                    e.printStackTrace();
                }
            }
        });
    }

list.java

public class list extends Activity{
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.list);

        TextView d = (TextView) findViewById(R.id.movieName);
        database movies = new database(this);

        try{
            movies.open();
            String data = movies.getData();
            movies.close();
            d.setText(data);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

}

LogCat:

03-16 07:28:17.617: E/SQLiteLog(3194): (20) statement aborts at 6: [INSERT INTO mList(mName) VALUES (?)] datatype mismatch
03-16 07:28:17.635: E/SQLiteDatabase(3194): Error inserting mName=dfsefsd
03-16 07:28:17.635: E/SQLiteDatabase(3194): android.database.sqlite.SQLiteDatatypeMismatchException: datatype mismatch (code 20)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at com.example.project.database.createEntry(database.java:63)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at com.example.project.insert$1.onClick(insert.java:34)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.view.View.performClick(View.java:4204)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.view.View$PerformClick.run(View.java:17355)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.os.Handler.handleCallback(Handler.java:725)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.os.Handler.dispatchMessage(Handler.java:92)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.os.Looper.loop(Looper.java:137)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at android.app.ActivityThread.main(ActivityThread.java:5041)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at java.lang.reflect.Method.invokeNative(Native Method)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at java.lang.reflect.Method.invoke(Method.java:511)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
03-16 07:28:17.635: E/SQLiteDatabase(3194):     at dalvik.system.NativeStart.main(Native Method)
03-16 07:28:26.418: I/Choreographer(3194): Skipped 33 frames!  The application may be doing too much work on its main thread.

could you use breakpoint to know what data you waht to input (if you want to add list, empty data, null values, etc) ? — clement
– clement, Commented Mar 16, 2015 at 7:39
Try to change the database name and copy it again to database path in android system, maybe you had copied another database with the same name and different schema. — Mahmoud
– Mahmoud, Commented Mar 16, 2015 at 7:41
maybe you have multiple db's, try to change db name and try again. — Murtaza Khursheed Hussain
– Murtaza Khursheed Hussain, Commented Mar 16, 2015 at 7:52
@clement Sorry >< I'm new, i didnt go into breakpoint yet... No idea what is that — Danzeeeee
– Danzeeeee, Commented Mar 16, 2015 at 8:38
Thanks Mahmoud Shahoud and Murtaza Hussain, it works after i change the db name, I feel weird because i didn't even used that name before ><. Thanks anyway !!! — Danzeeeee
– Danzeeeee, Commented Mar 16, 2015 at 8:40

avk · Accepted Answer · 2015-03-16 08:19:21Z

1

First start changing your code from

db.execSQL("DROP TABLE IF EXISTS " + DATABASE_NAME);

to

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

Good chance this explains the datatype error.

answered Mar 16, 2015 at 8:19

avk

8711 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danzeeeee Over a year ago

I didn't notice that this is one of the error, Thanks mate!

Collectives™ on Stack Overflow

Inserting data into SQLite database, datatype mismatch

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related