Re: Northwind ReportsTo NULL ComboBox

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

From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 12/23/04 

Date: Thu, 23 Dec 2004 14:50:42 -0500

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: 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)
  • RE: Repost Cant populate 2nd listbox from selected value of first
    ... We can just fill the DataSet once, and a DataRelation can ... Create a typed DataSet with a single table Employees in it. ... The KeyField is EmployeeID and the Foreign Key Field is ReportsTo. ... Put 2 ListBoxes on the form and use the following code in Form_Load. ...
    (microsoft.public.dotnet.framework.adonet)