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?

 

[Filip Brnic]

I think im onto something, the debugger was completely off, i had empty fields that i tried to insert into the table, now im repairing it, it had nothing to do with iznos fields in the first place, im so sorry but the debugger was indeed tripping me off

 

[Chris Miller]

Why are you doing these replaces at all, if you use controlsource, that's a two way binding, it reads from the contolsource and displays and it saves the modified value back to the controlsource. There is no need for all these replaces.

A data type mismatch error in such code actually says that eh textbox valkues are NOT given by a controlsource driven by the same field that you not want to save to again with all these REPLACES. You can't store a text into a numeric field, for example.

if there is an error, why does it INPUT the data into the table????? why is there an error than????

Dopes it? I'f so, then not by the replaces which error.

In the first place I'd recommend you just delete all these lines of code. Just use the controlsource of the controls. If you, for example, set

Thisform.iznoz1.ControlSource = "iznoz1"

Then this means the iznoz1 control will display the iznoz1 field value, the iznoz1.value will have the same data type as the field iznoz1 and the controlsource also saves changes made by users back. Therefore you won't have any data type mismatch errors.

You don't show how you set the controlsources, so we can only guess why things have a data type mismatch, but if you do as is normal for control binding, you will a) not need REPLACE statements at all and b) will never have any chance of data type mismatches as source and target of the control value are the same field (really same, not just equal or similar, the same field) and that has one data type and keeps that data type.

Or aer you using ALTER TABLE while the table is bound to controls?

I have no idea what exactly goes on, but one thing is clear already, you're doing a lot of unnecessary things.

 

[tomk3]

View attachment 333
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????

Do set step on before starting replace, set a breakpoint on the line giving the error, what shows debugger watchwindow for vartype(thisform.xxxx.value) ?

btw : I recommend adding a property odata to form, then using scatter memo name Thisform.odata for loading values, set textboxes,... to thisform.odata.xxx as controlsource, then for saving use gather memo name thisform.odata

 

[Joe Crescenzi]

Two things:

1. To know for sure what is going on there... put this in front of the line that is failing:

MESSAGEBOX("thisform.iznos2.value is type: " +VARTYPE(thisform.iznos2.value))

I created this to illustrate what it will look like when I don't assign a value before using it... it's C (Character):


Also, as a rule of thumb, it's faster to have multiple replaces on one line, instead of multiple lines because it compiles to a single command and pushes all the values at once.

replace ;
   X with something;
  ,Y with SomthingElse;
  ,Z with YetAnotherthing

 

[Chris Miller]

You're busy, maybe you got your remote control help from an appointment with SANJIB KR KANUNGO.

But three things are quite clear, aren't they?

1. Binding textboxes with a controlsource you have textbox value datattype = table field datatype automatically, you don't need any REPLACE statements as the controlsource does the job.
2. You have all table fields changed to numeric, you said "the table.fields are all N(3,2)" - by now they are hopefully more like N(6,2) or even more digits.
3. You have REPLACE statements that cause data type mismatch errors.

Well, then either texboxes like iznoz2 have values that are not numeric or the iznoz2 field data type is not yet numeric, really. You can verify the first issue with Joes last advice to break before the series or REPLACES and the table definition is at hand when you open the table designer for the table by first using it and then using the MODIFY STRUCTURE command in the command window, for example (therer are always multiple ways to get there).

You get a datatype mismatch because the textbox value and the field of the table have a different type, it doesn't matter which of them has a different type. If you think a textbox always only has text types as the control name suggests, not everything names suggests is true, it's often a good indication, but textboxes can bind to any data type and so it's very easy you have this mismatch. That error also is not ambiguous, it's stating the textbox.value and field are not the same type, for whatever reason. And the loop closes, getting back to poiunt 1 above, if you use textbox controlsources you will have no chance to have a differing data type in field and textbox, unless you do something insane as ALTER TABLE while the table is bound to a textbox. I'm not even checking, if that would work, but if it did, you'd change the field type to something that's currently not in the textbox.value, so of course you'd get that error.

 

[Joe Crescenzi]

.... That error also is not ambiguous, it's stating the textbox.value and field are not the same type, for whatever reason...

That one sentence explains it perfectly. There is no other cause possible.

The table has a type.

The textbox does not have a type until you either set an initial value in the designer, or programatically via Textbox.value = (something) before the form becomes active, where whatever you put here determines the type, OR, you bind it to a controlsource, where it sets the type from there.

Failing to set an initial value always will return a string, so if you try to replace a numeric table value with that string it will always trigger that error.

That said here are a pieces of advice.

1. If the form is used to add values, and there is no existing record to preload or bind to a controlsource, just put something neutral in the control from the in the designer or in the Init of the control. When in doubt, you can even use a variable as the controlsource such as m.MyDefaultValue, where that value was already assigned something like a zero.

2. If that is the case and you are appending a blank, then doing replaces with the control values, consider removing all those individual REPLCE x with somecontrol.value with an INSERT or simple a single replace as I mentioned earlier. It's faster and more efficient for the database engine to execute one line setting all the values at once than to keep changing one column at a time via multiple commands.

3. Lastly, a reminder that N(3,2) can only hold a value lower than 0.99 (that's 3 characters).