Field Concatenation in SQL Dataset for Combobox

[Stewart Lane]

I have read the threads on here about adding a calculated column to a dataset
to get the concatenation of first and last name fields into a single name
field and then binding the calculated field to the control on a form.

I am coming to VB.NET and ADO.NET and SQL Server from Visual Foxpro with its
embedded SQL. I'm used to being to combine fields on the fly inside my sql
statement.

Is it true that this can not be done using the above tools and in the above
scenario?
Must one always create caluclated fields by adding them to a dataset?

Please note.. I'm not arguing for or against VFP vs VB... I just want to
make sure I'm not missing an alternative that might exist that resembles the
VFP way.

Also.. while on the topic... I am having difficulty in getting the
calculated field's name to show up as a display member option for a combobox
control who's dropdown style is dropdown list.

I am using this code in the form's load event without error but without the
desired result:

Customers1.Clear()
Dim dc As DataColumn
dc = New DataColumn("Name")
dc.DataType = System.Type.GetType("System.String")
dc.Expression = "lname + ', ' + fname"
Customers1.Tables(0).Columns.Add(dc)
OleDbDataAdapter1.Fill(Customers1)

The code doesn't generate an error but the new datacolumn "Name" does not
show up in the display member field on the properties sheet for the control.

Before I tried using the calculated field, my code was this...

Customers1.Clear()
OleDbDataAdapter1.Fill(Customers1)

....and the display member showed the fields and I could bind them to the
control.

Please note I am able to see the calculated column when I preview the data
set.
Should my new code BE somewhere else rather than form load event?

If you have the time to comment, I'd appreciate it.

[Cor Ligthert [MVP]]

Stewart,

It is not a strongly typed dataset so your fieldnames are from the fill, can
you try to do that direct after the

OleDbDataAdapter1.Fill(Customers1)

Dim dc As DataColumn
dc = New DataColumn("Name")
dc.DataType = System.Type.GetType("System.String")
dc.Expression = "lname + ', ' + fname"
Customers1.Tables(0).Columns.Add(dc)

I am not sure of that, it is just a try.

(I assume that lname and fname are exactly the columnnames case sensitive as
in your select or in your databasetable)

Cor

[Stewart Lane]

Hi Cor,

I tried moving the code as you suggested. It had no effect and I still can
not see 'Name' as a display member. Your assumption is correct. I respected
the spelling and case sensitivity of the field names exactly.

I appreciate your reply though. Let me know if you can suggest anything else.

I'm wondering if I should delete the adapter or/and dataset and recreate them.

Stewart

"Cor Ligthert [MVP]" wrote:

Stewart,

It is not a strongly typed dataset so your fieldnames are from the fill, can
you try to do that direct after the

OleDbDataAdapter1.Fill(Customers1)

Dim dc As DataColumn
dc = New DataColumn("Name")
dc.DataType = System.Type.GetType("System.String")
dc.Expression = "lname + ', ' + fname"
Customers1.Tables(0).Columns.Add(dc)

I am not sure of that, it is just a try.

(I assume that lname and fname are exactly the columnnames case sensitive as
in your select or in your databasetable)

Cor

[Cor Ligthert [MVP]]

Stewart,

I have created and added this sample to our website.

http://www.windowsformsdatagridhelp....c-f0b560f43e7c

I hope this helps,

Cor

[Stewart Lane]

Hi Cor,

I checked the tip and your site (nice by the way). It is clearly written
and I understand it but it does not address my problem directly. I think my
problem exists because I do not yet fully understand the ado.net
manipulations of datatables within datasets. I will continue my studies.

Thanks,

Stewart
"Cor Ligthert [MVP]" wrote:

Stewart,

I have created and added this sample to our website.

http://www.windowsformsdatagridhelp....c-f0b560f43e7c

I hope this helps,

Cor