Line return problem when editing free fields notes with another program

Question

I need to make an application editing notes on a database sage 100 Cloud.

When I modify the note (Table dbo.F_COMPTETNOTE, column CT_Note) the new lines are recorded but do not appear in sage gescom.

• My application is written in C# .NET 6.0
• The database is collated with French_CI_AS

Did you have any idea why I have this problem apart the simple response "Encoding problem"?

connection is of type SqlConnection from lib Microsoft.Data.SqlClient the query with the problem is

request = "UPDATE F_COMPTETNOTE SET CT_Note = @Content, cbModification = @cbModification WHERE CT_Num = @CT_Num";
SqlCommand cmd = new SqlCommand(request, Program.connection.conn);
SqlParameter p1 = new SqlParameter("@Content", SqlDbType.VarChar);
p1.LocaleId = 0x040c;
cmd.Parameters.Add(p1).Value = cleanText;
cmd.Parameters.Add("@cbModification", SqlDbType.VarChar).Value = timeString;
cmd.Parameters.Add("@CT_Num", SqlDbType.VarChar).Value = NoteId;
cmd.CommandTimeout = 5;
int ret = cmd.ExecuteNonQuery();
cmd.Dispose();

I already forced conversion from Utf16 to Utf-8 or ANSI or latin1 but nothing seem to work

Edit1:

Table structure

USE [PROJI13]
GO

/****** Object:  Table [dbo].[F_COMPTETNOTE]    Script Date: 12/02/2024 18:04:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[F_COMPTETNOTE](
    [CT_Num] [varchar](17) NOT NULL,
    [cbCT_Num]  AS (CONVERT([varbinary](18),[CT_Num])),
    [CT_Note] [varchar](max) NULL,
    [cbProt] [smallint] NULL,
    [cbMarq] [int] IDENTITY(1,1) NOT NULL,
    [cbCreateur] [char](4) NULL,
    [cbModification] [datetime] NULL,
    [cbReplication] [int] NULL,
    [cbFlag] [smallint] NULL,
    [cbCreation] [datetime] NULL,
    [cbCreationUser] [uniqueidentifier] NULL,
 CONSTRAINT [PK_CBMARQ_F_COMPTETNOTE] PRIMARY KEY CLUSTERED 
(
    [cbMarq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [ICT_NOTE] UNIQUE NONCLUSTERED 
(
    [cbCT_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] ADD  DEFAULT ((0)) FOR [cbProt]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] ADD  DEFAULT ('CSQL') FOR [cbCreateur]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] ADD  DEFAULT (CONVERT([datetime2](0),getdate())) FOR [cbModification]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] ADD  DEFAULT ((0)) FOR [cbReplication]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] ADD  DEFAULT ((0)) FOR [cbFlag]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] ADD  DEFAULT (CONVERT([datetime2](0),getdate())) FOR [cbCreation]
GO

ALTER TABLE [dbo].[F_COMPTETNOTE]  WITH NOCHECK ADD  CONSTRAINT [FKA_F_COMPTETNOTE_CT_Num] FOREIGN KEY([CT_Num])
REFERENCES [dbo].[F_COMPTET] ([CT_Num])
GO

ALTER TABLE [dbo].[F_COMPTETNOTE] CHECK CONSTRAINT [FKA_F_COMPTETNOTE_CT_Num]
GO

Answer 1

Well the correct approach is as follow

1. pass an DateTime object rater than the string representation replace
2. the eventual \r by nothing and after replace the \n by \r

DateTime dt = TimeProcess.UnixTimeStampToDateTime(TimeProcess.TimeStampUnix());

string  request = "UPDATE F_COMPTETNOTE SET CT_Note = @Content, cbModification = @cbModification WHERE CT_Num = @CT_Num";
SqlCommand cmd = new SqlCommand(request, Program.connection.conn);
SqlParameter p1 = new SqlParameter("@Content", SqlDbType.VarChar);
p1.LocaleId = 0x040c;
cmd.Parameters.Add(p1).Value = cleanText.Replace("\r", "").Replace("\n", "\r");
cmd.Parameters.Add("@cbModification", SqlDbType.DateTime).Value = dt;
cmd.Parameters.Add("@CT_Num", SqlDbType.VarChar).Value = NoteId;
cmd.CommandTimeout = 5;
int ret = cmd.ExecuteNonQuery();
cmd.Dispose();