1

I have a problem with some android code. I'm writing values in SQLite database but after reading selected row from db I'm getting another values. This is my code for Database class:

    package com.example.maszspotkanie;
    import android.content.ContentValues;
    import android.content.Context;
    import android.content.CursorLoader;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteDatabase.CursorFactory;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.util.Log;

    public class MSDatabase {
    private static final String DEBUG_TAG = "SqLiteTodoManager";
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "database.db";
    private static final String DB_MS_TABLE = "spotkania";
    public static final String KEY_ID = "_id";
    public static final String ID_OPTIONS = "INTEGER PRIMARY KEY AUTOINCREMENT";
    public static final int ID_COLUMN = 0;
    public static final String KEY_NAME = "name";
    public static final String NAME_OPTIONS = "TEXT NOT NULL";
    public static final int NAME_COLUMN = 1;
    public static final String KEY_COMPLETED = "completed";
    public static final String COMPLETED_OPTIONS = "INTEGER DEFAULT 0";
    public static final int COMPLETED_COLUMN = 2;
    public static final String KEY_DATE = "date";
    public static final String DATE_OPTIONS = "TEXT NOT NULL";
    public static final int DATE_COLUMN = 3;
    public static final String KEY_PICTURE = "picture";
    public static final String PICTURE_OPTIONS = "INTEGER";
    public static final int PICTURE_COLUMN = 4;
    public static final String KEY_HOUR = "hour";
    public static final String HOUR_OPTIONS = "TEXT NOT NULL";
    public static final int HOUR_COLUMN = 5;
    public static final String KEY_LAT = "lat";
    public static final String LAT_OPTIONS = "FLOAT NOT NULL";
    public static final int LAT_COLUMN = 6;
    public static final String KEY_LNG = "lng";
    public static final String LNG_OPTIONS = "FLOAT NOT NULL";
    public static final int LNG_COLUMN = 7;
    public static final String KEY_LOCALIZATION = "localization";
    public static final String LOCALIZATION_OPTIONS = "TEXT";
    public static final int LOCALIZATION_COLUMN = 8;
    public static final String KEY_DESCRIPTION = "description";
    public static final String DESCRIPTION_OPTIONS = "TEXT";
    public static final int DESCRIPTION_COLUMN = 9;
    private static final String DB_CREATE_MSDatabase_TABLE = "CREATE TABLE "
            + DB_MS_TABLE + "( " + KEY_ID + " " + ID_OPTIONS + ", " + KEY_NAME
            + " " + NAME_OPTIONS + ", " + KEY_DESCRIPTION + " "
            + DESCRIPTION_OPTIONS + ", " + KEY_COMPLETED + " "
            + COMPLETED_OPTIONS + ", " + KEY_DATE + " " + DATE_OPTIONS + ", "
            + KEY_PICTURE + " " + PICTURE_OPTIONS + ", " + KEY_HOUR + " "
            + HOUR_OPTIONS + ", " + KEY_LAT + " " + LAT_OPTIONS + ", "
            + KEY_LNG + " " + LNG_OPTIONS + ", " + KEY_LOCALIZATION + " "
            + LOCALIZATION_OPTIONS + ");";
    private static final String DROP_MSDatabase_TABLE = "DROP TABLE IF EXISTS "
            + DB_MS_TABLE;
    private SQLiteDatabase db;
    private Context context;
    private DatabaseHelper dbHelper;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        public DatabaseHelper(Context context, String name,
                CursorFactory factory, int version) {
            super(context, name, factory, version);
        }

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

            Log.d(DEBUG_TAG, "Database creating...");
            Log.d(DEBUG_TAG, "Table " + DB_MS_TABLE + " ver." + DB_VERSION
                    + " created");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL(DROP_MSDatabase_TABLE);

            Log.d(DEBUG_TAG, "Database updating...");
            Log.d(DEBUG_TAG, "Table " + DB_MS_TABLE + " updated from ver."
                    + oldVersion + " to ver." + newVersion);
            Log.d(DEBUG_TAG, "All data is lost.");

            onCreate(db);
        }
    }

    public MSDatabase(Context context) {
        this.context = context;
    }

    public MSDatabase open() {
        dbHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
        try {
            db = dbHelper.getWritableDatabase();
        } catch (SQLException e) {
            db = dbHelper.getReadableDatabase();
        }
        return this;
    }

    public long insertMS(String description, String date, int picture,
            String hour, Float lat, Float lng, String localization, String name) {
        ContentValues newValues = new ContentValues();
        newValues.put(KEY_NAME, name);
        newValues.put(KEY_DESCRIPTION, description);
        newValues.put(KEY_DATE, date);
        newValues.put(KEY_PICTURE, picture);
        newValues.put(KEY_HOUR, hour);
        newValues.put(KEY_LAT, lat);
        newValues.put(KEY_LNG, lng);
        newValues.put(KEY_LOCALIZATION, localization);
        System.out.println("Database:  nazwa " + name + " termin " + date
                + " h " + hour + " opis " + description + " zdjecie " + picture
                + " lat " + lat + " lng " + lng + " lokalizacja "
                + localization);

        return db.insert(DB_MS_TABLE, null, newValues);
    }

    public boolean deleteMS(long id) {
        String where = KEY_ID + "=" + id;
        return db.delete(DB_MS_TABLE, where, null) > 0;
    }

    public Cursor getAllMS() {
        String[] columns = { KEY_ID, KEY_NAME, KEY_DESCRIPTION, KEY_COMPLETED,
                KEY_DATE, KEY_PICTURE, KEY_HOUR, KEY_LAT, KEY_LNG,
                KEY_LOCALIZATION };

        return db.query(DB_MS_TABLE, columns, null, null, null, null, null);
    }

    public MSDBTask getMS(int id) {
        String[] columns = { KEY_ID, KEY_NAME, KEY_DESCRIPTION, KEY_COMPLETED,
                KEY_DATE, KEY_PICTURE, KEY_HOUR, KEY_LAT, KEY_LNG,
                KEY_LOCALIZATION };
        String where = KEY_ID + "=" + id;
        Cursor cursor = db.query(DB_MS_TABLE, columns, where, null, null, null,
                null);
        MSDBTask task = null;
        if (cursor != null && cursor.moveToFirst()) {
            String name = cursor.getString(NAME_COLUMN);
            String description = cursor.getString(DESCRIPTION_COLUMN);
            String date = cursor.getString(DATE_COLUMN);
            int picture = cursor.getInt(PICTURE_COLUMN);
            String hour = cursor.getString(HOUR_COLUMN);
            float lat = cursor.getFloat(LAT_COLUMN);
            float lng = cursor.getFloat(LNG_COLUMN);
            String localization = cursor.getString(LOCALIZATION_COLUMN);
            boolean completed = cursor.getInt(COMPLETED_COLUMN) > 0 ? true
                    : false;
            task = new MSDBTask(id, name, description, completed, date,
                    picture, hour, lat, lng, localization);

        }
        return task;
    }

    public void close() {
        dbHelper.close();
    }

    public void dropDatabase() {
        db.execSQL(DROP_MSDatabase_TABLE);
        db.execSQL(DB_CREATE_MSDatabase_TABLE);
    }

    public boolean updateMS(MSDBTask task) {
        long id = task.getId();
        String description = task.getDescription();
        boolean completed = task.isCompleted();
        String date = task.getDate();
        int picture = task.getPicture();
        String hour = task.getHour();
        Float lat = task.getLat();
        Float lng = task.getLng();
        String localization = task.getLocalization();
        String name = task.getName();
        System.out.println("Database:  nazwa " + name + " termin " + date
                + " h " + hour + " opis " + description + " zdjecie " + picture
                + " lat " + lat + " lng " + lng + " lokalizacja "
                + localization);
        return updateMS(description, date, picture, hour, lat, lng,
                localization, name, completed, id);
    }

    public boolean updateMS(String description, String date, int picture,
            String hour, Float lat, Float lng, String localization,
            String name, boolean completed, long id) {

        String where = KEY_ID + "=" + id;
        int completedTask = completed ? 1 : 0;
        ContentValues updateMSValues = new ContentValues();
        updateMSValues.put(KEY_LOCALIZATION, localization);
        updateMSValues.put(KEY_LNG, lng);
        updateMSValues.put(KEY_LAT, lat);
        updateMSValues.put(KEY_HOUR, hour);
        updateMSValues.put(KEY_PICTURE, picture);
        updateMSValues.put(KEY_DATE, date);
        updateMSValues.put(KEY_COMPLETED, completedTask);
        updateMSValues.put(KEY_DESCRIPTION, description);
        updateMSValues.put(KEY_NAME, name);

        return db.update(DB_MS_TABLE, updateMSValues, where, null) > 0;
    }

}

