CODEWITHSUNDEEP
androidandroid-sqlite
Sam
Sam

Reputation: 49

SQLite not updating table

I'm learning database right now. I created an SQLite database. I've 6 EditText in my layout and when I click on "Save to DB" button the data gets saved to DB. And when I click on "Show" button, it displays the DB details in EditTexts. But the problem is, when I try to save different data in the same database, it doesn't gets updated with the new data and the "Show" button shows the old data only.

public class FragmentFour extends Fragment {

    EditText name_ET, website_ET, bio_ET, altEmail_ET, phone_ET, facebook_ET;
    String name_str="", website_str="", bio_str="", altEmail_str="", phone_str="", facebook_str="";
    Button save, show, clear;
    private SQLiteHandler db;

    @Nullable
    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
        View rootView = inflater.inflate(R.layout.fragment_four, container, false);

      db = new SQLiteHandler(getActivity());

        name_ET = (EditText)rootView.findViewById(R.id.name);
        website_ET = (EditText)rootView.findViewById(R.id.webiste);
        bio_ET = (EditText)rootView.findViewById(R.id.bio);
        altEmail_ET = (EditText)rootView.findViewById(R.id.altEmail);
        phone_ET = (EditText)rootView.findViewById(R.id.phone);
        facebook_ET = (EditText)rootView.findViewById(R.id.facebook);

        save = (Button)rootView.findViewById(R.id.btn_save);
        show = (Button)rootView.findViewById(R.id.btn_show);
        clear = (Button)rootView.findViewById(R.id.btn_clr);

        save.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                name_str = name_ET.getText().toString();
                website_str = website_ET.getText().toString();
                bio_str = bio_ET.getText().toString();
                altEmail_str = altEmail_ET.getText().toString();
                phone_str = phone_ET.getText().toString();
                facebook_str = facebook_ET.getText().toString();

                db.addProfile(name_str, website_str, bio_str, altEmail_str, phone_str, facebook_str);
                name_ET.setText("");
                website_ET.setText("");
                bio_ET.setText("");
                altEmail_ET.setText("");
                phone_ET.setText("");
                facebook_ET.setText("");



            }
        });

        show.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                HashMap<String, String> pro = db.getProfDetails();
                String name = pro.get("name");
                String website = pro.get("website");
                String bio = pro.get("bio");
                String email = pro.get("email");
                String phone = pro.get("phone");
                String facebook = pro.get("facebook");

                name_ET.setText(name);
                website_ET.setText(website);
                bio_ET.setText(bio);
                altEmail_ET.setText(email);
                phone_ET.setText(phone);
                facebook_ET.setText(facebook);

            }
        });

        clear.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                name_ET.setText("");
                website_ET.setText("");
                bio_ET.setText("");
                altEmail_ET.setText("");
                phone_ET.setText("");
                facebook_ET.setText("");

            }
        });



        return rootView;
    }






}

SQLite Database

public class SQLiteHandler extends SQLiteOpenHelper {

    private static final String TAG = SQLiteHandler.class.getSimpleName();

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "android_api";

    // Profile Settings table name
    private static final String TABLE_PROF = "prof";

    // Profile Settings information names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_WEBSITE = "website";
    private static final String KEY_BIO = "bio";
    private static final String KEY_ALT_EMAIL = "alt_email";
    private static final String KEY_PHONE = "phone";
    private static final String KEY_FACEBOOK = "facebook";


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

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_PROF_TABLE = "CREATE TABLE " + TABLE_PROF + "("+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_NAME+" TEXT, "+KEY_WEBSITE+" TEXT, "+KEY_BIO+" TEXT, "+KEY_ALT_EMAIL+" TEXT, "+KEY_PHONE+" TEXT, "+KEY_FACEBOOK+" TEXT" + ")";

        db.execSQL(CREATE_PROF_TABLE);

        Log.d(TAG, "Database tables created");
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROF);

        // Create tables again
        onCreate(db);
    }


    /**
     * Storing Prof_settings details in database
     * */
    public void addProfile(String name, String website, String bio, String alt_email, String phone, String facebook){

        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(KEY_NAME, name);
        values.put(KEY_WEBSITE, website);
        values.put(KEY_BIO, bio);
        values.put(KEY_ALT_EMAIL, alt_email);
        values.put(KEY_PHONE, phone);
        values.put(KEY_FACEBOOK, facebook);

        // Inserting Row
        long id = db.insert(TABLE_PROF, null, values);
        db.close(); // Closing database connection

        Log.d(TAG, "New profile settings inserted into sqlite: " + id);

    }




    /**
     * Getting Profile Settings data from database
     * */
    public HashMap<String, String> getProfDetails() {
        HashMap<String, String> pro = new HashMap<String, String>();
        String selectQuery = "SELECT  * FROM " + TABLE_PROF;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            pro.put("name", cursor.getString(1));
            pro.put("website", cursor.getString(2));
            pro.put("bio", cursor.getString(3));
            pro.put("email", cursor.getString(4));
            pro.put("phone", cursor.getString(5));
            pro.put("facebook", cursor.getString(6));
        }
        cursor.close();
        db.close();
        // return log details
        Log.d(TAG, "Fetching profile details from Sqlite: " + pro.toString());

        return pro;
    }

    /**
     * Re crate database Delete all tables and create them again
     * */
    public void deleteUsers() {
        SQLiteDatabase db = this.getWritableDatabase();
        // Delete All Rows
        db.delete(TABLE_PROF, null, null);
        db.close();

        Log.d(TAG, "Deleted all profile info from sqlite");
    }

}

Upvotes: 0

Views: 4315

Answers (2)

Rohit5k2
Rohit5k2

Reputation: 18112

Scenario 1

I think data is being saved to the Database but problem is that on click of "Save to DB" button you are inserting the data to the table.

When you retrieve the data you get the first row from the cursor (cursor.moveToFirst();) and display it in your EditText.

Rather than inserting the value into the database you should update it if data already exists in the table or simply delete the existing rows before inserting new one.

If you want to clear the table

Just add this line 

db.execSQL("delete from "+ TABLE_PROF);

before 

long id = db.insert(TABLE_PROF, null, values);

in addProfile() method

If you want to update the existing row

Use command like this

db.update(TABLE_PROF, values, "_id=1", null);

But do a check if there is an existing item. If item doesn't exist just insert the row/

Scenario 2

Or if you want to add multiple rows then you should show values in the EditText from the last row of cursor (cursor.moveToLast();).

Change 

 cursor.moveToFirst();

to 

 cursor.moveToLast();

then you will see the updated values.

Upvotes: 2

gdaramouskas
gdaramouskas

Reputation: 3747

On the click of the button you always insert a new row to your database, there is no update statement in your code.

Also, when your query the database and your get a cursor back, that cursor can be used to iterate over the results. What you are basically doing over there is instruct the cursor to moveToFirst and you only query the first row. So even if you add the update function you will always get the first row of the results.

Look at the official documentation to see how you can update a row and how you can use a cursor.

See this for an example.

Upvotes: 0

Related Questions