[Solved] Invalid Value

[TicklishPig]

I have created a database which refers to a pre-existing table (dbase from FoxPro). I used the form wizard to create a form for adding records to this table. This table has several columns of the "decimal" field type with length = 4 and decimal places = 0.

Whenever I put a 4 digit number into one of these fields in the form and attempt to save the new record, I get two error windows. The first says:

"Error inserting the new record
invalid value for column '[column name]' " "OK" or "More"

Clicking "More" presents me with a list of 3 errors:

"Error inserting the new record"
"SQL Status: HY000 invalid value for column '[column name]' " and
"The "[column name]" column has been defined as a "Decimal" type, the max. length is 4 characters (with 0 decimal places). The specified value is longer than the number of digits allowed."

If I put a 3 digit number in the field there is no problem.

Now, that seems pretty clear to me: it thinks the defined field length is less than the number of digits I'm entering. I have gone to the "edit" screen of the table being referenced and have confirmed that the troublesome columns are a "decimal" field type with length = 4 and decimal places = 0. So I figure I'll just change the field length to 6, just to give some wiggle room. But, when I try to save the table after changing the length of one of these columns to 6 I get the following warning:

The column "[column name]" could not be changed. Should the column instead be deleted and the new format appended?" "Yes" or "No"

"No" and the table edit window closes without saving.
"Yes" gives me the following error:
"Error while saving the table design invalid value for column "PERCENT". And then: "SQL Status: HY000" "The "PERCENT" column has been defined as a "Decimal" type, the max. length is 2 characters (with 1 decimal places). The specified value is longer than the number of digits allowed."

What? I haven't even attempted to change the size of the "PERCENT" column. Why is that coming up? Apparently this is an additional issue concerning saving the table design. So, I change the "PERCENT" column size to 4, for good measure, and when attempting to save the table design I get the message that the column could not be changed and asking if the column should instead be deleted and the new format appended. Regardless of my answer I'm cycled back through a series of error messages again.

I have checked the .dbf table to make sure it was not read only.

I get these same errors when I directly edit the table and put a 4 digit number in one of these columns and attempt to save it. Oddly enough, this table already has 4 digit numbers in these fields in previous records.

Any thoughts as to why I can not add new 4 digit numbers to this table?

Win XP Pro
OO 2.4.1

[Safway]

I have seen this message "The column '[column name]' could not be changed. Should the column instead be deleted and the new format appended?" when the data in my column didn't apply to the new condition I was trying to apply to the column. For example, if I had text in the column and then went to try to tell OpenOffice I wanted this column to now be "Decimal" type, I would get this message. Maybe that is what is happening?

I believe your .dbf is simply a text file. Look at the .dbf file with a text editor and particularly the column headings. If the column headings are simply text, say, COLUMN_HEADER_NAME,N,4,2, then you can manually alter that setting.
I believe:
The N stands for Numbers
The 4 stands for digits to the left of the decimal
The 2 stands for digits to the right of the decimal.

If none of these ideas work and if this database is an ODB file, then can you attach it to this issue and I will download it and take a look?

[TicklishPig]

The column headings don't seem to have any attributes other than the field name when I view it with Notepad. Although, when I edit it in Base, it is evident that Base was able to correctly parse the field type and size correctly.

The file is attached. I don't know if it is ODB.

[Safway]

Hmmmmm, Whenever I put a 4-digit number in these fields, I don't get any kind of error. When I try to put a 5 or higher digit, I get an error as expected. I am using OO.o version 2.4.0.