And class where I call this function:

package com.example.maszspotkanie;

import java.util.ArrayList;
import java.util.List;

import android.view.View.OnClickListener;
import android.app.Activity;
import android.content.CursorLoader;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;

public class MainActivity extends Activity {
    Button noweSpotkanie;
    Button deleteAll;
     private MSDatabase MSDbAdapter;
        private Cursor MSCursor;
        private List<MSDBTask> tasks;
        private MSDBTaskAdapter listAdapter;
        private ListView lvSpotkania;
        private static final int NEWMS_ACTIVITY_REQUEST_CODE = 1;
        private int actualid;
        int idlast=0;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        noweSpotkanie=(Button) findViewById(R.id.button1);
        deleteAll=(Button) findViewById(R.id.drop);
        lvSpotkania=(ListView) findViewById(R.id.listView1);
        MSDbAdapter = new MSDatabase(getApplicationContext());
        MSDbAdapter.open();
        initListView();
        actualid=2147483647;
        noweSpotkanie.setOnClickListener(new OnClickListener(){

            @Override
            public void onClick(View v) {
                Intent intent = new Intent(getApplicationContext(),NewMSActivity.class);
                intent.putExtra("ID", actualid);
                startActivityForResult(intent, NEWMS_ACTIVITY_REQUEST_CODE);

            }


        });
        deleteAll.setOnClickListener(new OnClickListener(){

            @Override
            public void onClick(View v) {
                MSDbAdapter.dropDatabase();
                initListView();
            }


        });
    }
    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {


        if (NEWMS_ACTIVITY_REQUEST_CODE == resultCode) {
            String nazwa = data.getStringExtra("Nazwa");
            String termin = data.getStringExtra("Termin");
            String godzina = data.getStringExtra("Godzina");
            String opis = data.getStringExtra("Opis");
            String zdjecie = data.getStringExtra("Zdjecie");
            String lat = data.getStringExtra("Lat");
            String lng = data.getStringExtra("Lng");
            String lokalizacja = data.getStringExtra("Lokalizacja");
            String odpowiedz=data.getStringExtra("RESPONSE");

            System.out.println("Before save :  nazwa "+nazwa+ " termin " +termin+ " h "+ godzina 
                    + " opis " +opis + " zdjecie " + zdjecie+" lat " + lat
                    + " lng " +lng + " lokalizacja " +lokalizacja ); 
           saveNewTask(actualid,nazwa,termin,godzina,opis,zdjecie,lat,lng,lokalizacja,odpowiedz);
        }
    }
    private void saveNewTask(long id, String nazwa, String termin, String godzina, 
            String opis, String zdjecie, String lat, String lng, String lokalizacja,String odpowiedz){      

        int picture=Integer.parseInt(zdjecie);
        float LAT=Float.parseFloat(lat);
        float LNG=Float.parseFloat(lng);
        System.out.println("Save :   nazwa "+nazwa+ " termin " +termin+ " h "+ godzina 
                + " opis " +opis + " zdjecie " + picture+" lat " + LAT
                + " lng " +LNG + " lokalizacja " +lokalizacja+ "  odpowiedz "+odpowiedz );
        if(odpowiedz.equalsIgnoreCase("insertnew")){
            MSDbAdapter.insertMS(opis, termin,picture, godzina, LAT, LNG, lokalizacja, nazwa);

          idlast++;
          System.out.println("id="+idlast);
          initListView();
            }
        else{
            MSDBTask task=new MSDBTask(actualid,  nazwa,opis, false, termin,  picture, 
                     godzina,  LAT,  LNG, lokalizacja);
            MSDbAdapter.updateMS(task);
            }
        updateListViewData();
    }

    private void initListView() {

            fillListViewData();
            initListViewOnItemClick();


    }
     private void initListViewOnItemClick() {
            lvSpotkania.setOnItemClickListener(new OnItemClickListener() {
                @Override
                public void onItemClick(AdapterView<?> parent, View v, int position,
                        long id) {
                    MSDBTask task = tasks.get(position);
                    Intent intent = new Intent(getApplicationContext(),NewMSActivity.class);
                    actualid=task.getId();
                    intent.putExtra("ID", actualid);
                    int x=1;
                    startActivityForResult(intent, x);
                    updateListViewData();
                }
            });
        }
    private void fillListViewData() {
        getAllTasks();
        listAdapter = new MSDBTaskAdapter(this, tasks);     
        lvSpotkania.setAdapter(listAdapter);

    }
    private void getAllTasks() {
        tasks = new ArrayList<MSDBTask>();
        MSCursor = getAllEntriesFromDb();
        updateTaskList();

    }
    private void updateTaskList() {
        idlast=0;
        if(MSCursor != null && MSCursor.moveToFirst()) {
            do {
                int id = MSCursor.getInt(MSDbAdapter.ID_COLUMN);
                String description = MSCursor.getString(MSDbAdapter.DESCRIPTION_COLUMN);
                boolean completed = MSCursor.getInt(MSDbAdapter.COMPLETED_COLUMN) > 0 ? true : false;
                String name=MSCursor.getString(MSDbAdapter.NAME_COLUMN);
                String date=MSCursor.getString(MSDbAdapter.DATE_COLUMN);
                String hour=MSCursor.getString(MSDbAdapter.HOUR_COLUMN);
                int picture=MSCursor.getInt(MSDbAdapter.PICTURE_COLUMN);
                float lat=MSCursor.getFloat(MSDbAdapter.LAT_COLUMN);
                float lng=MSCursor.getFloat(MSDbAdapter.LNG_COLUMN);
                String localization=MSCursor.getString(MSDbAdapter.LOCALIZATION_COLUMN);
                tasks.add(new MSDBTask( id, name, description, completed, date, picture, 
                         hour,  lat, lng, localization));

                idlast++;

            } while(MSCursor.moveToNext());
        }

    }

    private Cursor getAllEntriesFromDb() {
        MSCursor = MSDbAdapter.getAllMS();

        if(MSCursor != null) {

            startManagingCursor(MSCursor);
            MSCursor.moveToFirst();
        }
        return MSCursor;
    }
    private void updateListViewData() {

        MSCursor.requery();
        tasks.clear();
        updateTaskList();
        listAdapter.notifyDataSetChanged();

    }
    @Override
    protected void onDestroy() {
        if(MSDbAdapter != null)
            MSDbAdapter.close();
        super.onDestroy();
    }
}

