Remove the crlf in the SQL-database?

Question

I'm doing an exercise with the Northwind database from Microsoft. I try to fill the values from a table into a Truedbgrid. That still worked, but afterwards I noticed that 3 columns have a crlf. I tried to solve it with a REPLACE, but it didn't work.

I changed the query(in dataset) from

SELECT ContactID, ContactType, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Extension, Fax, HomePage, PhotoPath, Photo FROM dbo.Contacts

to

SELECT ContactID, ContactType, CompanyName, ContactName, ContactTitle, REPLACE(Address,CHAR(13)+CHAR(10),' '), City, Region, PostalCode, Country, Phone, Extension, Fax, HomePage, PhotoPath, Photo FROM dbo.Contacts

An another way I don't known. I search on Internet, if theres a way to say to the truedbgrid, remove all crlf. But I doesn't found something. In the table is that crlf invisible. I can't remove that. Are there any ideas?

My VB.NET Classcode:

Private Sub tdbgContactsParamentrieren()
    Try
        With tdbgContacts
            .AllowAddNew = False
            .AllowDelete = False
            .AllowColMove = False
            .AllowFilter = True
            .AllowSort = True
            .MultiSelect = MultiSelectEnum.None
            .AllowUpdate = True
            .AllowUpdateOnBlur = True
            .FilterBar = True

            .RowHeight = 22

            .TabAction = TabActionEnum.ColumnNavigation

            .MarqueeStyle = MarqueeEnum.HighlightCell

            .Columns(0).Caption = "ID"
            .Columns(1).Caption = "Type"
            .Columns(2).Caption = "Company"
            .Columns(3).Caption = "Name"
            .Columns(4).Caption = "Title"
            .Columns(5).Caption = "Address"
            .Columns(6).Caption = "City"
            .Columns(7).Caption = "Region"
            .Columns(8).Caption = "Postal Code"
            .Columns(9).Caption = "Country"
            .Columns(10).Caption = "Phone"
            .Columns(11).Caption = "Extension"
            .Columns(12).Caption = "Fax"
            .Columns(13).Caption = "Home Page"
            .Columns(14).Caption = "Photo Path"

            With .Splits(0)
                .AlternatingRowStyle = True
                .EvenRowStyle.BackColor = Color.LightYellow
                .EvenRowStyle.VerticalAlignment = AlignVertEnum.Center
                .OddRowStyle.VerticalAlignment = AlignVertEnum.Center

                .ColumnCaptionHeight = 30

                .DisplayColumns(0).Width = 42 'ID
                .DisplayColumns(1).Width = 52 'Type
                .DisplayColumns(2).Width = 218 'Company
                .DisplayColumns(3).Width = 168 'Name
                .DisplayColumns(4).Width = 168 'Title
                .DisplayColumns(5).Width = 150 'Country
                .DisplayColumns(6).Width = 257 'Address
                .DisplayColumns(8).Width = 56 'PostalCode
                .DisplayColumns(9).Width = 168 'City
                .DisplayColumns(10).Width = 90 'Phone
                .DisplayColumns(11).Width = 56 'Extension
                .DisplayColumns(12).Width = 93 'Fax
                .DisplayColumns(13).Width = 200 'HomePage

                For index As Integer = 0 To .DisplayColumns.Count - 1
                    .DisplayColumns(index).HeadingStyle.HorizontalAlignment = AlignHorzEnum.Center
                    .DisplayColumns(index).Locked = True
                Next
            End With
        End With
    Catch ex As Exception
    End Try
End Sub

EDIT:

Thats, before I make a change.

When I do it so:

SELECT ContactID, ContactType, CompanyName, ContactName, ContactTitle, REPLACE(Address,CHAR(13)+CHAR(10),' '), City, Region, PostalCode, Country, Phone, Extension, Fax, HomePage, PhotoPath, Photo FROM dbo.Contacts

or