These are the steps I took to look at your data and test.
* Extract your zip file to get to the DBF file
* Renamed ShowStats.DBF to ShowStats.dbf (I am using Linux and case matters)
* Opened OO.o and imported the file this way
* File | New | Database
* Connect to existing where type = dBASE
* Next
* Browsed to the ShowStats.dbf directory and selected this directory
* Next | Finish | Save
* Looked at the table in OO.o and things look fine and also realized that the dbf format in an of itself does not support defining relationships!!!!!
* Tool | Relationships provides the message
* Used the Wizard to create a form
* Selected Tables
* Right-clicked on ShowStats table and selected Form Wizard
* Selected all fields | Next | Next | Next | Next | Next | Finish
* Tried to put a four-digit number into Names and RC and they both worked without problems (notice I didn't change the table's field properties to include five decimal places. I kept it at four!!!)
* Tried to put a five-digit number into Names and RC and they both gave the error I was expecting
* Tried changing the table's field properties to see if I can duplicate your other problem and I can
* Selected the tables view
* Right-clicked on ShowStats table and selected Edit
* Changed the "RC" field to have a length of 6.
* File | Save and got the error
* I looked at your ShowStats.dbf file using Calc and found what I believe is a discrepancy in the amount of characters allowed. See the screenshot.
This lead me to think whether OpenOffice and the dBASE format think of the decimal point as a character or not. . . . and this lead me to think of changing the PERCENT field so OpenOffice and dBASE are both happy. (This goes back to my earlier comment of "when the data in my column didn't apply to the new condition I was trying to apply to the column."

So, I changed the PERCENT field to length of THREE and then tried again to apply the change to Names and RC fields and it worked.

I know that in general when you write a table in a database in some database systems, it re-writes an entire field to satisfy a condition. This might have been what OO.o was trying to do and (not matter what field you were trying to adjust) it knew the PERCENT field was off and was throwing this error. Once that error was taken care of, it worked as expected.

[TicklishPig]

Safway, Thank you very much for your efforts to help troubleshoot this. The steps you outlined to try to duplicate my problem with my dbase file are essentially what I did originally. Just to be sure I started over, following your steps. The result was no different than the first time- no joy. Additionally, I followed the same steps on a Linux box I have access to, and all worked correctly: I could input a 4 digit number in a form and save the record, and could even edit the table to change a field length and save successfully. Thus, my conclusion is that there is something wrong with my installation on this Windows box, since you confirmed there is nothing seriously wrong with the data file.

I shall run the OO.O repair utility and see if that fixes the issue. If not, I'll uninstall/reinstall OO.O with hopes that that will fix things. I'll post the results, either way.

[Hagar Delest]

Before reinstalling, you can try to reset the OpenOffice.org user profile.

[Safway]

I can duplicate your issue on a Windows XP computer; however, I believe I figured out your problem.

You dbf file doesn't work well with OO.o on Windows. I don't know if it is how FoxPro exported it (maybe a DOS formatted file versus Windows formatted file issue) or if Windows itself is getting involved with OO.o in processing the file. If I get the raw data from the dbf file and then imported that into OO.o, then things worked. Here is how I made things work in Windows XP.

* Start OO.o Calc
* Start OO.o Base and open your database
* Open your ShowStats.dbf file from within Calc
* Change the column headers to what you would expect (i.e. change PERCENT,N,4,1 to PERCENT)
* Select the entire spreadsheet (click the small box between the A column and the 1 row)
* Copy the entire spreadsheet (CTRL + C)
* Select the tables view from your currently-opened database
* Right-click within the tables section and select to Paste the definition and data
* Follow the wizard
* Once the pasted table is in the database, change the field types to what you expect

Note, the field type is (length of characters in field + 1). I think the +1 is to account for the negative sign possibility a decimal number might have. In other words:
ATT = field length of 4
CARDS = field length of 5
Names = field length of 5
RC = field length of 4
etc.

[TicklishPig]

My uninstall/reinstall of OO.o made no difference. I installed OO.o on a different Win XP system and had the same problem, so that confirms the Windows problem, what ever that is. I was just looking into figuring some other way of importing the data into a new table when I noticed your latest post, safway.

Thank you very much for saving me a day's worth of research and trial-and-error. I was able to follow your instructions to successfully import the data. And now am ready to take on the subject of Reports.

I'll mark this tread "Solved", even though it is just a work-around - I'm just as satisfied.