Re: How do I preserve carriage returns in SQL?

From: Joseph Geretz (jgeretz_at_nospam.com)
Date: 03/21/05 

Date: Mon, 21 Mar 2005 13:42:59 -0500

Hi Dan,

Thanks for your reply. You are absolutely correct. We are submitting the SQL
through the ADO Connection.Execute method. However, I did find a bit of code
which was replacing vbCrLf with " ". Duh! :-)

Anyway, with that out of the way, I'm finding that the data makes its way
into the table complete with carriage returns. Or more precisely, complete
with line feeds. I'm finding on the retrieval side that I need to execute
the following string replacement in order for the data to display correctly
in a multi-line textbox.

RS("Alert") = Replace(RS("Alert"), vbLf, vbCrLf)

I'm guessing that if I'd be writing this to the table via a Recordset this
wouldn't be an issue. This is the way most of our database updates are
applied and I've never noticed carriage returns to be an issue. However,
since we're doing this particular update via embedded SQL it seems that
line-breaks go into the database as the vbLf character, while a vbCrLf is
needed in order to properly display a newline in a textbox.

Does this sound correct?

Thanks for your help,

- Joe Geretz -

"Daniel Crichton" <msnews@worldofspack.co.uk> wrote in message
news:%23pSwBZjLFHA.3844@TK2MSFTNGP14.phx.gbl...
> Joseph wrote on Mon, 21 Mar 2005 10:44:29 -0500:
>
>> How do I preserve carriage returns in my SQL statement? See the update
>> statement below. The value for the Alert field is:
>>
>> 'Alert1
>> Alert2
>> Alert3
>> Alert4'
>>
>> However, when I read this back from the database, I get 'Alert1 Alert2
>> Alert3 Alert4'. I guess I could convert vbCRLF to '|' and vice versa when
>> I get the data back from the database. But this would eliminate the pipe
>> character as a legal data character. Is there a more standard approach?
>>
>> Thanks for your help!
>>
>> - Joe Geretz -
>>
>> UPDATE PERSONALERTS
>> SET ALERTID = 1,
>> ISPRIVATE = 'N',
>> ISDELETED = 'N',
>> CREATEDBY = 1,
>> CREATETIME = '03/21/2005 10:36',
>> MODIFIEDBY = NULL,
>> MODIFYTIME = NULL,
>> DELETEDBY = NULL,
>> DELETETIME = NULL,
>> ALERT = 'Alert1
>> Alert2
>> Alert3
>> Alert4'
>> WHERE PERSONID = 9367
>>
>
> How are you sending this SQL statement to your server? I've just tried
> something similar on mine in SQL Query Analyzer and it stores and returns
> the line breaks fine.
>
> Dan
>
>
>
>
>

Relevant Pages

  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... But SQL does not have a pointer data type or the ... More and more programmers who have absolutely no database training are ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)