Concatenate field recordset convert strings with a carriage return

Databases
1

i have a recorset which is the result of a query which extracts a single row with 5 columns.
I can’t concatenate these 5 columns together with the carriage return character.

This is my example code:

Dim rsc as  RecordSet
Dim sql, v_id, CR, v_anagrafica, name, street, city As  String


CR = Encodings.ASCII.Chr(10)

v_id = 100
  sql = "SELECT * FROM client WHERE user_id='" + v_id +"' " 
  rsc = ConnectionDB.SQLSelect(sql)


  rsc.MoveFirst
    ' Anagrafica
    v_anagrafica = rsc.IdxField(3).StringValue + CR + rsc.IdxField(5).StringValue + CR + rsc.IdxField(6).StringValue
    Txt_anagrafica.Text = v_anagrafica
    
    v_anagrafica = rsc.IdxField(3).StringValue + Chr(10) + rsc.IdxField(5).StringValue + " " + rsc.IdxField(6).StringValue
    Txt_anagrafica.Text = v_anagrafica
    
    v_anagrafica = rsc.IdxField(3).StringValue + EndOfLine + rsc.IdxField(5).StringValue + " " + rsc.IdxField(6).StringValue
    Txt_anagrafica.Text = v_anagrafica
    
    v_anagrafica = Str(rsc.IdxField(3).StringValue) + chr(13) + Str(rsc.IdxField(5).StringValue) + " " + Str(rsc.IdxField(6).StringValue)
    Txt_anagrafica.Text = v_anagrafica
    
    name = rsc.IdxField(3).StringValue
    street = rsc.IdxField(5) 
    city =  rsc.IdxField(6)
    
    Txt_anagrafica.Text = name + chr(13) + street + " " + city

    Txt_anagrafica.Text = str(name) + chr(13) + str(street) + " " + str(city)

as you can see i have done various tests on a textfield object in xojo.
I used:

  • “stringValue” in the IdxField
  • I tried the Str () function
  • I used various returns Chr (13), Chr (10), EndOfLine
    with various encodings Encodings.ASCII.Chr (10) ect.

If I simply concatenate two static strings like “MickMouse” + Chr (13) + “Pluto” the field is filled in correctly.
Instead with concatenated rsc.IdxField (3) .StringValue does not work

Can anyone help me?

2

Just to check here. You mentioned TextField. Did you mean TextArea? TextField is single line, TextArea is multi-line. If you are in fact using TextField, you will only see the first line of a string that contains CR.

3

EndOfLine should work. The others (Chr(10) or Chr(13)) might work, depending on which platform (Mac, Windows, Linux) its running. EndOfLine generates the correct character(s) for the platform.

Another option is let your database do it. Syntax depends on which database you’re using, but look for a string concatenation function. Something like this for MySQL but the \n for newline might only work on Linux database servers.

SELECT CONCAT_WS('\n', Field1, Field2, Field3)
4

The example with EndOfLine isn’t consistent with the first one (using CR); it uses a space for the second line break.
Are you using the debugger and breaking to see these results (since each replaces the contents of the field)? There may be a refresh problem, if so.
Are there any unexpected characters in the database fields?
Windows or Mac?

5

I always use the method of putting the value in a variable before performing operations with it.

To have control of the correct value and give it a line return.

I’ll give you an example:

Dim Catena As String
Dim Valore_Campo As String
Dim cSQL_Consulta As String = ""

cSQL_Consulta = "SELECT * FROM client WHERE user_id='" + v_id +"' "
Recset = ConnectionDB.SQLSelect(sql)

If ConnectionDB.Error Then
  MsgBox("DB Error: " + mDB.ErrorMessage)
  Return
End If

If Recset<>Nil Then
  While Not Recset.EOF
    Valore_Campo = Recset.IdxField(3).StringValue
    Catena = Valore_Campo+EndOfLine
    Valore_Campo = Recset.IdxField(5).StringValue
    Catena = Catena + Valore_Campo
    
    Recset.MoveNext
  Wend
  Recset.Close
End If

TextArea1.Text = Catena
6

Hi, I tried with various solutions but always error:
Textfield
TextArea
Label

7

in the example there is an attempt with EndOfLine but not always an error,
the result of the variable is always:
NAME_CLIENT�ADDRESS_CLIENT�

The database is Mysql I try with CONCAT_WS and print only one line

8

the database is Mysql and the data is sample.
I trie with windows and Mac

9

Hi josè,
in the example code I have such a thing but it doesn’t print the final value as I want it with the carriage return, but it always prints this anyway:
NAME_CLIENT�ADDRESS_CLIENT�
i tried to print in:
TExtfield
TextArea
Label

10

I trie with unique mysql query to CONCAT_WS, but the result it’s same :
NAME_CLIENT�ADDRESS_CLIENT�

11

Thats weird. It must work.

What version of XOJO do you have?

What operating system do you want to run it on?

12

Check the text encoding of the string.

13

i try on string:
name_client = rsc.IdxField(3).StringValue
name_client = name_client.ConvertEncoding(Encodings.WindowsANSI)

but not work

14

XOJO 2018 version 3
Mac Os Monterey 12.0.1

15

Try DefineEncoding. If it doesn’t have an encoding there is nothing that ConvertEncoding can do.

16

SUPER! Now work!!! Thank you Tim.
I modify encodings with UTF8