Database/Control Binding issues in C#

The Corinthian

C# and .Net arn't my speciality, although, I'll have to say I'm enjoying working with C# and Visual Studio a fair bit (reminds me of my VB5 days...).

Anyhow, I have a local SQL Server CE database, with two tables; first is accounts, the second is transactions. The accounts table allows for sub accounts; each account has a parent_id field that corresponds to a potential parent account_id in the same table (with '0' denoting the top level accounts). The transactions table is linked via a reference to account_id.

To manage and navigate all this, the UI has a combobox (for the parent accounts), a listbox (for the sub-accounts to the selected account above) and a datagrid for the transactions.

So the issue is binding the listbox to the combobox selection, and in turn the datagrid to the listbox selection. The approach I took was:

 private void cAccounts_SelectedIndexChanged(object sender, EventArgs e)
        {
                BindingSource bs = new BindingSource();
                bs.DataSource = from x in abbDBDataSet.accounts where x.account_parent_id == (int)cAccounts.SelectedValue select x;
                lSubAccounts.DataSource = bs;

Which isn't working, instead filling the listbox with an object and/or freezing the app. I've looked on the Web for a solution and come up with dozens that don't seem to help.

So:

• Where am I going wrong?
• Can one recommend a good, clear tutorial on this sort of thing?

awec

So, I guess the app could be freezing because you are doing the call to the database on the UI thread and it is taking time to execute?

Have you tried stepping through the method? Stick a breakpoint in it and see if the .DataSource property is being properly populated.

If you are getting strings like "object" in your listbox - check that you have set the listboxes view property up correctly so it knows what property of the bound object to display (otherwise I guess it just calls ToString). I think the property is called "DisplayMember".

Databinding in winforms is a bit ewww and a bit old. Not sure if any of this will be of any help.

The Corinthian

Thanks for the reply awec.

Databinding in winforms has always been a bit ewww. In VB3+ it was a bit of a quick and dirty way of setting up controls bound to simple tables, but it always became more trouble than it was worth the moment you started trying to do anything even slightly more complex and so most didn't even bother and would programmatically employ ADO or ODBC instead.

I'd kind of hoped that .Net had cracked this and perhaps it has, but ultimately it's still more trouble than it's worth and, while still binding the data, I went the coded route, rather than letting Visual Studio do it for me, instead:

private void UpdateSubaccountsDisplay(int parentID)
        {
            SqlCeConnection conn = new SqlCeConnection(Lib.ConnectionString);
            string sql = "SELECT * FROM accounts WHERE account_parent_id = " + parentID;
            SqlCeDataAdapter da = new SqlCeDataAdapter(sql, conn);
            conn.Open();
            
            DataSet ds = new DataSet();
            da.Fill(ds, "accounts");
            lSubAccounts.DisplayMember = "account_label";
            lSubAccounts.ValueMember = "account_id";
            lSubAccounts.DataSource = ds.Tables["accounts"];

            conn.Close();
        }

Naturally methods for the other controls were also required.

As to 'DisplayMember' and 'ValueMember', of which you mentioned the former, these are important, as they allow one to add a key-value pair per list row to either comboboxes or listboxes.

awec

.net has sort of fixed it a bit. WPF's databinding is brilliant compared to WinForms and allows for much cleaner code.

The downside is that it's not quite as quick to hack something together / get it up and running really fast.

The Corinthian

I'd previously only dabbled in .Net, but I've been seeing how improvements have been made alright. TBH, I'm enjoying coding for it, also because while one can fault MS on many things, they do very nice development IDE's.

Band new problem now. I have a select that uses datetime, from two datetimepickers, in the WHERE clause. The SELECT runs fine, populates a datagrid, no problem, but bizzarely the datetime part of the SELECT is completely ignored and the whole thing returns a recordset as if only "WHERE x_account_id = " + subaccountID were employed:

SqlCeConnection conn = new SqlCeConnection(Lib.ConnectionString);
string sql = "SELECT x_scaleid, x_weight, x_timestamp FROM x WHERE x_account_id = " + subaccountID
     + " AND (x_timestamp BETWEEN @start_date AND @end_date) ORDER BY x_timestamp DESC";
SqlCeCommand cmd = new SqlCeCommand(sql, conn);
cmd.Parameters.Add("@start_date", SqlDbType.DateTime, 8).Value = dFromFilter.Value.Date;
cmd.Parameters.Add("@end_date", SqlDbType.DateTime, 8).Value = dToFilter.Value.Date;
SqlCeDataAdapter da = new SqlCeDataAdapter();
da.SelectCommand = cmd;

Not been able to find anyone with the same issue online, so I'm kind of stuck. Maybe I'm better off trying to convert all the datetimes to int's - always hated working with datetime types.

Before you ask, I've tried various versions of the clause, including the use of '<' and '>', as well as different CONVERT variations.