I get "error inserting the new record" and cannot do ANYTHING with Base

English
, , , , , , ,
#1

I’m trying to build a database, but occasionally am told I have an error inserting information into my table – "Value too long in statement [INSERT INTO (Name, fields) VALUES(?,?,?,?,?,?,?,?,?)]

I’m using 64-bit Windows 7 and LibreOffice 5.2.5.1. Once the error occurs, almost anything I try to do brings up the error statement. Click to position the cursor in a new cell, ERROR. Click to save data, ERROR. Click to quit, ERROR. Try to insert data in a cell, ERROR. (Of course I could quit without trying to save the new data, but then I’d lose a lot of entries.)

Once it starts, it will not end. How do I make it go away?

#2

obviously there an SQL INSERT statement going wrong somewhere … perhaps in a macro? It seems that Name, fields, and values are not instantiated at the time of insertion

#3

You should not “…lose a lot of entries” just the one you are working on. You do not give any information such as working on a form or is it direct entry to a table? Is there a SPECIFIC error being generated? If a form is it a table grid control or individual controls? Does this start to happen on a specific field of when you go to a new or different record?

There are possibilities. Controls are pointing to incorrect table fields or the data being entered in a field is larger than the defined field in the table. Or possibly the type of data being entered is not what is defined in the table.

When you get the error, use the Navigation Bar (from menu select View->Toolbars->Form Navigation) and click on the Undo Entry icon (just to the right of the diskette icon) and that should clear the immediate record problem. Then figure out where things are not matching up.

Will need more information if further assistance is needed.

Edit:

If this is your error:

image description

it is because the table field in this instance was defined to be 10 characters and the entry is more characters than allowed. Undo the entry (as mentioned above or remove some characters from field) then if needed adjust the field in the table design to be larger.

#4

When I click “More” on the error statement it says
SQL Status: 22001
Error code: -124

Value too long in statement [INSERT INTO “CoinFolk” ( “Address”,“City”,“E-Mail”,“First Name”,“ID”,“Last Name”,“Phone”,“ST”,“Zip+7”) VALUES ( ?,?,?,?,?,?,?,?,?)]

It is one table, no calculations, and everything is a text field. I’m pretty sure I made all the cells large enough for any data I plan to enter in that cell. And - when I left click on any cell, before I’ve even tried to enter anything, I get the error. I don’t even get a chance to try making an entry. I can’t even save my work and try to fix things.

A detail which may or may not be significant: the big red mark in your example has a white X in it. The one I get has a minus sign. Another detail: I did a reasonable amount of programming from 1960 up to about 1995, but decided then that it was more work than it was worth to keep up. I’m semi-literate here, but when I speak of programming, I’m way out of date. How many languages have appeared and disappeared in that time?

#5

OK, I don’t think you got the last part of my answer. The error has nothing to do with the size of the controls on the form (cells as you put it). It is the actual table. In my example displaying the error, The TABLE field (NOT on the FORM) is defined to be up to 10 characters long. The entry in the field on the form was more than 10 characters thus causing the error. So, How is your table defined compared to what you are entering? BTW - the red X is just an icon- depends on system set-up.

#6

Also, my displayed error is exactly the same - SQL Status: 22001 Error code: -124 leading me to believe your problem is in the table definition of the fields.

#7

Another thing to keep in mind, the error is showing ALL the fields you are trying to insert and not just the one presenting the problem (although it can be more than one). Best bet is to verify all field lengths as defined in the table you created.

#8

In your original question you stated you occasionally get this error. In the posted answer you state “And - when I left click on any cell, before I’ve even tried to enter anything, I get the error. I don’t even get a chance to try making an entry. I can’t even save my work and try to fix things.” Which is it - occasionally or always? Please clarify.

#9

Once it happens, it KEEPS happening. Always.

#10

This is certainly something relatively easy to solve as I have stated. However I cannot look at what you see. I have given you some Karma. Can you post a sample of you DB for examination without any confidential Info of course. Even if the tables are empty. Just make a copy of yours and empty table if necessary.

#11

Once I threw away that last line of data, I could save the rest. Since then, I save every time I add a line, and live with the situation. I think I’m okay now.

#12

Your last comment finally gave some info I’d been asking for. You are using a Table grid for entering data. In a table grid, when you move from one line to a different one, it tries to update the record from the line you just left. If it finds an error it will not proceed until the error is corrected or the input erased. The error you received was just as I have mentioned previously - too many characters were placed in a field compared to the Table definition description.

#13

I am having something like this problem (BaseV 5.4.5.1). Made the D/B, got three records entered and then it stopped accepting records, I assumed it was subforms I had set up but did not use (since the error implicated the "statement (insert into Table ((and then it listed all but 6 of the 28 fields)). So after deleting the DB, all forms and tables I rebuilt the DB w/o subforms. Now it won’t accept anything. Not sure where to turn. My woes sound similar to this case. Thanks, John G

#14

@JG15 Try starting in Safe mode - from menu under Help. Otherwise, if still doesn’t help, please post this as a new question referencing this question. Explain with specifics and include a sample .odb (no personal or confidential info please)

#15

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