SQLite db duplicate values Android

Question

I'm using a spinner in my application that takes the values from a SQLite database. The problem is that every time I run the application from Eclipse the values loads again and again. So if the first time I have 5 values in the spinner, the second time I have 10 values, then 15, then 20 etc. How can I solve this?

This is the db part:

In the onCreate:

createTable();
        insertIntoTable();

        ArrayList<String> my_array = new ArrayList<String>();
        my_array = getTableValues();

        My_spinner = (Spinner) findViewById(R.id.scelte);
        My_spinner.setOnItemSelectedListener(this);
        ArrayAdapter my_Adapter = new ArrayAdapter(this, R.layout.spinner_row, my_array);
        My_spinner.setAdapter(my_Adapter);

and then:

// CREATE TABLE IF NOT EXISTS
    public void createTable() {
        try {
            mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
            mydb.execSQL("CREATE TABLE IF  NOT EXISTS " + TABLE
                    + " (ID INTEGER PRIMARY KEY, NAME TEXT, PLACE TEXT);");
            mydb.close();
        } catch (Exception e) {
            Toast.makeText(getApplicationContext(), "Errore durante la creazione della tabella",
                    Toast.LENGTH_LONG);
        }
    }

    // THIS FUNCTION INSERTS DATA TO THE DATABASE
    public void insertIntoTable() {
        try {
            mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
            mydb.execSQL("INSERT INTO " + TABLE
                    + "(NAME, PLACE) VALUES('Cibo e Bevande','cibo')");
            mydb.execSQL("INSERT INTO " + TABLE
                    + "(NAME, PLACE) VALUES('Trasporti','trasporti')");
            mydb.execSQL("INSERT INTO " + TABLE
                    + "(NAME, PLACE) VALUES('Svago','svago')");
            mydb.execSQL("INSERT INTO " + TABLE
                    + "(NAME, PLACE) VALUES('Acquisti vari','acquisti')");
            mydb.execSQL("INSERT INTO " + TABLE
                    + "(NAME, PLACE) VALUES('Sanità','sanità')");
            mydb.execSQL("INSERT INTO " + TABLE
                    + "(NAME, PLACE) VALUES('Altro','altro')");
            mydb.close();


        } catch (Exception e) {
            Toast.makeText(getApplicationContext(),
                    "Error in inserting into table", Toast.LENGTH_LONG);
        }
    }

    // THIS FUNCTION SHOWS DATA FROM THE DATABASE
    public ArrayList<String> getTableValues() {

        ArrayList<String> my_array = new ArrayList<String>();
        try {
            mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
            Cursor allrows = mydb.rawQuery("SELECT * FROM " + TABLE, null);
            System.out.println("COUNT : " + allrows.getCount());

            if (allrows.moveToFirst()) {
                do {

                    String ID = allrows.getString(0);
                    String NAME = allrows.getString(1);
                    String PLACE = allrows.getString(2);
                    my_array.add(NAME);


                } while (allrows.moveToNext());
            }
            allrows.close();
            mydb.close();
        } catch (Exception e) {
            Toast.makeText(getApplicationContext(), "Errore.",
                    Toast.LENGTH_LONG);
        }
        return my_array;

    }

    @Override
    public void onItemSelected(AdapterView<?> arg0, View arg1, int arg2,
            long arg3) {
        // TODO Auto-generated method stub
        String selecteditem = My_spinner.getSelectedItem().toString();
        TextView categorietext = (TextView) findViewById(R.id.sceltelabel);
        categorietext.setText(selecteditem);
    }

    @Override
    public void onNothingSelected(AdapterView<?> arg0) {
        // TODO Auto-generated method stub

    }

Do the checking before inserting the data into the table. You can 1. query the DB, if it is empty then insert, or 2. use SharedPreference to indicate that the DB has been initialized or not. — Andrew T.
– Andrew T., Commented Nov 7, 2013 at 10:42

Szymon · Accepted Answer · 2013-11-07 10:53:16Z

2

You run this

insertIntoTable();

every time in onCreate so it adds more and more data.

You should use extend SQLiteOpenHelper class and use onCreate method to create your database and initialise tables. See Android developer reference.

edited Nov 7, 2013 at 10:53

answered Nov 7, 2013 at 10:42

Szymon

43k16 gold badges99 silver badges115 bronze badges

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

2 Comments

David_D Over a year ago

yes.. but if i remove this how can i insert the values only for the first time?

Szymon Over a year ago

Check SQLiteOpenHelper class: developer.android.com/reference/android/database/sqlite/…. It has onCreate method where you can do this sort of initialisation.

Yahya Arshad · Accepted Answer · 2013-11-07 10:48:03Z

1

Every time you will open your activity onCreate method of activity will be invoke and in your onCreate method you are calling insertIntoTable(); which will insert new record every time.

answered Nov 7, 2013 at 10:48

Yahya Arshad

1,6282 gold badges19 silver badges34 bronze badges

Comments

Satyen Udeshi · Accepted Answer · 2013-11-07 10:52:30Z

0

Every time you run yor application you call insertintotable() method, so its is happenning

to solve this:

check the table is empty or not . if it is empty then call insertintotable() method else dont

answered Nov 7, 2013 at 10:52

Satyen Udeshi

3,2531 gold badge22 silver badges27 bronze badges

Comments

Andrew T. · Accepted Answer · 2013-11-07 11:02:48Z

0

Every time you run the app, onCreate() will be called, and thus insertIntoTable() will also be called. You have to add a checking before inserting the data. One of the way is to check whether the DB is empty or not before inserting the data.

public void insertIntoTable() {
    try {
        mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
        Cursor c = mydb.query(TABLE, null, null, null, null, null, null);
        if (c == null || c.getCount() == 0) {
            mydb.execSQL("INSERT INTO " + TABLE
                + "(NAME, PLACE) VALUES('Cibo e Bevande','cibo')");
            mydb.execSQL("INSERT INTO " + TABLE
            + "(NAME, PLACE) VALUES('Trasporti','trasporti')");
            mydb.execSQL("INSERT INTO " + TABLE
            + "(NAME, PLACE) VALUES('Svago','svago')");
            mydb.execSQL("INSERT INTO " + TABLE
            + "(NAME, PLACE) VALUES('Acquisti vari','acquisti')");
            mydb.execSQL("INSERT INTO " + TABLE
            + "(NAME, PLACE) VALUES('Sanità','sanità')");
            mydb.execSQL("INSERT INTO " + TABLE
            + "(NAME, PLACE) VALUES('Altro','altro')");
            mydb.close();
        }
        c.close();
    } catch (Exception e) {
        Toast.makeText(getApplicationContext(),
            "Error in inserting into table", Toast.LENGTH_LONG);
    }
}

edited Nov 7, 2013 at 11:02

answered Nov 7, 2013 at 10:50

Andrew T.

4,7078 gold badges48 silver badges69 bronze badges

3 Comments

David_D Over a year ago

The method query(String, String[], String, String[], String, String, String) in the type SQLiteDatabase is not applicable for the arguments (String, null, null, null, null, null)

:(

David_D Over a year ago

i added a null, now i try

Andrew T. Over a year ago

Sorry for the typo! Glad it worked. If possible, please mark the answer when you think it's correct to indicate that the problem has been solved. :)

Collectives™ on Stack Overflow

SQLite db duplicate values Android

4 Answers 4

2 Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related