to get the no of records from my database and store it in an integer

Question

I want to get the count of the records from the database onto my app in an integer then display it... how do i do that???

i am not able to do so

here is the database code

public int counttable()
{
    int count=0;
    openOrCreateDatabase();

    count=db.execSQL("select count(*) from "+TableNameis+";");

    return count;

}

i know that the datatypes dont match...can anyone please suggest how do i do that?? how do i store the count value in an integer variable.

Dashboard.java

public class Dashboard extends Activity {

EditText edt_pending, edt_completed,edt_synched;

protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);

    requestWindowFeature(Window.FEATURE_NO_TITLE);

    setContentView(R.layout.dashboard);

    edt_pending=(EditText)findViewById(R.id.editpending);
    edt_completed=(EditText)findViewById(R.id.editcompleted);
    edt_synched=(EditText)findViewById(R.id.editsynched);

    WayDataBase way=new WayDataBase(Dashboard.this);
    int count=way.counttable();

    edt_completed.setText(count);


}

}

logcat

04-08 07:15:02.216: E/AndroidRuntime(16026): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.android.lthomepage/com.android.lthomepage.Dashboard}: android.content.res.Resources$NotFoundException: String resource ID #0x1 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.ActivityThread.access$600(ActivityThread.java:141) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.os.Handler.dispatchMessage(Handler.java:99) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.os.Looper.loop(Looper.java:137) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.ActivityThread.main(ActivityThread.java:5039) 04-08 07:15:02.216: E/AndroidRuntime(16026): at java.lang.reflect.Method.invokeNative(Native Method) 04-08 07:15:02.216: E/AndroidRuntime(16026): at java.lang.reflect.Method.invoke(Method.java:511) 04-08 07:15:02.216: E/AndroidRuntime(16026): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793) 04-08 07:15:02.216: E/AndroidRuntime(16026): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560) 04-08 07:15:02.216: E/AndroidRuntime(16026): at dalvik.system.NativeStart.main(Native Method) 04-08 07:15:02.216: E/AndroidRuntime(16026): Caused by: android.content.res.Resources$NotFoundException: String resource ID #0x1 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.content.res.Resources.getText(Resources.java:230) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.widget.TextView.setText(TextView.java:3640) 04-08 07:15:02.216: E/AndroidRuntime(16026): at com.android.lthomepage.Dashboard.onCreate(Dashboard.java:27) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.Activity.performCreate(Activity.java:5104) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080) 04-08 07:15:02.216: E/AndroidRuntime(16026): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)

Answer 1

Try this code:

public int counttable()
{
int count=0;
openOrCreateDatabase();
Cursor c = db.rawQuery("select * from your_table_name",null);
count=c.getCount();
return count;
}

Also,

edt_completed.setText(String.valueOf(count));

Using SELECT query is enough, then count the size of Cursor..

Answer 2

1. Create One Class for DatabaseCreate Like this..

   public class WayDataBase extends SQLiteOpenHelper {

   private static String DATABASE_NAME = "Database Name";
   private SQLiteDatabase myDataBase;
   private Context myContext;
   private String DATABASE_PATH = "/data/data/"Package Name"/databases/";
   
   
   // Constructor  
   
   public WayDataBase (Context context) 
   {
   
       super(context, DATABASE_NAME, null, 1);
       this.myContext = context;
   }
   
   // Create DataBase 
   
   public void createDatabase() throws IOException
   {
   
       boolean dbExist = checkDataBase();
   
       if(dbExist)
       {
       }
       else
       {
   
           this.getReadableDatabase();
   
           try 
           {
               this.close();   
               copyDataBase();
           } 
           catch (IOException e) 
           {
               throw new Error("Error copying database");
           }
       }
   
   }
   
   // check Database Existing or not
   
   private boolean checkDataBase() 
   {
       SQLiteDatabase checkDB = null;
   
       try
       {
           String myPath = DATABASE_PATH + DATABASE_NAME;
           checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
   
       }
       catch(SQLiteException e)
       {
       }
   
       if(checkDB != null)
       {
   
           checkDB.close();
   
       }
   
       return checkDB != null ? true : false;
   
   }
   
   // Copy DataBase from assets to SD card 
   
   private void copyDataBase() throws IOException
   {
   
       String outFileName = DATABASE_PATH + DATABASE_NAME;
   
   
       OutputStream myOutput = new FileOutputStream(outFileName);
       InputStream myInput = myContext.getAssets().open(DATABASE_NAME);
   
       byte[] buffer = new byte[1024];
       int length;
       while ((length = myInput.read(buffer)) > 0) 
       {
           myOutput.write(buffer, 0, length);
       }
   
       myInput.close();
       myOutput.flush();
       myOutput.close();
   }
   
   // Open DataBase
   
   public void openDatabase() throws SQLException
   {
   
       String myPath = DATABASE_PATH + DATABASE_NAME;
       myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
   
   }
   public int counttable()
   {
       int count=0;
       openDatabase();
       Cursor c = myDataBase.rawQuery("SELECT * FROM table_name",null);
       return count;
   }
   

   }

Now in your Activity

public class Dashboard extends Activity {

private WayDataBase way;

EditText edt_pending, edt_completed,edt_synched;

protected void onCreate(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onCreate(savedInstanceState);

    requestWindowFeature(Window.FEATURE_NO_TITLE);

    setContentView(R.layout.dashboard);

    edt_pending=(EditText)findViewById(R.id.editpending);
    edt_completed=(EditText)findViewById(R.id.editcompleted);
    edt_synched=(EditText)findViewById(R.id.editsynched);


    way= new WayDataBase(this);
    try 
    {
        way.createDatabase();

    } catch (IOException ioe) 
    {
        throw new Error("Unable to create database");
    }


    int count=way.counttable();

    edt_completed.setText(count+"");


}

}

Answer 3

You can use the Cursor#getCount() to get the count of the no. of records.

public int counttable()
{
    int count=0;
    openOrCreateDatabase();

    // count=db.execSQL("select * from "+TableNameis+";"); - This statement is invalid

    // Use this instead
    count=db.rawQuery(selectionQuery, null).getCount(); 

    return count;

}