Re: Northwind ReportsTo NULL ComboBox
From: Diego Deberdt (deberdtdotdiego_at_pandora.be)
Date: 12/30/04
- Next message: JMark: "Update to Visual FoxPro 6 Table is failing"
- Previous message: Zak Milas: "Re: INSERT into Access Database using C# from ASP page"
- In reply to: Mary Chipman: "Re: Northwind ReportsTo NULL ComboBox"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Dec 2004 01:36:41 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
"Mary Chipman" <mchip@online.microsoft.com> wrote in message
news:1150t0tttc9e95518e85sf63562b5ssesj@4ax.com...
> 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
>
> On Fri, 24 Dec 2004 23:04:26 GMT, "Diego Deberdt"
> <deberdtdotdiego@pandora.be> wrote:
>
> >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
> >> >> >> >
> >> >> >>
> >> >> >
> >> >>
> >> >
> >>
> >
>
- Next message: JMark: "Update to Visual FoxPro 6 Table is failing"
- Previous message: Zak Milas: "Re: INSERT into Access Database using C# from ASP page"
- In reply to: Mary Chipman: "Re: Northwind ReportsTo NULL ComboBox"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
