How do i display data from my database in a listview?

Question

Hi i got some three tables in my database. i want to be able to display the name and surname from the database in a listview.

1. first i open the database and add a client's name, surname and number. Then i close the database.
2. from here, i get confused and as to how i can display the name and surname in one line but in a listview leaving behind the number.

here is my database class called DBAdapter and the clientsActivity class where i want to be able to display the client i added in it.

i know its a bit simple but am done. No clue and in the end after following a number of tutorials, i display the data in a toastview manner which is not what i want.

kindly help....

my database class.(DBAdapter)

package com.android.ideos;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author G Sam

 *
 */



public class DBAdapter {
    //defining columns(fields) in all 3 tables
    public static final String KEY_CLIENTID = "_id";
    public static final String KEY_TRANSID = "transId";
    public static final String KEY_NAME = "name";
    public static final String KEY_SURNAME = "surname";
    public static final String KEY_MOBILE= "mobile"; 
    public static final String KEY_TYPE = "Type";
    public static final String KEY_DATETIME = "DateTime";
    public static final String KEY_AMOUNT = "Amount";
    public static final String KEY_BALANCE = "Balance";

    private static final String TAG = "DBAdapter";

    private static final String DATABASE_NAME = "radicalfinance";
    private static final String DATABASE_CLIENTSTable = "clientstable";
    private static final String DATABASE_TRANSACTIONS = "TransactionsTable";
    private static final String DATABASE_CLIENTSBALANCE = "ClientsBalanceTable";
    private static final int DATABASE_VERSION = 1;
 //Creating the database radicalfinance
    //CREATING CLIENTSTable
    private static final String DATABASE_CREATE_CLIENTSTABLE =
        "create table clientstable (_id integer primary key autoincrement, "
        + "name text not null, surname text not null, " 
        + "mobile integer not null);";
 //CREATING TransactionsTable    
    private static final String DATABASE_CREATE_TRANSACTIONSTABLE =
        "create table TransactionsTable (_id integer primary key autoincrement, "
        + "transId integer,"
        + "Type boolean not null, DateTime text not null, " 
        + "Amount long not null);";
 //CREATING ClientsBalanceTable
    private static final String DATABASE_CREATE_CLIENTSBALANCETABLE =
        "create table ClientsBalanceTable (_id integer primary key autoincrement, "
        + "Balance long not null); "; 


    private final Context context;  
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;


    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    public class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE_CLIENTSTABLE);
            db.execSQL(DATABASE_CREATE_TRANSACTIONSTABLE);
            db.execSQL(DATABASE_CREATE_CLIENTSBALANCETABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
                              int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                  + " to "
                  + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS clientstable");
            onCreate(db);
        }
    }    

    //methods for opening and closing the database, as well as the methods for adding/editing/deleting rows in the table.



   //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;

    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }

    //---insert a client and his info into the database---

    public long insertClient(String name, String surname, String mobile) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_SURNAME, surname);
        initialValues.put(KEY_MOBILE, mobile);
        return db.insert(DATABASE_CLIENTSTable, null, initialValues);
    }
    public long insertClientTransaction(String transId, String Type, String DateTime, String Amount) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TRANSID, transId);
        initialValues.put(KEY_TYPE, Type);
        initialValues.put(KEY_DATETIME, DateTime);
        initialValues.put(KEY_AMOUNT, Amount);
        return db.insert(DATABASE_TRANSACTIONS, null, initialValues);
    }
    public long insertClientBalance(String Balance) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_BALANCE, Balance);
        return db.insert(DATABASE_CLIENTSBALANCE, null, initialValues);
    }

    //---deletes a particular client---
    public boolean deleteClient(long clientId) 
    {
        return db.delete(DATABASE_CLIENTSTable,KEY_CLIENTID + "=" + clientId, null) > 0;
    }

    //---retrieves all the clients---
    public Cursor getAllClients() 
    {
        return db.query(DATABASE_CLIENTSTable, new String[] {
                KEY_CLIENTID, 
                KEY_NAME,
                KEY_SURNAME,
                KEY_MOBILE}, 
                null, 
                null, 
                null, 
                null, 
                null);

    }
    //querying TransactionsTable
    public Cursor getAllTransactionsRecords() {

        return db.query(DATABASE_TRANSACTIONS, new String[] {
                KEY_TRANSID, 
                KEY_TYPE,
                KEY_DATETIME,
                KEY_AMOUNT}, 
                null, 
                null, 
                null, 
                null, 
                null);
    }
    //made comments will be taken care of i.e the clientsbalanceRecords
        //querying clientsbalancetable
    //public Cursor getAllBalanceRecords() {

        //return db.query(DATABASE_CLIENTSBALANCE, new String[] {
                //KEY_BALANCE}, 
                //null, 
                //null);
    //}

    //---retrieves a particular client---
    public Cursor getClient(long clientId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DATABASE_CLIENTSTable, new String[] {
                        KEY_CLIENTID,
                        KEY_NAME, 
                        KEY_SURNAME,
                        KEY_MOBILE
                        }, 
                        KEY_CLIENTID + "=" + clientId, 
                        null,
                        null, 
                        null, 
                        null, 
                        null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    //---updates a client's details---
    public boolean updateClient(long clientId, String name, 
    String surname, String mobile) 
    {
        ContentValues args = new ContentValues();
        args.put(KEY_NAME, name);
        args.put(KEY_SURNAME, surname);
        args.put(KEY_MOBILE, mobile);
        return db.update(DATABASE_CLIENTSTable, args, 
                        KEY_CLIENTID + "=" + clientId, null) > 0;
    }
    public boolean updateTransactions(long clientId, long transId, String Type, 
            String DateTime, long Amount) 
            {
                ContentValues args = new ContentValues();
                args.put(KEY_TRANSID, transId);
                args.put(KEY_TYPE, Type);
                args.put(KEY_DATETIME, DateTime);
                args.put (KEY_AMOUNT, Amount);
                return db.update(DATABASE_TRANSACTIONS, args, 
                                KEY_CLIENTID + "=" + clientId, null) > 0;
            }

    public SQLiteDatabase getWritableDatabase() {
        // TODO Auto-generated method stub
        return null;
    }
}

