Re: Northwind ReportsTo NULL ComboBox

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

Date: Fri, 24 Dec 2004 23:04:26 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.

"Mary Chipman" <mchip@online.microsoft.com> wrote in message
news:pa8ms09ft72amf53rvru0k5ufuc4qu4m36@4ax.com...
> 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
>
> On Wed, 22 Dec 2004 17:47:56 GMT, "Diego Deberdt"
> <deberdtdotdiego@pandora.be> wrote:
>
> >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: 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)
  • 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. ... wondering how to exclude the EmployeeId of the selected record from the ... to the table that fills the combobox each time a new row is selected. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ComboBox ItemData?
    ... not clear why you couldn't use complex databinding to accomplish it. ... > fill a combobox with those values. ... >>> The idea with the comboboxes was to use them as lists of choosable ... > private void InitializeComponent() ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: ComboBox ItemData?
    ... The only thing I saw in your code is that you are using "simple" databinding ... as you did for the cmbBoxItems combobox: ... > private void InitializeComponent() ... > this.comboBox2.Items.Add("Detta är objekt 1"); ...
    (microsoft.public.dotnet.framework.compactframework)