String variable adding line breaks

English
, ,
#1

I have the following code, which I am attempting to use to append a required input from the user to the contents of my “Notes” field:

	While sAnswer = "" 'Force the user to enter something for a reason for rejecting a data set. 
 		sAnswer = InputBox("What is the reason for rejecting the data set?", "Question:")
 		sAppend = " - " & sAnswer
	Wend
	msgBox sAppend

	oDB = thisdatabasedocument.CurrentController.ActiveConnection
	msgBox "UPDATE MEASUREMENTDATA SET ACCEPTED = FALSE, NOTES = NOTES + " & sAppend & "  WHERE ID = " & iMeasData

The first msgbox always shows what I would expect, but the second one has a line break at the first space in the entry into the input box.

#2

I think I may have found the problem. Strings only hold up to 65,535 characters.

#3 
Sub Test
  Dim s As String
  s=Space(100500)
  Msgbox Len(s) 
End Sub
#4

You’re right. There’s no way I came anywhere near touching that limit. There’s something else going on here. It might be an SQL problem.

#5

It’s definitely an SQL problem. MySQL doesn’t want to append a string that’s not in quotes. I tried single quotes, but it doesn’t like that, either, and it’s trying to get a DOUBLE value out of it. I might just have to come at concatenation from a different angle. Really, I need to have basic concatenate with a string containing double quotes. I’m not sure I can do that…

#6

Well, since I can’t get basic to escape double quotes, my workaround is to have basic perform the concatenation of the notes field by first querying for the original contents, having basic dump the concatenation into another string variable, and then perform an update to set the field to its concatenated value.

#7

There is no reason for this. Took reading your post multiple time to (I hope) understand just what you are attempting. You are looking to add comments to an existing field in a table, That is not clear and also it may be the field is empty to begin with.
.
The answer is to use CONCAT. See → MySQL UPDATE append data into column

Tested with MySQL 8.x:

Screenshot at 2022-02-09 13-34-31

Matters not if run from a macro.

#8

Apologies. You nailed it. I will attempt to clarify it.

[quote=“Ratslinger, post:7, topic:73755”]
and also it may be the field is empty to begin with.
[/quote]Indeed, that was the case in my test case.

I tried that, but to no avail.

Thanks for your help!

#9

Does that mean you do not want this?
.
My sample above showed it works. To go one step further, I did write a short macro to test. Used basic code you presented in question and my table and field. Main update lines:

sSQL =  "UPDATE TOOLS SET TOOL = CONCAT(IFNULL(TOOL,' '),' " & sAppend & "') WHERE ID = 1"
oStatement.executeUpdate( sSQL ) 'Execute the SQL command

.
Hard coded the ID for record to update but that is a trivial modification. Worked for empty field as well as existing data (new text appended). Again as earlier noted Matters not if run from a macro.
.
Not understanding your response. Why not present your problem as to what is not working, hopefully a sample and see what others have to offer? Otherwise what is the sense of asking for help? Just noting I tried that, but to no avail. seems you don’t want this answered.

#10

Not at all, I’m just communicating that is not the answer.

#11

I’m sorry to have wasted your time. To summarize:

The real problem was that I neglected to make sure the string to append was in quotes, which is why MySQL didn’t like it. (It wanted to treat it as a double.) I could not figure out how to pass the string in quotes in the SQL update command, since basic would just read the quotes as the end of the string, and I couldn’t escape them.

Solution: I worked around the problem and used two SQL commands to query the initial contents of the field, have basic do the concatenating, and then do an update command to put the concatenated string into the field.

#12

Just use chr(34):
.

sSQL =  "UPDATE TOOLS SET TOOL = CONCAT(IFNULL(TOOL,' '),' " & Chr(34) & sAppend &  Chr(34) & "') WHERE ID = 1"
oStatement.executeUpdate( sSQL ) 'Execute the SQL command

.
Result:
.
Screenshot at 2022-02-11 09-39-14
.
Sorry but I have a difficult time understanding just what you are looking for. It may help to add example(s).
.

No need to apologize. Not a waste if there is a resulting conclusion.

#13

I’m not looking for anything, anymore. I found a way to make it work by moving the concatenation over to the macro instead of SQL.

#14

Regardless, others viewing may want a similar answer.

#15

Right. I’ve described my workaround in my solution.

#16

Not needed. Instead of multiple commands, one can place directly in SQL.

#17

Yes, that’s great, if you can get the macro to pass the SQL command with the needed quote marks. In the original example, because the string being added had to be concatenated into the string serving as the SQL command, I could not, as I wrote in Post #11

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.