I get values from other activity, of course I checked that values are correct forwarded from next activity. I'm using class MDSBTask to get and set values:

package com.example.maszspotkanie;

public class MSDBTask {
    private int id;
    private String description;
    private boolean completed;
    private String date;
    private int picture;
    private String hour;
    private Float lat;
    private Float lng;
    private String localization;
    private String name;


    public MSDBTask(int id, String name,String description, boolean completed,String date, int picture, 
            String hour, Float lat, Float lng, String localization) {
        this.id = id;
        this.description = description;
        this.completed = completed;
        this.date=date;
        this.hour=hour;
        this.picture=picture;
        this.lat=lat;
        this.lng=lng;
        this.localization=localization;
        this.name=name;
        System.out.println("TASK: id "+id+" nazwa "+name+ " termin " +date+ " h "+ hour 
                + " opis " +description + " zdjecie " + picture+" lat " + lat
                + " lng " +lng + " lokalizacja " +localization );
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public boolean isCompleted() {
        return completed;
    }

    public void setCompleted(boolean completed) {
        this.completed = completed;
    }
    public int getPicture() {
        return picture;
    }

    public void setPicture(int picture) {
        this.picture = picture;
    }
    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
    public String getHour() {
        return hour;
    }

    public void setHour(String hour) {
        this.hour = hour;
    }
    public Float getLat() {
        return lat;
    }

    public void setLat(Float lat) {
        this.lat = lat;
    }
    public Float getLng() {
        return lng;
    }

    public void setLng(Float lng) {
        this.lng = lng;
    }
    public String getLocalization() {
        return localization;
    }

    public void setLocalization(String localization) {
        this.localization = localization;
    }
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

When I open row that was already saved I get values in wrong columns. Example:

             Values to save            Saved values
name           name1                      name1
date           12/09/2015                 0
description    description1               Wrocław
picture        123                        13
lat            51.12708                   14.0
lng            16.991863                  51.12708
localization   Wrocław                    16.9919
hour           12:30                      123

Probably this is just a silly mistake which I can't see.

Improve this question
2
  • 1
    Instead of cursor.getString(NAME_COLUMN); try like cursor.getString(cursor.getColumnIndex(KEY_NAME))); Commented May 26, 2015 at 10:25
  • Accept the answer if it helps you to resolve your problem. Commented May 26, 2015 at 10:56

1 Answer 1

Reset to default
1

Your COLUMN INDEX is not matching with actual one.

private static final String DB_CREATE_MSDatabase_TABLE = "CREATE TABLE "
            + DB_MS_TABLE + "( " + KEY_ID + " " + ID_OPTIONS + ", " + KEY_NAME
            + " " + NAME_OPTIONS + ", " + KEY_DESCRIPTION + " "
            + DESCRIPTION_OPTIONS + ", " + KEY_COMPLETED + " "
            + COMPLETED_OPTIONS + ", " + KEY_DATE + " " + DATE_OPTIONS + ", "
            + KEY_PICTURE + " " + PICTURE_OPTIONS + ", " + KEY_HOUR + " "
            + HOUR_OPTIONS + ", " + KEY_LAT + " " + LAT_OPTIONS + ", "
            + KEY_LNG + " " + LNG_OPTIONS + ", " + KEY_LOCALIZATION + " "
            + LOCALIZATION_OPTIONS + ");";

//Your COLUMN_INDEX
public static final int ID_COLUMN = 0;           //Ok
public static final int NAME_COLUMN = 1;         //Ok
public static final int DESCRIPTION_COLUMN = 9;  //It Should Be 2
public static final int COMPLETED_COLUMN = 2;    //It Should Be 3
public static final int DATE_COLUMN = 3;         //It Should Be 4
public static final int PICTURE_COLUMN = 4;      //It Should Be 5
public static final int HOUR_COLUMN = 5;         //It Should Be 6
public static final int LAT_COLUMN = 6;          //It Should Be 7
public static final int LNG_COLUMN = 7;          //It Should Be 8
public static final int LOCALIZATION_COLUMN = 8; //It Should Be 9

Alternate Way: You can use getColumnIndex to get COLUMN INDEX

public int getColumnIndex (String columnName)

Returns the zero-based index for the given column name, or -1 if the column doesn't exist.
E.g.
cursor.getString(cursor.getColumnIndex(KEY_NAME)));

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

1 Comment

I have an issue updating ListAdapter to highlight search text here: stackoverflow.com/questions/79041428/…? I would appreciate any insights or ideas you might have on how to fix.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.