Select vbCRLF as part of a string for Insert

[Rabbitfood]

Hi,

I am trying to do a select into ie.

INSERT INTO TableA (col1, col2)
SELECT (col1, col2)
FROM TableA
WHERE col3 = ...

Now in part of my select statement I want to create a new string by selecting some existing data and building it up using the + operater but I also want to include vbCRLF

SELECT ....,
'Some Text:' + col4 + 'some more text', etc..

I did not include the vbCRLF in the example above because I cant get it to work - so rather than me fudging it I will explain why I am trying to do it.

Im working in ASP (classic) (and MS SQL 05) and I want to insert some data so that when presented back in html it wraps - but I dont want to use "<br />". So what I would have done is:

dim myString : myString = "some text" & vbCRLF & "more text"

Now after submitting this data, I can pull it back out and it will display with line breaks but without the use of "<br />".

The end result is to send the data I'm pulling out in a HTML formatted email.

So is it possible, or is there another way, to do what im asking?

Below is a small portion of the query I will actually be running - note I have used <br /> in my example but I would really like to not hae to do it that way.

SELECT 'REFUND FOR ORDER #195001<br />See original order comments below<br />---------------------------------<br />' + ISNULL(comments, '')
+ '<br />---------------------------------<br />' AS comments
FROM Orders
WHERE (Order_ID = 195001)


Thanks In Advance, David.

 

[TJRTech]

If you are truly looking for HTML formating then CrLf (vb or not) will NOT give you a line break and you will need to use <br> which you seem to be avoiding.

I have written several stored procedures in the past that use SqlMail to send emails, they format the text of the email, and they use CHAR(13) + CHAR(13) (line feed twice) to provide newlines.

 

[ESquared]

Char 13 is carriage return, not line feed. That's 10.

 

[Rabbitfood]

I have been using vbCRLF in the past and it does what I need.

The purpose of the query I am trying to create is to post my data in one hit without first having to retrieve the data then format it and post it back.

I believe I would experience the same problems with trying to use CHAR(13) + CHAR(13)in my query eg.

1. SELECT 'some text ' + CHAR(13) + CHAR(13) ...
This will result in error about CHAR(13) + CHAR(13) not being valid columns.

2. SELECT 'some text CHAR(13) + CHAR(13)' ...
This will simply return (ultimately post) a string containing the text 'CHAR(13) + CHAR(13)' which will not have the affect of creating an actual CRLF.

The full query I will ultimately use is:

INSERT INTO Orders
(eAccountNumber, OrderStatus_ID, DateCreated, WareHouseID, SalesPersonID, isRefund, Bill_Name, Bill_Company, Email, Bill_Address,
Bill_Address2, Bill_Suburb, Bill_State, Bill_PostCode, Bill_Phone, Del_Name, Del_Company, Del_Address, Del_Address2, Del_Suburb, Del_State,
Del_PostCode, Del_Phone, comments)
SELECT eAccountNumber,
(SELECT OrderStatus_ID
FROM OrderStatus
WHERE (OrderStatus = 'Incomplete')) AS OrderStatus_ID, GETDATE() AS date, 2 AS WareHouseID, sf AS SalesPersonID, 1 AS isRefund, Bill_Name,
Bill_Company, Email, Bill_Address, Bill_Address2, Bill_Suburb, Bill_State, Bill_PostCode, Bill_Phone, Del_Name, Del_Company, Del_Address,
Del_Address2, Del_Suburb, Del_State, Del_PostCode, Del_Phone,
'REFUND FOR ORDER #124001<br />See Original Order comments below<br />---------------------------------<br /><br />' + comments + '<br />---------------------------------<br />'
AS comments
FROM Orders AS Orders_1
WHERE (Order_ID = 124001)

Note that I dont want the "<br />" left in my string as per above.

Please let me know if I am not explaining this enough, nothing worse than ambiguity!

Thanks Again, David.

 

[ESquared]

What do you mean not valid columns?

I just ran the following on my SQL Server 2000 installation here at home:

select 'Line' + CHAR(13) + CHAR(13) + 'wrap'

It ran fine with no problem. Query Analyzer doesn't show the line breaks, but they're there, as you can prove by changing the select to a print.

 

[Rabbitfood]

I stand corrected!

I handnt actually tried that, I tried "select 'Line' + vbCRLF + 'wrap'" which is obviously not going to work.

I will test and confirm that "select 'Line' + CHAR(13) + CHAR(13) + 'wrap'" works as expected.

Thanks, David.

 

[ESquared]

I did mean to change it to CHAR(13) + CHAR(10) which is actually carriage return + linefeed.

vbCRLF is a constant in VB (see the vb on the front) which has no meaning in SQL Server. It is equivalent to the string Chr$(13) & Chr$(10)

In the immediate pane in VB type this:
? asc(vbCRLF), asc(mid(vbcrlf, 2, 1)), len(vbcrlf)

 

[Rabbitfood]

Thanks for the feedback guys!

Doing the char(13) thing worked eg.

INSERT INTO Table1 (col1)
SELECT 'See Original Order comments below' + CHAR(13) + CHAR(10) + col1 AS col1
FROM Table1
WHERE col2 = something


Thanks Again, David.

 

[AlexCuse]

Rabbitfood-

Sorry to be so late to the party, but remember that you have those in there if you ever need to export this to a text file. I have had all kinds of problems with clients sending tab delimited files that contain cr-lf's in the data. It can make things pretty ugly, to say the least.

Take Care,

Alex

Ignorance of certain subjects is a great part of wisdom