Binding two columns to a Data Object [ReOpened]

[theonewhowas]

Greetings:

I'm looking to bind more than 1 column to a combo or list box. I've searched the forums but I can't seem to find any posts that have an answer. Could someone please assist me by giving me an example how or link me a post (if you know of one) that can help me?

I'm using this combo box to select a specific record so I'd like to have the first and last name for the user to see

Thank you all in advance!

~~ The One Who Was ~~

[Lunatic3]

Edneeis has developed a user control (combobox) that you can bind to more than one column. However his site is down at the moment, you may request him to send it to you. I think he has posted that here before, but dont know if that was the final version or not.

There is another way you can do it. In you select stament select a custom column that concats two fields, something like this:
Select firstname, lastname, id , (firstname + ' ' + lastname) as fullname from mytable.

As an alternatvie if you are working with datasets you can add a custom column to the table in your dataset that its expression is (firstname +' ' +lastname)
The only problem with the above mentioned ways is that if your firstname or lastname field is null then the result of the whole field will be null. To avoid this you may change your select statment as follows.

Select firstname, lastname, id , (IsNull(firstname,'') + ' ' + IsNull(lastname,'')) as fullname from mytable.

And the column expression should be:
"IsNull(firstname,'') + ' ' + IsNull(lastname,'')"

[theonewhowas]

Lunatic:

Very helpful. I shall investigate these options further myself. They seem to make sense to me.

Thanks very much =)

[theonewhowas]

Greetings again:

Anyone able to tell me how I would make it so that the null records do not take up a row in the combo box? At the moment, each row that does not have a first or last name, the row is displayed as blank?

[Lunatic3]

I think i told you abou this in previous post, did you read it carefully?

[theonewhowas]

Lunatic:

My apologies, it appears you did explain.

I did enter the statement into the select command

Select ContactIDNumber, CompanyName, FirstName, LastName, (isnull(FirstName,'') + ' ' + isnull(LastName,'') as FullName FROM Contacts

After I entered that it of course was displaying the blank rows. Then I re-read your post stating the Column Expression. I didn't really know what it was you were speaking of so I did some research and this is what I came up with for a column expression.

dsSystems.Tables("Contacts").Columns("FullName").Expression = "IsNull(firstname,'') + ' ' + IsNull(lastname,'')"

Now, when I fill my dataset (dsSystems), I receive an error

"An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: The column mapping from SourceColumn 'FullName' failed because the DataColumn 'FullName' is a computed column."

Is my expression typed correctly? or what am I doing wrong?

[Edneeis]

You may have to just run a query that returns only rows where the fullname is not null and fill the list with that.

Select ContactIDNumber, CompanyName, FirstName, LastName, FirstName + ' ' + LastName as FullName FROM Contacts WHERE (NOT (FirstName IS NULL)) AND (NOT (LastName IS NULL))

[Lunatic3]

Originally posted by Edneeis
You may have to just run a query that returns only rows where the fullname is not null and fill the list with that.

Select ContactIDNumber, CompanyName, FirstName, LastName, FirstName + ' ' + LastName as FullName FROM Contacts WHERE (NOT (FirstName IS NULL)) AND (NOT (LastName IS NULL))

Your query returns the rows where non of the first and last name are null. It may happen that the first name is null but the last name is still needed. So this wont help him.

Originally posted by theonewhowas
Is my expression typed correctly? or what am I doing wrong?

You are mixing two ways. Either change your query, or add a custom column to the dataset table. You are doing both, that causes error.

[Edneeis]

I don't think I understood your previous post as well, but now I get it. The 'And the column expression' part at the end threw me.

Also your right my query cuts out any rows that don't have a full name, which he may not want.

theonewhowas listen to Lunatic3 hes got it going on.

Also who were you?

[theonewhowas]

Greetings again:

Is there not a way I could filter records displayed in the combobox by creating a proper column expression for the full name?

Somthing kinda like the following...

dsSystems.Tables("Contacts").Columns("FullName").expression = "iif(isnull(FirstName) AND isnull(LastName), , isnull(FirstName,'') + ' ' + isnull(LastName))"

I am unsure if a person can do this in an expression. I've tried the above statement and was returned with this error:

An unhandled exception of type 'System.Data.EvaluateException' occurred in system.data.dll

Additional information: Invalid number of arguments: function isnull().

I believe I am using the isnull command incorrectly.

[Lunatic3]

Originally posted by theonewhowas

dsSystems.Tables("Contacts").Columns("FullName").expression = "iif(isnull(FirstName) AND isnull(LastName), , isnull(FirstName,'') + ' ' + isnull(LastName))"


Additional information: Invalid number of arguments: function isnull().

I believe I am using the isnull command incorrectly.

The second isnull is missing the second argument which is '' .

So follow this simple steps to make it work.
1- your select statment may be :
Select ID, firstname, lastname from contacts

2- Fill the dataset

3- Add a column to the dataset that concats both fields.

dsSystems.Tables("Contacts").Columns.Add("fullname", GetType(System.String), "isnull(firstname,'') + ' ' +isnull(lastname,'')")

Remember that the second argument of isnull is two ' not a single ".