Dealing with null values in a Column Expression

T

The One Who Was

Well-known member
Joined
Jun 2, 2003
Messages
52
Location
Manitoba Canada
Greetings

Ive been having an issue with a combo box. I have it bound to a computed column. Ive manually created the column by editing the dataset. The dataset name is "dsSystems", the table name is "Contacts" (which contains name and address ect info in it), and the column I added is named FullName .

the following is the line I used to define my column expresstion

dsSystems.Tables("Contacts").Column("FullName").Expression = "isnull(FirstName,) + + isnull(LastName,)"

Then Ive also bound the combo box to that FullName Column.


The problem I have is... some of my contacts are comanies, and I dont have an associated First and Last name with this contact record, so what happend is I get a list with scattered blank lines since the companies dont have a first and last name...

My question is, can I get rid of these blank lines without adding a WHERE clause to my select statement (since I still may need to access these businesses records from this form)

Thank you for any help in advance
 
You could use a DataView to bind to, as in:
Code: 
dsSystems.Tables("Contacts").DefaultView.RowFilter = "FirstName IS NOT NULL and LastName IS NOT NULL"
comboBox1.DataSource = dsSystems.Tables("Contacts").DefaultView
Or replace the filter with "FullName <> " or whatever works out best.

If youre already using the DefaultView property, you can create a new DataView to bind to:
Code: 
Dim dv As DataView = New DataView(dsSystems.Tables("Contacts"))
dv.RowFilter = "FirstName IS NOT NULL and LastName IS NOT NULL"
comboBox1.DataSource = dv

-Nerseus
 
Nerseus:

I knew there had to be some sort of solution or work around for what I was trying to accoplish.

Thank u greatly for introducing me to the dataview. Ive found it handy in a few other places as well. =)
 
No problem. Also, if you just need to loop through a DataTables rows but not bind them, you can use the Select method of the DataTable, as in (I may not have the VB syntax correct - Im more C#):
Code: 
Dim rows() As DataRow = dsSystems.Tables("Contacts").Select("FirstName IS NOT NULL and LastName IS NOT NULL")
Dim row As DataRow
ForEach row In rows
     Do something with row here
Next  ? Not sure how to end Foreach in VB.NET :)

Along with the filter shown above, you can pass Select an "ORDER BY".

There are other methods, such as Compute (to quickly calculate a SUM, AVG, COUNT, etc.) that are worth looking at as well.

-Nerseus
 
Back
Top