Select Statement Not Returning Max Number

I'm having an issue with the SELECT statement in the following code (this is in my database helper class): public Cursor selectMaxAreaNumber (long inspectionId) { String inspectio

Solution 1:

Try this instead:

public Cursor selectMaxAreaNumber (long inspectionId) { 
    String[] tableColumns = newString[] {  
        "Max(" + AREA_NUMBER + ") AS max"  
    };  
    String whereClause = INSPECTION_LINK + " = ?"; 
    String[] whereArgs = newString[] { 
        String.valueOf(inspectionId); 
    }; 
    return rmDb.query(AREAS_TABLE, tableColumns, whereClause, whereArgs,  
        null, null, null); 
} 

This will return a Cursor with the largest AREA_NUMBER that has the appropriate inspectionId.

A couple notes:

• When you use a function like Max() you only get one row as a result, there is no need to ask for AREA_NUMBERSandMax(AREA_NUMBERS)
• A Cursor might be empty, but it won't be null
• Don't close a Cursor before you have used it

So you don't need this:

if (c != null) { 
    c.moveToFirst(); 
} 
c.close(); 

Understand that if c somehow was null, you will still receive a NullPointerException on c.close()

---

(Optional) You could remove the whereArgs and just use:

StringwhereClause= INSPECTION_LINK + " = " + inspectionId;

(Only because inspectionId is long data type, you need a String to perform an injection attack.)

---

Finally you should check for an empty Cursor here:

Cursor c = rmDbHelper.selectMaxAreaNumber(inspectionId); 
startManagingCursor(c); 
if(c.moveToFirst()) 
    nextAreaNumber = c.getInt(c.getColumnIndex("max")) +1;
else//emptyCursor, return a defaultvalue
    nextAreaNumber =0;

Solution 2:

Try change

"(SELECT max(" + AREA_NUMBER + ") FROM " + AREAS_TABLE + ") AS max"

to

"SELECT max(" + AREA_NUMBER + ") AS max FROM " + AREAS_TABLE