Setting initial column value in SQLite database to 0

Question

I am making an app where I have a certain checkbox called "Full tank". If i check this box i have to calculate a value mileage and save it into the database. If the box in not checked the mileage is not calculated and 0 is stored in the mileage column. However, When I make the first entry into my database I want the mileage to be 0 irrespective of the checkbox being checked or not.

Can anyone help me with the idea? Heres something I have tried uptil now, but this code generates the mileage for the first row as well:-

    public void calculateMileage(FuelExpense fe) 
    {

        // TODO Auto-generated method stub
        String [] columns = new String[]{KEY_ROW_ID, KEY_KM, KEY_FUEL_QTY, KEY_FUEL_PRICE, KEY_TOTAL_COST, KEY_MILEAGE, KEY_DATE,KEY_TANK_FULL};

        long numRows = DatabaseUtils.queryNumEntries(ourDatabase, "fuel_table");
        if(fe.getTankFullstatus()==true)
        {
            mileageCursor = ourDatabase.rawQuery("SELECT * FROM fuel_table ORDER BY _id DESC LIMIT 1", null);

            if(mileageCursor!=null && mileageCursor.getCount()>0)
            {

                mileageCursor.moveToLast();

                String lastKm = mileageCursor.getString(1);
                String lastFuelQty = mileageCursor.getString(2);

                lastDBFuelEntry.setKm(Long.parseLong(lastKm));
                lastDBFuelEntry.setFuelQty(Double.parseDouble(lastFuelQty));
            }
            else
            {
                mileage = 0.0d;
            }

        }
        else
        {

            for(mileageCursor.moveToLast();!mileageCursor.isBeforeFirst();mileageCursor.moveToPrevious())
            {
                nftQty = nftQty+lastDBFuelEntry.getFuelQty();

            }

        }
        fuelUsed = nftQty + fe.getFuelQty();
        mileage =(fe.getKm() - lastDBFuelEntry.getKm())/ fuelUsed;
        mileage= (double)(Math.round(mileage*100))/100;
        fe.setMileage(mileage);
        }

Table creation:-
//Fuel info table
            db.execSQL( "CREATE TABLE "+FUEL_DATABASE_TABLE +" (" +
                    KEY_ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    KEY_KM + " LONG NOT NULL, "+
                    KEY_FUEL_QTY + " INTEGER NOT NULL, "+
                    KEY_FUEL_PRICE + " DOUBLE NOT NULL, "+
                    KEY_TOTAL_COST + " DOUBLE NOT NULL, "+
                    KEY_MILEAGE + " DOUBLE DEFAULT 0, "+
                    KEY_DATE + " DATE NOT NULL, "+
                    KEY_TANK_FULL + " TEXT);"
                    );

Insert:-
{
        // TODO Auto-generated method stub


        ContentValues cv = new ContentValues();
        cv.put(KEY_KM, fe.getKm());
        cv.put(KEY_FUEL_QTY, fe.getFuelQty());
        cv.put(KEY_FUEL_PRICE,fe.getFuelPrice());
        cv.put(KEY_TOTAL_COST, fe.getTotalCost());
        cv.put(KEY_MILEAGE, fe.getMileage());
        cv.put(KEY_DATE, fe.getFuelFilledDate());
        cv.put(KEY_TANK_FULL, fe.getTankFullstatus());
        return ourDatabase.insert(FUEL_DATABASE_TABLE, null, cv);
    }

what kind of database? It is possible for some database to have an default value — lordkain
– lordkain, Commented Aug 14, 2013 at 5:18
@Tarun I have added some more code for clarity..Kindly have a look — Pooja Gaonkar
– Pooja Gaonkar, Commented Aug 14, 2013 at 5:52
When you are inserting for the first time either dont fill mileage in the content value or fill it with 0. — Tarun
– Tarun, Commented Aug 14, 2013 at 5:56

HalR · Accepted Answer · 2013-08-14 06:24:45Z

1

If I understand you correctly,

If you have only one entry in your database, you want to not calculate, and just show a zero.

I think all you need to do is change this:

if(mileageCursor!=null && mileageCursor.getCount()>0

to this:

if(mileageCursor!=null && mileageCursor.getCount()>1

And then, if you only have one entry in your database, the resultant calculation will be 0. I would assume your code is freaking out because you only have one entry, yet its trying to call the moveToPrevious() on your cursor.

answered Aug 14, 2013 at 6:24

HalR

11.1k5 gold badges51 silver badges86 bronze badges

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

1 Comment

Pooja Gaonkar Over a year ago

Thank you for a nice explanation. I tried out the soultion by harish below and that seemed to work as well. Thank you for you time though

Harish Godara · Accepted Answer · 2013-08-14 06:19:37Z

0

You can check if it is first row like :

//Fetch row count in FUEL_DATABASE_TABLE before insertion
if(ROW_COUNT == 0)
    Insert into FUEL_DATABASE_TABLE (... KEY_MILEAGE ...) values (... 0 ...);
else
    // Insert what you want

answered Aug 14, 2013 at 6:19

Harish Godara

2,3861 gold badge15 silver badges28 bronze badges

Collectives™ on Stack Overflow

Setting initial column value in SQLite database to 0

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related