I have a situation where data in a field contains CrLf characters. Code has been in place for a couple of years and many records have been affected.

The question is:

Should I add code to catch the CrLf characters at the field level?
Should I add code to catch and possibly correct the data before it is uploaded to SQL?
Should I modify the upload to catch CrLf characters and possibly correct the data in the upload procedure?

Whichever approach is more appropriate, how do I identify CrLf characters and then correct it.

Why not create a project that will go through the entire database and correct code?
CrLf is nothing but a line break, like vbNewLine.

See if this helps to catch the CrLf.

Dim sTemp As String = "some" & vbNewLine & vbNewLine & "text"
        If sTemp.Contains(CChar(vbCrLf)) Then
            MsgBox(sTemp) '// Display before.
            sTemp = sTemp.Replace(vbCrLf & vbCrLf, " ") '// correct if needed.
            MsgBox(sTemp) '// Display after.
        End If

After searching for possible solutions, I tried this:

At the point in the code where the data is prepared before it is uploaded to SQL I added this code:

Imports System.Text.RegularExpressions

And replaced this:
CustomerName = txtCustomerName

With this:
CustomerName = Regex.Replace(txtCustomerName, "[^A-Za-z0-9\-/]", " ")

Which seems to replace anything that doesnt start with A-Za-z0-9\-/ with a space.
Seems to work so far.

Any reason why this would not be an acceptable solution?

Member Avatar


the Vbnewline or Crlf or what ever does have its number that tells the textbox that is must create a new line
but to detect it you must know it Byte or Ascii number to find it

Thanks codeorder!

I tried your suggestion, but for some reason it does not catch the Crlf.

Member Avatar


I don't see anything wrong with codeorder's example.

Heres his version that searches a byte array with the vbcrlf and replaces.

Dim sTemp As String = "some" & vbNewLine & vbNewLine & "text"

 Dim mStream As New System.IO.MemoryStream
 Dim bWriter As New System.IO.BinaryWriter(mStream)

 mStream.Position = 0
 Dim bReader As New System.IO.BinaryReader(mStream)

 MsgBox(sTemp) '// Display before.
 Do While bReader.PeekChar <> -1
     If bReader.ReadChar = CChar(vbCrLf) Then
         sTemp = sTemp.Replace(vbCrLf, " ") '// correct if needed.
      End If
 MsgBox(sTemp) '// Display after.

Awesome, I inserted this code:

If .CompanyName.Contains(CChar(vbCrLf)) Then
.CompanyName = .CompanyName.Replace(vbCrLf, " ")
End If

Works great! Thanks all!