Dealing with null values in a Column Expression

[The One Who Was]

Greetings

 

I've been having an issue with a combo box. I have it bound to a computed column. I've 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 I've also bound the combo box to that FullName Column.

 

 

The problem I have is... some of my contacts are comanies, and I don't 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 don't 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

[Nerseus]

You could use a DataView to bind to, as in:

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 you're already using the DefaultView property, you can create a new DataView to bind to:

Dim dv As DataView = New DataView(dsSystems.Tables("Contacts"))
dv.RowFilter = "FirstName IS NOT NULL and LastName IS NOT NULL"
comboBox1.DataSource = dv

 

-Nerseus

[The One Who Was]

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. I've found it handy in a few other places as well. =)

[Nerseus]

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 - I'm more C#):

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