Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nick_dkc
Starting Member
25 Posts |
 Posted - 2009-06-08 : 08:48:28
|
I am using SQL Server Express 2005 as the DB for a BPM solution called Singularity - which is great! My problem is that I am a complete SQL dummy - not a great combination!!I have a table in which we are recording enquiries - in that table there are columns:[responseDate] of type dateTime[responseUser] of type nvarcharThere is also a column called [responseAttempts]Every time the user tries to respond to the enquiry I need to be able to write the [responseDate] and [responseUser] to the [responseAttempts] column - separated by a CRLF - so when the column [responseAttempts] is called back into my front-end it looks like this:23-12-09 12:12 Fred24-12-09 16:10 Sarah01-01-10 13:24 Peter(and it would keep being added to every time a user attempts to respond to that one enquiry)I guess this should be a stored procedure, but using what functions I have no idea.If anyone can offer me even the vaguest hint of how to proceed I will be very very grateful  |
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2009-06-08 : 08:54:34
|
| Do you want the value to be saved in the table or just need to show in the front end in the desired format?Select convert(varchar(20),[responseDate]) + ' ' + [responseUser] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-06-08 : 09:00:13
|
| If you want to show them in front end application, better do concatenation thereMadhivananFailing to plan is Planning to fail |
|
|
|
nick_dkc
Starting Member
25 Posts |
Posted - 2009-06-08 : 09:12:12
|
| WOW - not used to such a rapid response!It needs to be saved to the [responseAttempts] column each time - I thought that it could be concatenated with the CRLF in it as well so that when it is queried from the front ed it will display as desired. The front end application doesn't allow me to concatenate there.Ayamas - I don't understand what the convert(varchar(20) is for - is it OK to join a date to a text field? |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2009-06-08 : 09:19:27
|
| I am converting it to varchar using convert function. |
|
|
|
eralper
Yak Posting Veteran
66 Posts |
Posted - 2009-06-08 : 09:28:02
|
| Hello Nick,If you are working with the nvarchar() or varchar() data types you can concatenate or basically add string values.But if you are working with text or ntext data types, you can not simply add two ntext values.Check the following article [url]http://www.kodyaz.com/articles/update-text-column-using-updatetext.aspx[/url] for UPDATETEXT command in T-SQL.-------------Eralperhttp://www.kodyaz.com |
|
|
|
nick_dkc
Starting Member
25 Posts |
Posted - 2009-06-09 : 01:42:24
|
| Thanks ayamas / eralper - that's what I thought (that they couldn't just be added together).Can I use an IIF statement to do this?Here's my logic (which may well be wrong!)If [responseAttempts] is empty then Select convert(varchar(20),[responseDate]) + ' ' + [responseUser] - Q. how do I write that to my [responseAttempts] field?If [responseAttempts] is NOT empty then [responseAttempts] + char(13) + char(10) + Select convert(varchar(20),[responseDate]) + ' ' + [responseUser]. - Q. how do I then write that to my [responseAttempts] field?Really - genuinely - appreciate the help I am getting! Thanks guys Do I need to make a new column in my table to temporarily hold the concatenation before writing it to the add CRLF - i.e. if the target field [responseAttempts] is NOT empty the concatenation has to start with char(13) + char(10).) |
|
|
|
|
|
|
Adding text into a column that has text in it