Data type mismatch

[Filip Brnic]

Hello, I'm facing a problem that is very easy to solve but due to the sheer amount of fields i have i struggle to find where it actually is,
Long story short, i have alot of fields that have the input mask (999.99) at first i had the data type mismatch because all the table fields were Character, so i created a form and tried to make the field Numeric(3,2)and it worked when i tried with 1 field, but now, i go back change the code, and it still gives me the same error, The fields are all (999.99) the table.fields are all N(3,2) So im struggling to understand what im doing wrong.

Is there a way to send the form sct and scx and have someone help me?

 

[Shane_SixOne]

LTrim(Str(Table.Field))

 

[Joe Crescenzi]

Just to be clear, FoxPro numeric types are based on the character length, including the decimal itself, so 999.99 is actually N(6,2), because that is 6 characters.

N(3,2) will only hold 1 number before the decimal, then the decimal, then the 2 numbers after the decimal. (Example 1.99).

 

[Chris Miller]

I did a testform and it works that way. In N(3,2) fields you get truncated value, not really what you entered, because you obviously didn't read the help topic documenting how to specify N fields in VFP.

But there has to be another reason for the error.

 

[tomk3]

Just to be clear, FoxPro numeric types are based on the character length, including the decimal itself, so 999.99 is actually N(6,2), because that is 6 characters.

N(3,2) will only hold 1 number before the decimal, then the decimal, then the 2 numbers after the decimal. (Example 1.99).

I think it must be N(4,2) for 1.99. N(3,2) gives 2.0 for 1.99 in table.

 

[SANJIB KR KANUNGO]

Ok. If u like to join with me on any desk, so that I can try. Preferably on Sunday.
I am from India.

 

[Joe Crescenzi]

I think it must be N(4,2) for 1.99. N(3,2) gives 2.0 for 1.99 in table.

Oops.. You are correct.

1.99 is 4 characters.

N(3,2) gives you a max value of .99
N(4,2) gives you a max value of 9.99
N(5,2) gives you a max value of 99.99
N(6,2) gives you a max value of 999.99, which, as I mentioned earlier is what the OP needs.

 

[tomk3]

Oops.. You are correct.

1.99 is 4 characters.

N(3,2) gives you a max value of .99
N(4,2) gives you a max value of 9.99
N(5,2) gives you a max value of 99.99
N(6,2) gives you a max value of 999.99, which, as I mentioned earlier is what the OP needs.

Hmm, I am afraid , VFP is a little bit more flexible in data , not in form input property.
Pls. try
CREATE CURSOR c_dummy (test n(3,2))
INSERT INTO c_dummy (test) VALUES (1.99)
INSERT INTO c_dummy (test) VALUES (.99)
INSERT INTO c_dummy (test) VALUES (30.99)
INSERT INTO c_dummy (test) VALUES (30)
INSERT INTO c_dummy (test) VALUES (30000)
BROWSE

Anyway, its a bad idea not to check for sufficient or more space in fields.

 

[Joe Crescenzi]

Hmm, I am afraid , VFP is a little bit more flexible in data , not in form input property.
Pls. try
CREATE CURSOR c_dummy (test n(3,2))
INSERT INTO c_dummy (test) VALUES (1.99)
INSERT INTO c_dummy (test) VALUES (.99)
INSERT INTO c_dummy (test) VALUES (30.99)
INSERT INTO c_dummy (test) VALUES (30)
INSERT INTO c_dummy (test) VALUES (30000)
BROWSE

Anyway, its a bad idea not to check for sufficient or more space in fields.

Here's the result, where every value is still no more than 3 characters.

 

[tomk3]

Hey Joe,

thats the result I get, too.Sorry, english is not my main languange.
I just meant that it is "strange" (to my opinion), N(3,2) should give *** for 31, hmm.
Anyway in real world N(3,2) is nonsense to me, it should be N(3,1) or N(4,2) , depending on the needs (because of the . counting as one char)

 

[Chris Miller]

The definition is mainly about the length, the number of decimal places is not fixing the decimal point, ie in an N(3,2) field you can also store 999, the 2 decimal places can be reduced to none and then you also don't need the decimal point. On the other end you can start a number with the decimal point, like .99, which is equal to 0.99.

What you're not allowed is more decimal places than specified. So in an N(4,2) field, which could have a length 4 with 3 decimal places - .999 - that is stored as 1.00, rounded up, but it allows 9999 and only higher numbers then cause **** which means (overflow, loss of data).

The value is interpreted as higher than anything, I think, You get .T. for test>9999999, even though clearly the test field width doesn't allow more than 999 in your N(3,2) test case.

But all that, including comparisons with overflowed fields, don't cause a datatype mismatch error.

 

[Joe Crescenzi]

Hey Joe,

thats the result I get, too.Sorry, english is not my main languange.
I just meant that it is "strange" (to my opinion), N(3,2) should give *** for 31, hmm.
Anyway in real world N(3,2) is nonsense to me, it should be N(3,1) or N(4,2) , depending on the needs (because of the . counting as one char)

I've always found it odd that there's any flexibility at all. (3,1) should mean that the decimal is always in the middle, making 9.9 the highest value.

The fact that it can also represent a max of 999 in cases where you push over a big number and it decides to move or remove the decimal is something they really shouldn't support, but I guess they assume people won't mind that they use rounding and give people flexibility regarding the decimal.

So, they enforce it when typing directly into a field, and give that wiggle room when inserting, appending or replacing values.

 

[gbcpastor]

