SQLServer 2005: getting excel text box vbcrlf's into the DB

Question

SQLServer 2005: getting excel text box vbcrlf's into the DB

londoner-737512

Old Hand

Points: 378
More actions
October 31, 2008 at 3:48 am

#214796

i have a text box object in excel 2003 that users enter text into.
line feeds or carriage returns are an important feature of the data to preserve. (vbCRLF)
standard VBA using ADO gets it into a MEMO field in any ACCESS version and can be read back in its preseved format.
using same code as an insert into SQLSERVER does NOT take the line feeds with it. spaces seem to be used instead. my data field is a VARCHAR(MAX)
i have read about replacing the vbcrlf's with chr(13) and then insert but this doesnt help.
any ideas on this please?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

1976 SSC Enthusiast Points: 159 More actions · Answer 1

You may only have a problem seeing the line breaks using your select statement in SSMS. Please compare output of this code in SSMS using 'Results to Grid' in Management Studio query window:

declare @text nvarchar(100)

set @text = 'text line 1

text line 2'

select @text

print 'Text:'

print @text

Switch between 'Results' and 'Messages' window. Although you may see @text with spaces instead of line breaks in the 'Results' window, line breaks are in the field.

Cheers,

londoner-737512 Old Hand Points: 378 More actions · Answer 2

thanks -and i agree inserting this line break text into the sqlserver directly from a stored proc and then querying it does show the breaks.

(BTW i use visual studio 2005)

the problem then must be the interface between sqlserver and excel ADO and the translation of a vbcrlf.

still stuck...........:)

1976 SSC Enthusiast Points: 159 More actions · Answer 3

Post some code samples you use to read the excel data.

londoner-737512 Old Hand Points: 378 More actions · Answer 4

i think i have just nailed this.

i changed the data field type to a VARCHAR(50) from a VARCHAR(MAX) and it now works.

dont know why - was thinking that the max variant would be a memo type Access field.