concatenating fields into single column datagridview

[Ultrawhack]

According to sql concatenation, joining fields syntax
http://4h.wsu.edu/4hData/4H/pdf/03sqlselectexamples.pdf

Select City || ‘, ‘ || State|| ‘ ‘ || Zip from Youth2B
should result in output : Wausau, WI 54402-6097

My question is, how can I use datasets to fill a single column DGV with concatenated data from the above 3 fields...

Thanks

 

[cjard]

You appear to asking more a question about how to get data into a dataset rather than anything about concatenation, correct?


In my signature, you'll find a link called Data Walkthroughs 2.0 - read the section about bringing data into your app. Once you are familiar with the concepts, all you have to do is establish a new TableAdapter
in your project (right click the DataSet Designer Surface and choose Add TableAdapter), follow the wizard to connect it to the existing data table, and type in the query text you entered in your post.. Call the method whatever you like and you will be in business!

 

[Ultrawhack]

Thanks. Actually it was all about concatenation of 3 fields into a single column in a DGV.

I found the Correct SQL is
SELECT City + ', ' + STATE + ' ' + cstr(ZIP) AS Address
From Youth2B

This formats concatenated data & I pop it into a single column DGV with column header called Address

 

[cjard]

Just to point out that there isnt really a "correct SQL" notion here.. You never actually said what database technology you were using, neither did you post ina forum (such as Database\Oracle, Database\SQL Server or Database\MS Access) that would have allowed it to be determined implicitly.

My apologies for not asking you directly; i'd assumed you had verified the syntax to be correct given that you would have gotten it off a website talking about your chosen database. As for why the notion of "correct SQL" is moot, the following are all correct:

MS Access
SELECT a & ', ' & b FROM table

SQL Server
SELECT a + ', ' + b FROM table

Oracle
SELECT a || ', ' || b FROM table

but only for their relevant DBs.. It looked like you were talking about Oracle but given that you found + to be the operator for string concatenation, its more likely that youre using SQL Server (maybe access supports this too, i'm not certain).
A point worthy of note is to always mention somewhere (implicit or explicit) what database youre working with because the SQL syntax varies across all.

 

[Ultrawhack]

Now you tell me! Yes, I'm using access and I shall implicitly be explicit about it from now on. Thanks.

 

[cjard]

Incidentally, VB6 (access) was a bit of a funny one when it came to string concatenation.. I seem to recall soemthing like:

+ and & would both work, but if either side was a number or a string that could be converted to a number, then + would fail or give unexpected results. For string concatenation, use &, and for numerical addition use +.