Blank ListView with multi-table SQLite database

I'm trying to populate a ListView with data from a relational database. What I am trying to accomplish is to show each nurse in NURSE_TABLE, and the room numbers that have been assigned to that nurse. What I'm getting as a result is the layout showing the two buttons, and an empty ListView.

The code to create the database is:

public void onCreate(SQLiteDatabase db) {
// create NURSE_TABLE
db.execSQL("CREATE TABLE " + NURSE_TABLE + " (" + KEY_NURSE_ROWID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " +          KEY_LNAME
        + " TEXT NOT NULL, " + KEY_FNAME + " TEXT NOT NULL, "
        + KEY_MI + " TEXT, " + KEY_EMAIL + " TEXT NOT NULL, "
        + KEY_PHONE_PRIMARY + " TEXT NOT NULL, "
        + KEY_PHONE_SECONDARY + " TEXT, " + KEY_FTE
        + " TEXT NOT NULL, " + KEY_DUTY_STATUS + " TEXT NOT NULL);");

// create ROOM_TABLE
db.execSQL("CREATE TABLE " + ROOM_TABLE + " (" + KEY_ROOM_ROWID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TYPE_PATIENT
        + " TEXT, " + KEY_OCCUPIED + " TEXT NOT NULL, "
        + KEY_ROOM_NUMBER + " TEXT NOT NULL, "
        + KEY_ASSIGNED_NURSE + " INTEGER, " 
        + " FOREIGN KEY (" + KEY_ASSIGNED_NURSE + ") REFERENCES " 
        + NURSE_TABLE + " (" + KEY_NURSE_ROWID + "));");
    } // onCreate

The code for the activity is:

public void displayNurseRoster(){

    ListView listView = (ListView) findViewById(R.id.list);

    // int variables filled with NURSE_TABLE data
    int[] to_nurseTable = new int[] {

            // int list for data from NURSE_TABLE fields
            R.id.rosterDutyStatus,
            R.id.rosterListLname, 
            R.id.rosterListFname, 
            R.id.rosterListMI, 
            R.id.rosterListID,

            // int list for data from ROOM_ROWID fields
            R.id.firstAssignment,
            R.id.secondAssignment,
            R.id.thirdAssignment,
            R.id.fourthAssignment,
            R.id.fifthAssignment,
            R.id.sixthAssignment };

    // String array holding data fields from NURSE_TABLE 
    String[] from_nurseTable = new String [] {

            // fields from NURSE_TABLE
            DbCommunicator.KEY_DUTY_STATUS,
            DbCommunicator.KEY_LNAME, 
            DbCommunicator.KEY_FNAME, 
            DbCommunicator.KEY_MI,
            DbCommunicator.KEY_NURSE_ROWID,
            DbCommunicator.KEY_ROOM_NUMBER}; 

    // instantiate instance of DbCommunicator object
    rosterView = new DbCommunicator(this);

    // open instance
    rosterView.open();

    // get & manage cursor for NURSE_TABLE data
    Cursor nurseTableCursor = rosterView.getNurseRosterCursor(this);
    startManagingCursor(nurseTableCursor);

    // instantiate cursor adaptor
    ListAdapter nurseTableAdapter = new SimpleCursorAdapter(this,
    R.layout.nurse_list, nurseTableCursor, from_nurseTable, to_nurseTable);

    nurseTableCursor.moveToNext();

    // set adapter to listView
    listView.setAdapter(nurseTableAdapter);
}// displayNurseRoster()

The code to generate the Cursor looks like this:

public Cursor getNurseRosterCursor (Context context) {

    SQLiteQueryBuilder nurseQueryBuilder = new SQLiteQueryBuilder();
    nurseQueryBuilder.setTables("NURSE_TABLE, ROOM_TABLE"); 

    String[] returnedColumns = new String[] {
            "Nurse_Table._id", 
            "Nurse_Table._dutyStatus",
            "Nurse_Table._lName",
            "Nurse_Table._FName",
            "Nurse_Table._mi",
            "Room_Table._id",
            "Room_Table._roomNumber"};

    Cursor nurseCursor = nurseQueryBuilder.query(sqldb, returnedColumns,
            null, null, null, null, null);

    return nurseCursor;
}

And finally my layout looks like this:

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="#81BEF7"
android:orientation="vertical" >

<Button
    android:id="@+id/btnAddNurseToRoster"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="@string/add" 
    android:focusable="true"/>

<Button
    android:id="@+id/btnRemoveNurseFromRoster"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignTop="@+id/btnAddNurseToRoster"
    android:layout_toRightOf="@+id/btnAddNurseToRoster"
    android:text="@string/delete" 
    android:focusable="true"/>

<ListView
    android:id="@+id/list"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:layout_below="@+id/btnAddNurseToRoster"
    android:textColor="#FFFFFF"
    android:textStyle="bold"/>

</RelativeLayout>

When I initially started, I was just populating the ListView with data from Nurses Table, which worked great, but have had a helluva time since I started trying to get the two tables to work together.

Any help / direction would be highly appreciated.

Answers


The two table in your setTables() call are not properly joined.

The from_nurseTable and to_nurseTable arrays do not match.

You have not mentioned what you want to show in the list view, but I'd guess it might be something like the result of this query:

SELECT nurse_table._id,_dutyStatus,_lName,_FName,_mi,group_concat(_roomNumber)
  FROM nurse_table LEFT JOIN room_table ON nurse_table._id = _assignedNurse
  GROUP BY nurse_table._id
  ORDER BY _lName,_FName /* or whatever */

When you do not know how to construct your SQL query, it is often more helpful to test queries in a copy of the database on your desktop. (There are many SQLite administrations tools, or as last restort use the sqlite(.exe) command-line tool.)


Need Your Help

Subclassed form not behaving properly in Designer view (VS 2008)

c# winforms visual-studio-2008

I have subclassed Form to include some extra functionality, which boils down to a List&lt;Image&gt; which displays in a set of predefined spots on the form. I have the following:

Free event tracking for iOS with raw data export

iphone ios ipad google-analytics analytics

I'm looking for a free analytics tool to track events on iOS.

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.