vb.net - Trying to make MS SQL store CRLF -

how can sql 2008 r2 store cr/lf embedded in string (so 2 paragraphs, or possibly multiple cr/lfs) field of record being inserted programmatically vb.net? not records have cr/lf cannot add them every record. +char(13)+char(10) split chararrary.

update: appears actual proble: xml parser convert cr/lf lf consistency. passing data , web service , seems cr being lost.

these strings coming access db , have cr/lf included in multi-paragraph field of record. pass them straight in, sql converting them spaces.

in vs sql editor record appears store 20:20 hex cr/lf should reside. string should have char(13) & char(10) or 0x0d0a not.

i see same results editing or inserting record directly via ssms or vs sql editor. records submitted via web service have eliminated testing. read xml parser convert cr/lf lf consistency.

i not able show actual query. adapter built here , populated , passed app via web service call. process works records have crlf in them.

dim strconnection string strconnection = configurationmanager.connectionstrings("connectionstringmydb").connectionstring          dim myconnection new sqlconnection(strconnection)         dim mycommand new sqlcommand(strsql, myconnection)  ' access file name mycommand.parameters.addwithvalue("@dbname", oledbtype.varchar).value = dbname  ' table in access db            mycommand.parameters.addwithvalue("@tablename", oledbtype.varchar).value = tablename    myconnection.open()  ' create dataadapter dim mydataadapter new sqldataadapter() mydataadapter.selectcommand = mycommand  ' populate dataset , close connection dim mydataset new dataset() mydataadapter.fill(mydataset) myconnection.close()  return mydataset 

processsing in app after assigning dataset table.

for each row datarow in mytable.rows     dim stringval string = row(columnname).tostring()      ' added see row adding 2 spaces after carriage return     dim cstringval() char = stringval.tochararray     dim csearchvalue() char = searchvalue.tochararray     ' added code        dim line_array1 [string]() = stringval.split(vbcr & vblf.tochararray())        dim line_array2 [string]() = searchvalue.split(vbcr & vblf.tochararray())      ' line_array2 consistently ids vbcrlf, line_array1 never does.       ' tried     if row(columnname) = searchvalue         return true     end if      ' tried     if stringval = searchvalue         return true     end if      ' ,      if string.compare(stringval, searchvalue, false) = 0         return true      end if    next return false 

thanks

maybe problem unicode. experience there no problem storing crlf, there can problem unicode <-> ascii.

please try

dim cmd sqlcommand cmd.commandtext = "insert mytable(fld) values (n'" + variablecontainingcrlf +"')" cmd.executenonquery 

or

dim cmd sqlcommand cmd.commandtext = "insert mytable(fld) values (@param)" cmd.parameters.add("@param", sqldbtype.nvarchar, 50).value = variablecontainingcrlf cmd.executenonquery 

letter n before string or nvarchar crucial. dataadapter introduces other confusing layer.