CODEWITHSUNDEEP
androidsqlite
erad
erad

Reputation: 1786

Android SQLite Query of an Integer

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"

Upvotes: 0

Views: 754

Answers (2)

Rohan Kandwal
Rohan Kandwal

Reputation: 9326

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.

Upvotes: 3

Nitesh Tiwari
Nitesh Tiwari

Reputation: 4762

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...

Upvotes: 2

Related Questions