SELECT ContactID, ContactType, CompanyName, ContactName, ContactTitle, REPLACE(REPLACE(Address, CHAR(13), ' '), CHAR(10), ' '), City, Region, PostalCode, Country, Phone, Extension, Fax, HomePage, PhotoPath, Photo FROM dbo.Contacts

or

SELECT ContactID, ContactType, CompanyName, ContactName, ContactTitle, REPLACE(REPLACE(cast(Address as nvarchar(max)), CHAR(13), ' '), CHAR(10), ' '), City, Region, PostalCode, Country, Phone, Extension, Fax, HomePage, PhotoPath, Photo FROM dbo.Contacts

The result is:

EDIT 2:

I need the table "Contacts".

This is my Database structure:

What was your attempt with the `REPLACE`? `REPLACE` can remove both a Carriage Return and Line Break, but it's difficult (impossible) for us to tell you why it didn't work, when we can't see the attempt. — Thom A, Oct 07 '19 at 08:09
@a.b_om you didn't post any database-related code, just some grid configuration code. If you don't want a CR+LF in the database, don't include it in your input, or filter the input before trying to write anything to the database. — Panagiotis Kanavos, Oct 07 '19 at 08:19
Please *remove* the grid code and add the *relevant* code. Where does the data come from? How do you write it to the database? Does it actually contain *both* CR and LF? Or does it only contain one of these? You won't have to replace anything in the query if you clean up the input in your code — Panagiotis Kanavos, Oct 07 '19 at 08:21
First of all, I have only drawn this table into this Truedbgrid. I don't have any code that refers to the database. Except you mean the Load_form class. Second, I can send at most 3 images with the 3 columns in the database, the 3 wrong entries on my program and the database structure. I don't think that helps much, or am I wrong? — a.b_om, Oct 07 '19 at 08:27
@a.b_om Please see [Replace a newline in TSQL](https://stackoverflow.com/a/951705/1115360). — Andrew Morton, Oct 07 '19 at 08:38
When I do it, then it returns it nothing for the Address. It make the same, how it do it at my first try(see question). — a.b_om, Oct 07 '19 at 08:44
Your attempt has the function `REMOVE` in it, not `REPLACE`. `REMOVE` is not a T-SQL function. This appears to be purely typographical. — Thom A, Oct 07 '19 at 09:02
Are you *sure*? I can't replicate the problem: [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=309d73d023935bd1c169557e0ec4beae) — Thom A, Oct 07 '19 at 09:06
As per my comment on your deleted answer: *"The* (likely) *reason `Address` has no values is because it doesn't exist in any of your 3 SELECT statements. `REPLACE(Address,CHAR(13)+CHAR(10),' ')` has no alias; so it is not the column Address"* Give you column an alias. — Thom A, Oct 07 '19 at 09:31

score 1 · Accepted Answer · answered Oct 07 '19 at 10:38

You haven't shown us exactly how you're binding the grid to your data source, but based on the observed behaviour it seems likely that it relies in some way on the names of the fields returned by the SELECT query.

Therefore, as mentioned in the comments, try giving the output of your REPLACE function an alias so that it has a recognisable name:

REPLACE(REPLACE(cast(Address as nvarchar(max)), CHAR(13), ' '), CHAR(10), ' ') As Address,

Right now, since that column has no alias, I would expect that it isn't binding to the "Address" column in the grid.

P.S. a as a separate issue, this:

Try
...
Catch ex As Exception
End Try

is an anti-pattern. It's fine to catch exceptions, but if you do that you need to log the exception details somewhere (e.g. in the Windows Event Log, or to a file). Otherwise if something does go wrong in your code you'll have no idea what it was and therefore no way of trying to fix it. You are throwing away important information which is intended help you as a developer to solve problems.

ah... So I misunderstood that before. Sorry I'm from Switzerland and my English isn't great. — a.b_om, Oct 07 '19 at 11:14

Remove the crlf in the SQL-database?

1 Answers1