Android SQLite Query of an Integer

Question

I'm trying to count the number of rows in a table that have year and month that fall within a certain range. For some reason, I'm always getting zero.

Any idea where I'm going wrong?

Main code

//define Report Start and End Dates
        // Get due date
        Calendar curDate = Calendar.getInstance();
        Calendar defaultStart = Calendar.getInstance();
        defaultStart.set(curDate.get(Calendar.YEAR), curDate.get(Calendar.MONTH)-1, curDate.get(Calendar.DAY_OF_MONTH));
        String curYear = ((Integer)curDate.get(Calendar.YEAR)).toString();
        String curMonth = ((Integer)(curDate.get(Calendar.MONTH)+1)).toString();
        String curDay = ((Integer)curDate.get(Calendar.DAY_OF_MONTH)).toString();
        if(curDate.get(Calendar.MONTH)+1 < 10)
            curMonth = "0"+((Integer)(curDate.get(Calendar.MONTH)+1)).toString();
        String defaultYear = ((Integer)defaultStart.get(Calendar.YEAR)).toString();
        String defaultMonth = ((Integer)(defaultStart.get(Calendar.MONTH)+1)).toString();
        String defaultDay = ((Integer)defaultStart.get(Calendar.DAY_OF_MONTH)).toString();
        if(defaultStart.get(Calendar.MONTH)+1 < 10)
            defaultMonth = "0"+((Integer)(defaultStart.get(Calendar.MONTH)+1)).toString();
        reportEndDate = curYear+"-"+curMonth+"-"+curDay;
        reportStartDate = defaultYear+"-"+defaultMonth+"-"+defaultDay;

int numEntries = StatisticsAdapter.getNumEntriesByDate(startYear, startMonth, endYear, endMonth);
Toast.makeText(ReportsByDateScreen.this, "numEntries="+numEntries, Toast.LENGTH_LONG).show();

StatisticsAdapter

public int getNumEntriesByDate(int startYear, int startMonth, int endYear, int endMonth)
{
    int count = 0;
    int year = startYear;
    int month = startMonth;
    String query = "select * from STATISTICS where YEAR = "+year+" and MONTH = "+month;
    while(year <= endYear)
    {
        if(year != endYear)
        {
            Cursor cursor = db.rawQuery(query, null);
            if(cursor.getCount()>=1) // At least 1 items found in given month and year
                count++;
            cursor.close();  
        }
        else if(year == endYear && month <= endMonth)
        {
            Cursor cursor = db.rawQuery(query, null);
            if(cursor.getCount()>=1) // At least 1 items found in given month and year
                count++;
            cursor.close();  
        }
        month++;
        if(month > 12)
        {
            year++;
            month = 1;
        }
    }

    return count;
}

My Toast always indicates that "numEntries=0"

please have a log for startYear, startMonth, endYear, endMonth and see what was there value before using them — Jitesh Upadhyay
– Jitesh Upadhyay, Commented Jan 14, 2014 at 6:19

Rohan Kandwal · Accepted Answer · 2014-01-14 06:46:05Z

3

You must use

cursor=db.rawQuery("Select count(*) from STATISTICS where YEAR = "+year+" and MONTH = "+month", null);

to count the number of values and then use cursor.getCount() to get the number of records returned.

answered Jan 14, 2014 at 6:46

Rohan Kandwal

9,3268 gold badges77 silver badges112 bronze badges

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

Comments

Nitesh Tiwari · Accepted Answer · 2014-01-14 06:19:11Z

2

You should count the values Like this below:

String query = "select count(*) from STATISTICS where YEAR = "+year+" and MONTH = "+month;

Instead of :

String query = "select * from STATISTICS where YEAR = "+year+" and MONTH = "+month;

On Getting the Cursor Result Just do Interger.parseInt(//your string) and you can get the Count

Hope this could help...

answered Jan 14, 2014 at 6:19

Nitesh Tiwari

4,7623 gold badges31 silver badges46 bronze badges

Collectives™ on Stack Overflow

Android SQLite Query of an Integer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related