0

Hello,

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.

4
Contributors
6
Replies
7
Views
6 Years
Discussion Span
Last Post by gspeedtech
0

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

Edited by codeorder: n/a

0

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?

0

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

0

Thanks codeorder!

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

0

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)
 bWriter.Write(sTemp)

 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
 Loop
 MsgBox(sTemp) '// Display after.
0

Awesome, I inserted this code:

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

Works great! Thanks all!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.