Concatenating fields in stored procedures or ADO.NET

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I'm trying to either use and expression based column in my dataset which
concatenates fields (this has got be a common task, name, new line, address
and post code) but have had trouble because carriage returns don't appear to
be supported, but more importantly if any of the fields contain a null the
whole expression returns null

The other option would appear to be to have SQL Server do it for me - I'm
still relatively new to SQL Server and again, if any fields are null then
the whole expression returns null

How do I get round this!!??

Thanks
 
Hi Matt,

There is ISNULL function.
What do you mean by "carriage returns don't appear to be supported"?
 
Thanks for both replies, things are a little more involved so going to
expand a bit:

Lets say its your typical customer table in SQL Server which contains
fields, Title, FirstName, LastName, Address (multi line) and PostCode.
I'm populating a Typed DataSet from a stored procedure and want to have
a field which is the title & " " & firstname & " " & LastName &
vbNewLine & Address & " " & PostCode which can be bound straight off
to a TextBox, which in theory should be possible using the expression
property of a datacolumn
ie.
mydataset.MyCombinedField.expression = title & " " & firstname & " " &
LastName & vbNewLine & Address & " " & PostCode

However, when any of the above fields are Null (even if you change the
schema to return an empty string "" for DBNulls) the concatenation fails
and returns NULL, even though they're actually returning "" not DBNULL.
So for populating an entire DataSet using DBNull functions which work on
a record by record nature doesnt really help

So what i was then coming down to was having SQL Server return the
combined field but again sql server returns null if any of the fields
are null, so there must be a concatenating function that handles null
surely??
 
Hi Matt,

You are doing wrong assignment.
It should be something like:
mydataset.MyCombinedField.expression = "IsNull(title, '') + ' ' &
IsNull(firstname,'') + ' ' + ... IsNull(PostCode,'')"
 
Yeah sorry, the way i wrote the string was wrong before (VB6 habit) but
i had it correct in my code, was surprised the IsNull(Field,'') function
worked but it makes it all the more annoying that the carriage return
wont work!

I've tried vbNewLine, vbCrLf and chr(13)..all dont work
Any suggestions appreciated, thanks anyway
 
Hi Matt,

From help on DataColumn.Expression:
\n (newline)

\t (tab)

\r (carriage return)
 
Back
Top