[Solved] Form Error Updating Current Record Right Truncation
[Solved] Form Error Updating Current Record Right Truncation
Hi I get this error when entering text in a form
Is there a way to allow unlimited text entry in a form?
My guess is I have exceeded some limit on how much text I can enter
SQL Status: 22001
Error code: -3401
data exception: string data, right truncation
There is an old post about this but it is to do with decimals
Thanks
Is there a way to allow unlimited text entry in a form?
My guess is I have exceeded some limit on how much text I can enter
SQL Status: 22001
Error code: -3401
data exception: string data, right truncation
There is an old post about this but it is to do with decimals
Thanks
Last edited by Mickey12 on Sat May 09, 2020 12:48 pm, edited 1 time in total.
Open Office 4.1.7 on Windows 10
Re: Form Error Updating Current Record Right Truncation
Is this a VARCHAR column? If so, what size have you defined it as, and how long is the string you are trying to enter?
If not, please provide a bit more information.
If not, please provide a bit more information.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Form Error Updating Current Record Right Truncation
Hi Yes
Text[VARCHAR]
Length 32
I am entering comments. Could be a few words or a few paragraphs.
Split dBase so I can only edit in SQL
Text[VARCHAR]
Length 32
I am entering comments. Could be a few words or a few paragraphs.
Split dBase so I can only edit in SQL
Open Office 4.1.7 on Windows 10
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Form Error Updating Current Record Right Truncation
Try increasing the size with an SQL statement like:
if you need more much more than 3000 characters then use
That will allow you to enter a book.
Don't forget to select View>Refresh Tables afterward
Code: Select all
ALTER TABLE "YourTableName" ALTER COLUMN "YourColumnName" VARCHAR(1000);
Code: Select all
ALTER TABLE "YourTableName" ALTER COLUMN "YourColumnName" CLOB;
Don't forget to select View>Refresh Tables afterward
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Form Error Updating Current Record Right Truncation
Thanks. Running that I get dependant object exists. I deleted relationships and tried again. Still get dependant object exists.
Open Office 4.1.7 on Windows 10
Re: Form Error Updating Current Record Right Truncation
The form control also has its own limit which is independent from the table's column limit. Open the form for editing and adjust the "Max. text length" property of the multi-line text box. A value of 0 seems to remove the limit.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Form Error Updating Current Record Right Truncation
This is the full error message when I try to convert the column into a CLOB:
Then rebuild "ExportView" from query "qExport" which I saved in the Base document for purposes like this one. Just in case that rebuilding fails for some unknown reason (it failed without error message here on my machine), restart the office suite and try again. Base is a bitch whereas HSQL is a Golden Retriever unless you jail that dog in a Base cage as an "embedded HSQLDB".
and it mentions the dependent object cliearly.1: dependent objects exist: PUBLIC."ExportView" in statement [alter table "Clients" alter column "Comments" CLOB]
Code: Select all
DROP TABLE "ExportView";
ALTER TABLE "Clients" ALTER COLUMN "Comments" CLOB
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Solved Form Error Updating Current Record Right Truncati
Thanks everyone for the great help. All good now.
I am learning valuable skills here.
I am learning valuable skills here.
Open Office 4.1.7 on Windows 10
Re: Solved Form Error Updating Current Record Right Truncati
You are working with 2 independent software products simultaniously. You are editing a HSQLDB through OpenOffice Base. The database driver executing database commands is the hsqldb.jar. The software sending commands to the engine and getting row sets in return is Base. The database driver does not see who or what sends the commands. It has no "notion" of your queries, forms and reports. It just takes commands and returns data, error messages sometimes. This is why some queries need to be stored as views. HSQL is aware of views and takes care that you do not change/rename any tables or columns that are referenced by a view. There is no tight connection between the 2 software components other than the JDBC standard which is why you won't get any more details about errors. You have to read and understand the scarce info from the error messages sent back from the database engine.
When you copy your view "ExportView" and paste it onto a table with option "append data", Base sends a command like:
INSERT INTO "Export" ( list, of, export, columns ) (SELECT list, of, view, columns FROM "ExportView")
When you store a modified record, Base sends an UPDATE ... WHERE "Primary Key"=X command
When you store a new record, Base sends an INSERT ... command.
When you delete a record, Base sends a DELETE ... WHERE "Primary Key"=X command.
This is the reason why Base can not edit any table without primary key. The PK is needed for the exact record position. And this is the reason why editable form records must refer to a single table including its primary key. We can not edit related tables in one form. But forms allow us to emulate relations by means of subforms so every (sub-)form includes a record set from one table.
Same issue with any other database engine connected to Base. Until version 2.0 OpenOffice was not able to create any relational database from scratch. It could connect to existing relational databases built with other tools. The only databases OpenOffice could build were file based (non-relational) pseudo-databases such as spreadsheet connections and dBase. The Base component came from popular demands for something "similar to MS Access". Access is a huge multi-million dollar product. The Base component is a tiny addition of some 30 MB to this office suite, underdeveloped since 15 years.
Same issue with any other database frontend you may connect to your HSQLDB. HSQL is a database product in its own right. It was not made for OpenOffice Base exclusively.
When you copy your view "ExportView" and paste it onto a table with option "append data", Base sends a command like:
INSERT INTO "Export" ( list, of, export, columns ) (SELECT list, of, view, columns FROM "ExportView")
When you store a modified record, Base sends an UPDATE ... WHERE "Primary Key"=X command
When you store a new record, Base sends an INSERT ... command.
When you delete a record, Base sends a DELETE ... WHERE "Primary Key"=X command.
This is the reason why Base can not edit any table without primary key. The PK is needed for the exact record position. And this is the reason why editable form records must refer to a single table including its primary key. We can not edit related tables in one form. But forms allow us to emulate relations by means of subforms so every (sub-)form includes a record set from one table.
Same issue with any other database engine connected to Base. Until version 2.0 OpenOffice was not able to create any relational database from scratch. It could connect to existing relational databases built with other tools. The only databases OpenOffice could build were file based (non-relational) pseudo-databases such as spreadsheet connections and dBase. The Base component came from popular demands for something "similar to MS Access". Access is a huge multi-million dollar product. The Base component is a tiny addition of some 30 MB to this office suite, underdeveloped since 15 years.
Same issue with any other database frontend you may connect to your HSQLDB. HSQL is a database product in its own right. It was not made for OpenOffice Base exclusively.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice