DataRelation between two data tables - can't bind data to a listbox (C# .NET 3.5)

Question

C# (.NET 3.5)

I have an SQLite database with two tables - employees (the first column being id INTEGER PRIMARY KEY), and holidays (id INTEGER - meaning the ID of the employee; start DATE, end DATE - self-explanatory).

My form contains textboxes, checkboxes etc. to represent the employee details - but say I'd also like a list box, listing all the holidays for the currently selected employee.

So I need a data relation, as the list box is supposed to display only holidays one person at the time (IDs on both datatables needs to match).

        var command = new SQLiteCommand("SELECT * FROM employees; SELECT * FROM holidays");
        var connection=new SQLiteConnection(@"data source=C:\employees.db");
        command.Connection = connection;
        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
        DataSet d = new DataSet();
        adapter.Fill(d);                        

        DataTable employees = d.Tables[0];

        // [...]
        // here I'm databinding my textboxes etc. to various columns 
        // of the employees data table - this works fine, navigation works well etc.
        // [...]

        DataTable holidays = d.Tables[1];            

        DataRelation relation;
        DataColumn master = employees.Columns[0];
        DataColumn slave = holidays.Columns[0];
        relation = new DataRelation("relation", master, slave);
        d.Relations.Add(relation);

        var dsView = holidays.DefaultView;
        listBox1.DisplayMember = "holidays.relation.start"; // <= it wouldn't look good like that of course, but I just want to get the thing to work for now
        listBox1.DataSource = dsView;

• but all I get is a listbox filled up with a bunch of "System.Data.DataRow" In this implementation, I tried to follow the tutorial I found on Akadia... Where do I go wrong? Thanks

Answer 1

First of all, you have named variables employees and holidays, but that does mean the tables within the dataset have names changed; they are still set to the default names, which, in your case are table and table1. (They would normally be table, table1 ... tableN) That means that your display member property should be as follows:

listBox1.DisplayMember = "table.relation.start";

Also, make sure you databind to the dataset itself rather than the holiday table within the dataset as follows:

listBox1.DataSource = d;

With these two changes, you should find that it works; this.BindingContext should help in navigating the parent records as you will notice only 1 set of child (holiday) results are shown at at time.

Complete modified code as follows:

    var command = new SQLiteCommand("SELECT * FROM employees; SELECT * FROM holidays");
    var connection=new SQLiteConnection(@"data source=C:\employees.db");
    command.Connection = connection;
    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
    DataSet d = new DataSet();
    adapter.Fill(d);                        

    DataTable employees = d.Tables[0];

    // [...]
    // here I'm databinding my textboxes etc. to various columns 
    // of the employees data table - this works fine, navigation works well etc.
    // [...]

    DataTable holidays = d.Tables[1];

    DataRelation relation;
    DataColumn master = employees.Columns["id"];
    DataColumn slave = holidays.Columns["employeeid"];
    relation = new DataRelation("relation", master, slave);
    d.Relations.Add(relation);


    listBox1.DisplayMember = "table.relation.start"; // <= it wouldn't look good like that of course, but I just want to get the thing to work for now
    listBox1.DataSource = d;