Re: Northwind ReportsTo NULL ComboBox

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Diego Deberdt (deberdtdotdiego_at_pandora.be)
Date: 12/22/04 

Date: Wed, 22 Dec 2004 17:47:56 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.

"Mary Chipman" <mchip@online.microsoft.com> wrote in message
news:b95js090dvdgmq34e03vdl8mhehrds01hq@4ax.com...
> 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
>
> On Tue, 21 Dec 2004 22:27:58 GMT, "Diego Deberdt"
> <deberdtdotdiego@pandora.be> wrote:
>
> >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" <mchip@online.microsoft.com> wrote in message
> >news:o42hs0ts3cn4u63ocm7l693mrseko8va8j@4ax.com...
> >> What does the T-SQL in your query look like? That's probably where you
> >> want to solve this problem.
> >>
> >> --Mary
> >>
> >> On Mon, 20 Dec 2004 23:13:17 GMT, "Diego Deberdt"
> >> <deberdtdotdiego@pandora.be> wrote:
> >>
> >> >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
> >> >
> >>
> >
>

Relevant Pages

  • Re: Northwind ReportsTo NULL ComboBox
    ... >The problem I am strugling with however is related to DataBinding. ... >When I specify the Employees table in my dataset as the datasource, ... >When I create a databinding for the SelectedValue property to the ReportsTo ... the combobox displays the appropriate name as I use the currency ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: VS2005 combox question
    ... > The scenario is that of the employees table wherein there's an EmployeeId ... > the reportsto field, but since a person can't report to himself. ... > to the table that fills the combobox each time a new row is selected. ... One BindingSource is for the DataGridView, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: VS2005 combox question
    ... >> The scenario is that of the employees table wherein there's an EmployeeId ... >> and a ReportsTo field in the same table. ... >> to apply a filter to the table that fills the combobox each time a new ... One BindingSource is for the DataGridView, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Northwind ReportsTo NULL ComboBox
    ... If all you want to do is show the user a list of Employees, ... But the ComboBox has to enable updating the contents of the ... >>The problem I am strugling with however is related to DataBinding. ... except for the case where the ReportsTo field is NULL. ...
    (microsoft.public.dotnet.framework.adonet)
  • Filtering Query based on field values
    ... The form is used to register hours worked by temporary employees in a table ... Next I use a combobox Select_Week to select the week that they did their work ... For this I have setup a large table WEEKS with all weeknumber (and dates ... I tried to check for empty values in the Enddate field, but for now, having ...
    (microsoft.public.access.forms)