then here is my ClientsActivity class

package com.android.ideos;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuInflater;
import android.view.MenuItem;
import android.widget.CursorAdapter;
import android.widget.ListAdapter;
import android.widget.Toast;
public class ClientsActivity extends ListActivity {


    protected DBAdapter db;
    protected CursorAdapter dataSource;
    protected ListAdapter adapter;
    //private static final String columns[] = { "name", "surname"};
    //private static final String TAGG = "ClientsActivity";



    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        db = new DBAdapter(this);
       /*DataBaseHelper helper = new DataBaseHelper(this);
        db = helper.getWritableDatabase();
        Cursor data = db.query("clientstable", columns,
                 null, null, null, null, null);
        dataSource = new SimpleCursorAdapter(this,
                 R.layout.clients, data, columns,
                 new int[] { R.id.name, R.id.surname });

        setListAdapter(dataSource);*/
        db.open();
        Long rowID = db.insertClient("Adera", "Dan", "0727858191");
        db.close();

        displayclients(rowID);

     }

    private void displayclients(long clientId) 
    **{
        // TODO Auto-generated method stub
        db.open();
        Cursor results = db.getClient(clientId);
        if (results.moveToFirst())
        {
            Toast.makeText(this, "Name: "+results.getString(1)+"  "+results.getString(2), Toast.LENGTH_LONG).show();
        }**





}

//calls the content menu layout
 @Override
 public boolean onCreateOptionsMenu(Menu menu) {
  MenuInflater myMenuInflater = getMenuInflater();
  myMenuInflater.inflate(R.menu.menu, menu);
     return true;
 }

 // the layout of the menu as seen in the menu.xml file
 @Override
 public boolean onOptionsItemSelected(MenuItem item) {
  // TODO Auto-generated method stub
  switch(item.getItemId())
  {
  // the menu button New Client and the functionality code will be implemented here.
   case(R.id.menu_new_client):
    Toast.makeText(this, "New client", Toast.LENGTH_LONG).show();


    break;

    // the menu button: Edit, and the functionality code will be implemented here.
   case(R.id.menu_edit):
    Toast.makeText(this, "Edit", Toast.LENGTH_LONG).show();
    break; 

    // the menu button: DElete, and the functionality code will be implemented here.
   case(R.id.menu_delete):
    Toast.makeText(this, "Delete", Toast.LENGTH_LONG).show();


    break;

    // the menu button: Search, and the functionality code will be implemented here.
   case(R.id.menu_search):
       Toast.makeText(this, "Search", Toast.LENGTH_LONG).show();
       break;
  } 
  return true;
 }
}

Solution

I can't see anywhere that you are assigning your database cursor to the ListView via a ListAdapter (CursorAdapter).

I think you need to do the google Notepad tutorials do all 3 but even just Notepad1 Tutorial explains about linking a SimpleCursorAdapter to a ListView. Look at this tutorial and pay particular attention to the fillData() method

private void fillData() {
    // Get all of the notes from the database and create the item list
    Cursor c = mDbHelper.fetchAllNotes();
    startManagingCursor(c);

    String[] from = new String[] { NotesDbAdapter.KEY_TITLE };
    int[] to = new int[] { R.id.text1 };

    // Now create an array adapter and set it to display using our row
    SimpleCursorAdapter notes =
        new SimpleCursorAdapter(this, R.layout.notes_row, c, from, to);
    setListAdapter(notes);
}

I hope that helps

Comments

[Gsam]

@wired00, should this database cursor be in the private void displayclients(long clientId) as i have done here?? private void displayclients(long clientId) { // TODO Auto-generated method stub db.open(); Cursor results = db.getClient(clientId); if (results.moveToFirst()) { Toast.makeText(this, "Name: "+results.getString(1)+" "+results.getString(2), Toast.LENGTH_LONG).show(); }

[wired00]

Just make sure to look at the tutorial. Yes you can think of your displayClients() similar to the google notepad tutorial fetching data in the fillData() method. I amended my answer with the code

[Gsam]

thanks WiredOO, i hope that helps. will also look at the google notepad tutorials

[Gsam]

thanks Wired00, been out for long but this helped me alot. Thumbs up.thanks

[wired00]

i've been away from Stack overflow a few days, sorry for late reply. Great to hear that helped you out :)