SQLiteException using WHERE " +KEY_Date+"='"+date+"'"

Question

This is on my selectedDayChange on my mainactivity.java

date= year +""+ month +""+ dayOfMonth;

                    allfood food = new allfood();
                    food.Date="DATE_"+date;
                    double a = repo.totalFat(food);
                    Toast.makeText(getApplicationContext(), "" +a, Toast.LENGTH_LONG).show();

While this is on my repo.java

public double totalFat(allfood date){


        SQLiteDatabase db = dbHelper1.getReadableDatabase();

        String query = "SELECT SUM(Fat) FROM " +allfood.TABLE+ " WHERE " +KEY_Date+"="+date;

        Cursor c = db.rawQuery(query, null);

        c.moveToFirst();
        double i=c.getDouble(0);

        return i;

    }

Then it shows an error, by the way, I know I needed to make something like this: KEY_Date+"='"+date+"'"

String query = "SELECT SUM(Fat) FROM " +allfood.TABLE+ " WHERE " +KEY_Date+"='"+date+"'";

android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: SELECT SUM(Fat) FROM allfood WHERE Date=info.androidhive.navigationdrawer.Overall.allfood@1cbb35e1

This line

Date=info.androidhive.navigationdrawer.Overall.allfood@1cbb35e1

should be

Date=DATE_20170213

How can I fix this?

OneCricketeer · Accepted Answer · 2017-02-13T14:59:43.733

0

1) Don't add a literal allfood object to a String. SQL can't interpret a Java object.

The method should be any of the following because allfood is the whole object, you do need it as the parameter. And naming it as date is simply confusing.

totalFat(Date date)
totalFat(String date)
totalFat(Calendar date)
totalFat(int year, int month, int dayOfMonth)

should be
Date=DATE_20170213

2) No, it really shouldn't because Sqlite does not support that format of dates. Additionally, pre-pending DATE_ is just wasting storage in your database.

3) Please do not use this

date= year +""+ month +""+ dayOfMonth

Build a Calendar object and use SimpleDateFormat to correctly get a date formatted string.

using the last option above, you'd have something like this

SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
Calendar calendar = Calendar.getInstance();
calendar.set(year, month, dayOfMonth);
String queryForDate = fmt.format(calendar.getTime());
// db.query(TABLE_NAME, null, new String[] {...  // TODO: Complete this

edited Feb 13 '17 at 14:59

answered Feb 13 '17 at 13:37

OneCricketeer

179,855
19
132
245

I read at somewhere that columns doesnt accept all number like 20170213 (Which is the date today) so I added "DATE_" But as what you've said, i reformatted my date as 2017-02-13, I have the same error, I think i need to wrap or pass the value. What do you think? – John Mark Delos Reyes Feb 13 '17 at 14:01
Oh, sorry I types really slow, wait sir, im gonna use the code you suggested – John Mark Delos Reyes Feb 13 '17 at 14:06
Don't just copy paste what I have. Try to understand why I'm doing it – OneCricketeer Feb 13 '17 at 14:08
1

OMG! You are a lifesaver! My code works! I really awe you a lot. Ive been struggling for almost 2 days just for this! Thankyou again sir. I hope I will be as good as you in terms of programming. Thankyou again sir! – John Mark Delos Reyes Feb 13 '17 at 14:56

SQLiteException using WHERE " +KEY_Date+"='"+date+"'"

1 Answers1

Linked