Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / ADO.NET / December 2004

Tip: Looking for answers? Try searching our database.

Northwind ReportsTo NULL ComboBox

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Diego Deberdt - 20 Dec 2004 23:12 GMT
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 - 21 Dec 2004 20:34 GMT
What does the T-SQL in your query look like? That's probably where you
want to solve this problem.

--Mary

>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
[quoted text clipped - 15 lines]
>
>End Sub
Diego Deberdt - 21 Dec 2004 22:27 GMT
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.

> What does the T-SQL in your query look like? That's probably where you
> want to solve this problem.
[quoted text clipped - 20 lines]
> >
> >End Sub
Mary Chipman - 22 Dec 2004 15:44 GMT
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

>The select statement that is used by the DataAdapter for the Employees
>database table simply reads all records: select * from Employees. The
[quoted text clipped - 29 lines]
>> >
>> >End Sub
Diego Deberdt - 22 Dec 2004 17:45 GMT
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

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.