Thank you Mary, for your help.
The problem I am strugling with however is related to DataBinding. I have
worked around the problem by not using databinding, but I'm still wondering
how I might do it if I did use databinding, because that would require less
coding.
When I specify the Employees table in my dataset as the datasource, the
combobox shows all employees:
cb.datasource = ds
cb.datamember = "Employees"
cb.displaymember = "LastName"
When I create a databinding for the SelectedValue property to the ReportsTo
field, the combobox displays the appropriate name as I use the currency
manager object to scroll through the records of the Employees table:
cb.databindings.add("SelectedValue", ds, "Employees.reportsTo")
This works fine, except for the case where the ReportsTo field is NULL. I
cannot add an item to the ComboBox that says 'Boss' or 'NULL' because when
the datasource property is set, you cannot add items. In the case where
ReportsTo is NULL, the combobox simply show the first employee in the list.
Obviously this is wrong. Also when updating the the field with the combobox
you cannot specify NULL.
> Using T-SQL is the best way to go about this, depending on what you
> want to see in the DataTable. The following SELECT uses the IsNull
[quoted text clipped - 70 lines]
> >> >
> >> >End Sub
Mary Chipman - 23 Dec 2004 19:50 GMT
Yes, it's a databinding issue, and when you create a SELECT query that
does away with returning null values, the issue goes away, no?
--Mary
>Thank you Mary, for your help.
>
[quoted text clipped - 100 lines]
>> >> >
>> >> >End Sub
Diego Deberdt - 24 Dec 2004 23:04 GMT
If all you want to do is show the user a list of Employees, then yes the
problem is gone. But the ComboBox has to enable updating the contents of the
database as well - and especially make it possible to insert NULL values for
the ReportsTo field. Modifying the select query does not offer a solution
there.
> Yes, it's a databinding issue, and when you create a SELECT query that
> does away with returning null values, the issue goes away, no?
[quoted text clipped - 105 lines]
> >> >> >
> >> >> >End Sub
Mary Chipman - 27 Dec 2004 14:03 GMT
No, of course not. Updating the data source is a separate issue
entirely. You need to write an UPDATE or INSERT statement or call a
stored procedure. The data displayed in a combo box is irrelevant to
updating the database.
The real issue is dealing with three-value logic, which complicates
programming and introduces additional overhead on the servers. Many
developers have opted out by disallowing nulls in the database, a
solution that makes sense for most situations. See the topic "Null
Values" in SQL Server Books Online for more information.
--Mary
>If all you want to do is show the user a list of Employees, then yes the
>problem is gone. But the ComboBox has to enable updating the contents of the
[quoted text clipped - 122 lines]
>> >> >> >
>> >> >> >End Sub
Diego Deberdt - 30 Dec 2004 01:36 GMT
Maybe you need to look at this as a 'disconnected' problem. I am not
concerned about SELECT or UPDATE statements. What I'm working with is a
DataSet that contains DataTables. I don't really care where the data comes
from, although in fact it does come from a database. I have been working on
this database application as an excercise. The database is Northwind. I am
working with the database 'as-is'. It would indeed be nice to side-step NULL
values by disallowing them in the DB, but they excist in Northwind so I'm
dealing with them.
As a final note I would like to thank you for your effort to think this
problem through with me these past few days (or has it been longer already).
Anyhow: Merry Christmas and a happy Newyear!
Ciao,
Diego
> No, of course not. Updating the data source is a separate issue
> entirely. You need to write an UPDATE or INSERT statement or call a
[quoted text clipped - 135 lines]
> >> >> >> >
> >> >> >> >End Sub
Mary Chipman - 30 Dec 2004 21:39 GMT
I figured as much when you said you were using Northwind, which nobody
much does outside of an exercise :-) Anything to do with working with
data on the client using ADO.NET is *always* disconnected, by
definition. The reason I recommended solving the problem with your SQL
query is because that is the most efficient and least code-intensive
way of dealing with the issue of nulls existing in the database and
not displaying the way you want them to in the UI. I'm sure there are
probably other more code-intensive solutions, but I can't say I'm very
motivated to look for them when just writing a simple SQL statement
works so well. Happy new year to you too,
--Mary
>Maybe you need to look at this as a 'disconnected' problem. I am not
>concerned about SELECT or UPDATE statements. What I'm working with is a
[quoted text clipped - 168 lines]
>> >> >> >> >
>> >> >> >> >End Sub