Hello, I'm facing a problem that is very easy to solve but due to the sheer amount of fields i have i struggle to find where it actually is,
Long story short, i have alot of fields that have the input mask (999.99) at first i had the data type mismatch because all the table fields were Character, so i created a form and tried to make the field Numeric(3,2)and it worked when i tried with 1 field, but now, i go back change the code, and it still gives me the same error, The fields are all (999.99) the table.fields are all N(3,2) So im struggling to understand what im doing wrong.

Is there a way to send the form sct and scx and have someone help me?

Hey there, I had this problem in the past. What I was doing was creating a "Character Type" variable in the form. I created it so I could enter in a masked value ("999,999.99"). When I tried to set the valued of the field in my table to the value in the table, it errored out.

Then I realized I was putting a character value in a numeric field with 2 decimals. FAIL! So first I converted the value in the form field to numeric and that worked fine. But for efficiency, I placed the actual numeric field from the table directly on the form. No conversion needed and no masking. Plus, validation was the default.

 

[Chris Miller]

I set the controlsource to an N field to test what could cause a datatype mismatch and used the Inputmask anyway - that also works without error, so you can use inputmasks when binding to character fields (or properties, variables) and also when binding to numeric fields.

So, Filip, whatever the source of the error is, it's surely not the combination of N type fields with InputMask, even when your N field definition is too small.

Add error handling to find out where/when the error happens, exactly. See ON ERROR in the help.

 

[Steve Meyerson]

Filip,
Just a reminder. Don't forget to add one to the field length if you need to allow a negative number. For example -99.9 could overflow a N(4,1) field. Should be N(5,1).
Steve

 

[Filip Brnic]

Hello, ive found out that it actually SAVES some of the fields into the table, but it absolutely makes zero sense why some of them arent saved??? all the fields are the same, even when i create the xml later, it actually saves 95% of the fields except like 15 of them, this is something so dumb, i tried using the debugger, clicking suspend it sends me on the line of code that works for every other field but that one, i check its properties and codes and its the same way its with the other fields???
Id appreciate someone connecting with me on anydesk/discord so i can show you the code, alot of the things you guys wrote worked good, when i saved the data with only n(3,2) there was no decimals, so whoever said i should put n(6,2) was correct, i tried float as well but still the same error. Id really really really appreciate connecting with someone so i can share screen the whole form, you might know then whats the problem... Thanks regardless!!!!!

 

[Chris Miller]

i tried using the debugger, clicking suspend it sends me on the line of code that works for every other field but that one

Then post that one line and the others. There must be a difference you're blind for.

Could be something like having a dot instead of a omma in a function call, so instead of separating two parameters, you have a dot between them, which could work between digits and make it one float numeric parameter instead of two integers, STR(field,6.2) instead of STR(field,6,2), for example. I'm not saying it's STR, it can be anything that has two or more parameters you separate wrong that way. Well, and that would cause the data type mismatch error. It'd not be about the data type of the field, but about the data type of the other two parameters.

 

[tomk3]

Hello, ive found out that it actually SAVES some of the fields into the table, but it absolutely makes zero sense why some of them arent saved??? all the fields are the same, even when i create the xml later, it actually saves 95% of the fields except like 15 of them, this is something so dumb, i tried using the debugger, clicking suspend it sends me on the line of code that works for every other field but that one, i check its properties and codes and its the same way its with the other fields???
I

Maybe for start 2 screenshots will be enough, one for working, one for not working
They should show the source of the line giving the error, the content of the variables used in in this line and the fieldtype used in this line (maybe with list structure in the vfp screen). I myself prefer set step on or a breakpoint on the offending line and a useful filled watch window

 

[Joe Crescenzi]

When debugging complex forms with lots of controls and fields, one approach is to isolate just one control that doesn't seem to work onto a test form so you eliminate any chain reactions that come from the complex forms with a lot of controls. It's when you have a simple form with a single control, and nothing more than a save button for the one control that you can get a clear view of what's going on.

With that in mind, here are some common issues I've seen over the years.

1. See if there is a Data Session embedded in the form by using a right click on the side of the form editor. I never use them, but sometimes they point to outdated versions of tables.

2. Make sure the tables reflect enough space to hold anything the user may type, so as I mentioned earlier, N(3,2) can only hold a value of .99 or smaller (the decimal counts).

3. If the controlsource is a column without the alias (price vs mytable.price), be sure there isn't a variable with the same name.

4. If you are not loading an existing record into it at the start, and are starting with an empty form, then plan to save the values only if they enter something and hit a save button, you need to make sure you give your controls a default value for any non-character controls. You can simply set the value in the designer to 0.00 for example. Then the control knows it's numeric. Just setting a numeric format or input mask of 999.99 doesn't necessarily mean it will become a numeric box. It's just dictates the layout such as 999-999-9999 as a phone number.

 

[Filip Brnic]

Then post that one line and the others. There must be a difference you're blind for.

Could be something like having a dot instead of a omma in a function call, so instead of separating two parameters, you have a dot between them, which could work between digits and make it one float numeric parameter instead of two integers, STR(field,6.2) instead of STR(field,6,2), for example. I'm not saying it's STR, it can be anything that has two or more parameters you separate wrong that way. Well, and that would cause the data type mismatch error. It'd not be about the data type of the field, but about the data type of the other two parameters.

It starts giving the error from where the comment is... I know that im not missing anything, like even the debugger itself gives me more then 20 error windows, but only stays at the bottom of the code, basically, but if there is an error, why does it INPUT the data into the table????? why is there an error than????