Database retrieval in android

Question

In my application i am showing data from database in a table view.My requirement is that from database i have to retrieve the data which will fall in the current month.I Have written the query but it is coming as 0.Actually i have 1 entry in the database with today's date,so my query should return that data,but it is showing as 0.Please help me.Thanks in advance.

My query is as follows:

public String addgroupincome(String grp) throws SQLException
{   
    long sum=0; 
    Cursor cursor1 = db.rawQuery(
             "SELECT SUM("+(KEY_TOTAL)+") FROM incomexpense WHERE date= Strftime('%Y-%m','now') AND category='Income' AND groups='"+grp+"'",null);
     if(cursor1.moveToFirst())
     {
       sum = cursor1.getLong(0);
     }
     cursor1.close();   
     String housetotal=String.valueOf((long)sum);    
     return housetotal; 
}

I am getting that total and showing in atextview in table layout..

final  String houtotal=db.addgroupincome(group1);  
     housetotal.setText(houtotal);

It's mean that you are getting the data from the database. Now check how you are dealing with the data. — Narendra Pal, Aug 01 '12 at 05:04
Nick That is the problem...Now i just summed up the total column and returned that..But it is coming as 0 y?????? — prakash .k, Aug 01 '12 at 05:06
Nick one more thing..Now i added 1 more data in today's date.So 2 datas are available in database with today's date,now the cursor count should return as 2 ,but now also it is coming as 1.. — prakash .k, Aug 01 '12 at 05:09
Dear. I have less knowledge of query. First make sure that your query is confirmed right. — Narendra Pal, Aug 01 '12 at 05:15
Ya i also have doubt in that only..I am also little bit new to this..But another person say's its correct.. — prakash .k, Aug 01 '12 at 05:18
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14724/discussion-between-nick-and-prakash-k) — Narendra Pal, Aug 01 '12 at 05:19
@prakash, you might need sqlite browser to help you with your query.Try http://sqlitebrowser.sourceforge.net/ or http://www.sqliteexpert.com/ — stuckedunderflow, Aug 01 '12 at 05:34

score 0 · Answer 1 · edited May 23 '17 at 10:24

Most probably nothing wrong with the query but the way you pass the query result to ListView. Can you show how you do it? Perhaps I could help.

Or you could take a look here or here

public int getCount() {
    DBHelper dbHelper = DBHelper.getDBAdapterInstance(this);
    int count = 0;

    try {
        dbHelper.openDataBase();

        String query = "select count(1) from t_model where upper(brandName) = upper('"
                + selectedBrand + "') order by modelName ASC";

        Cursor cursor = dbHelper.selectRecordsCursor(query, null);

        if (cursor.moveToFirst()) {
            count = cursor.getInt(0);
        }

        cursor.close();
        cursor = null;

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        dbHelper.close();
    }

    return count;
}

and for the TextView should be as simple as

TextView tvCount = (TextView) findViewById(R.id.tvCount);
tvCount.setText("Count : " + getCount);

If you are having trouble debugging your query. Try http://sqlitebrowser.sourceforge.net/ or http://www.sqliteexpert.com/

Sorry halim..not listview its table layout...sorry i done a mistake in my question..Anyway i will post my code ,.. — prakash .k, Aug 01 '12 at 04:56
Ok first make sure what value for String housetotal return 1 as expected. TableLayout should also be able to show value from db. — stuckedunderflow, Aug 01 '12 at 05:01
Halim...The housetotal should return the sum value,and i have to display that in a textview.. — prakash .k, Aug 01 '12 at 05:05

score 0 · Answer 2 · answered Aug 01 '12 at 05:48

0

Why don't you try by giving column names of your table in your query..might it work out for you..specify the columns which you want to retrive..

answered Aug 01 '12 at 05:48

Rekha

901
1
11
20

score 0 · Answer 3 · edited Aug 01 '12 at 12:13

0

if (cursor.moveToNext()) {
  cursor.moveToFirst();
  while (cursor.isAfterLast() == false) {
    Log.i(ID, cursor.getInt(0) + "");
    cursor.moveToNext();
  }
  cursor.close();
} else {
  cursor.close();
  return null;
}

Try This Method....

edited Aug 01 '12 at 12:13

legoscia

39,593
22
116
167

answered Aug 01 '12 at 06:00

J.K

2,290
1
18
29

Database retrieval in android

3 Answers3