Northwind ReportsTo NULL ComboBox

[Diego Deberdt]

I'm trying to show the ReportsTo field of the Employees table in the
Northwind database in a databound ComboBox. This works fine, except for the
case where ReportsTo is NULL. When ReportsTo is NULL the ComboBox should
show an empty string, or whatever, but it shows the first item in the list.
I have tried catching the Format event to detect NULL values and translate
them into something else, and the event actually gets called and I can
detect the DBNull values, but two things are odd. The field e.DesiredType is
always System.Object, while I'm expecting it to be of type Integer. The
second thing is that setting e.Value does not seem to have any effect.
Public Sub NULLToZero(ByVal sender As Object, ByVal e As
System.Windows.Forms.ConvertEventArgs)

If e.Value Is DBNull.Value Then

e.Value = 0

End If

End Sub

 

[Mary Chipman]

What does the T-SQL in your query look like? That's probably where you
want to solve this problem.

--Mary

 

[Diego Deberdt]

The select statement that is used by the DataAdapter for the Employees
database table simply reads all records: select * from Employees. The
Employees table in the DataSet is an exact copy of the records in the
database table.

 

[Mary Chipman]

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
function to convert the null value in ReportsTo to 'Boss'. Note that
you have to use explicit type conversions to convert to a string:

select LastName, IsNull(Cast(ReportsTo AS varchar), 'Boss') from
employees
ORDER BY LastName

The first few rows look like this:
LastName
-------------------- ------------------------------
Buchanan 2
Callahan 2
Davolio 2
Dodsworth 5
Fuller Boss
King 5

If you want to see the actual name instead of just the employeeID,
this query uses a self-join to give you the name of the person the
employee id refers to while using 'Boss' instead of null:

SELECT Employees.FirstName + ' ' + Employees.LastName AS EmployeeName,
IsNull(Cast(Employees2.FirstName + ' ' + Employees2.LastName AS
varchar), 'Boss') AS ReportsToName
FROM Employees LEFT JOIN Employees AS Employees2
ON Employees.ReportsTo = Employees2.EmployeeID
ORDER BY Employees.LastName

The first few rows of the result set look like:
EmployeeName ReportsToName
------------------------------- ------------------------------
Steven Buchanan Andrew Fuller
Laura Callahan Andrew Fuller
Nancy Davolio Andrew Fuller
Anne Dodsworth Steven Buchanan
Andrew Fuller Boss

--Mary

 

[Diego Deberdt]

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.

 

[Mary Chipman]

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

 

[Diego Deberdt]

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.

 

[Mary Chipman]

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

 

[Diego Deberdt]

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

 

[Mary Chipman]

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