Failing to query the maximum number in database table

I am trying, unsucessfully, to query my database to find the maximum 'area number', in my areas table for a certain inspection, so that I can set the text in a form to the next area number.

The database table consists of four columns; _id, inpsection_link, area_number, area-reference.

I have created the following in my database helper class (using this post as a guide: SQLIteDatabase.query method):

 public int selectMaxAreaNumber (long inspectionId) {
    String inspectionIdString = String.valueOf(inspectionId);
    String[] tableColumns = new String[] { 
            AREA_NUMBER, 
            "(SELECT max(" + AREA_NUMBER + ") FROM " + AREAS_TABLE + ") AS max" 
        }; 
    String whereClause = INSPECTION_LINK + " = ?";
    String[] whereArgs = new String[] {
            inspectionIdString          
        };
    Cursor c = rmDb.query(AREAS_TABLE, tableColumns, whereClause, whereArgs, 
            null, null, null);
    int maxAreaNumber = c.getColumnIndex("max");
    return maxAreaNumber;
}

Which I then call in the areaEdit class as follows:

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    rmDbHelper = new RMDbAdapter(this);
    rmDbHelper.open();
    Intent i = getIntent();
    inspectionId = i.getLongExtra("Intent_InspectionID", -1);
    areaId = i.getLongExtra("Intent_AreaID", -1);
    if (areaId == -1) {
        nextAreaNumber = rmDbHelper.selectMaxAreaNumber(inspectionId) + 1;
        Toast.makeText(getApplicationContext(), String.valueOf(nextAreaNumber), 
                Toast.LENGTH_LONG).show();  
    }
    setContentView(R.layout.edit_area);
    setUpViews();
    populateFields();
    setTextChangedListeners();
}

However, it just returns 1 everytime (even if there are numbers higher than that stored in the database).

Confused.com!! Any help much appreciated.

Answers


Your issue is here :

int maxAreaNumber = c.getColumnIndex("max");

You're getting the column index of max, which is 1 because you only have one column in your query. Instead, do something like this :

int maxAreaNumber = 0;
if(c.moveToFirst())
{
    maxAreaNumber = c.getInt(1);
    // or cleaner
    maxAreaNumber = c.getInt(c.getColumnIndex("max"));
}
else
    // no data in cursor

Need Your Help

Being innovative (in Web Design/Development) while maintaining backward-compatibility

javascript html css html5 css3

I love building websites, and I love making them compatible with as many different platforms, devices and browsers as possible, but I am really getting sick of it nowadays to be honest.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.