Database -- Wrong Value Type 3

[atlanticdit]

Been wrapping myself around this issue for a week now. I have a user defined field in the Customer Maintenance screen (7.6.3c on SQL Server 2000) that has a drop down for two values-- Y and N . The max size for that drop down box in the CM screen is 1. The field in the ARCUSFIL_SQL table also has a max size of 1. The field type in the table is char .

The error happens this way:
I go into a customer account and make a selection of either Y or N. Then I hit save and I get the error message. Note that I do not get the error message if I don't change that drop down box but make other changes.

The error message is:

MSL Technical Error Info:

An error occured in MacMSL.dll (ver. 76.300.15.6)
DB Provider Error:
COM Error: Error
Code = 0x800A0D5D
Code meaning = Unknown error 0x800A0D5D
Source = ADODB.Command
Description = Application uses a value of the wrong type for the current operation.
Op : F4, fhRewrite
Table or Script: [servername].[database].DBO.[ARCUSFIL_SQL]

MSL Error Info:
Type: Parameterization Error on CreateParameter.
Function: GetValueString.
Field: USER_DEF_FLD_2
Value: N


I have tried rebooting the SQL server; changing the max size; value type in the SQL table; checked the MDAC versions; removing a piece of code that popped up a message box; etc.

Anyone have experience with this and can shed some light on it. I would much appreciate it.

Thanks,
Dave

 

[dgillz]

If you get rid of the drop down box and manually enter the Y or N, do you still get the error?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[atlanticdit]

Nope. Same error still comes up.

 

[dgillz]

Go to enterprise manager and go to this table and right click, design. Make sure the data type is char.

If it isn't someone has changed the table. If it is this sounds like a bug, I would report it to Exact.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[atlanticdit]

Nope. Char hasn't changed. Only time it did was when I changed it to try varchar, text, etc.

Thanks for the help. I think it will come down to reporting it as a bug.

 

[abaldwin]

Hello,

You said

The field in the ARCUSFIL_SQL table also has a max size of 1.

What do you mean by this statement.

The userdefined fields in the db should all be Type = Char Length = 30

Did you modify the table?

If so change it back IMMEDIATELY. I will elaborate if this is the problem.




Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[dgillz]

Nope. Char hasn't changed. Only time it did was when I changed it to try varchar, text, etc.

Huh?

Did you change it, or not?

also:

removing a piece of code that popped up a message box

What code was this?

You should never change the datatype or length of any field in the Macola database. If you have changed any fields, change them back ASAP.

You can use a combination of screen designer and Flexibility to control the users' input into the database.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[abaldwin]

BTW......another thought I had (well actually a question)

What did you mean by this

removing a piece of code that popped up a message box; etc.

Was this a piece of flex that you changed?

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

Sorry Don, Did not see your post.

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[atlanticdit]

Andy, Don,

Here's where I screwed up. When I saw that you said that the char field size should be a minimum of 30, I thought I had troubleshooted that already. But then I realized I had enlarged it but never to a size of 30. Once I did that, the error did go away. Thanks. Can't wait to graduate from being a newbie with this stuff.

Question then:
The reason I did this in the first place was because I have Flexibility code in the Order Entry screen that looks at this user_defined_field2 in the ARCUSFIL table. If there is a "Y" in that field for that customer then a message box pops up. That code wasn't working and I was able to find out that the reason was that even if there was a "Y" for that customer in that particular field, the value was being read as "Y" and then 29 spaces. My code was looking for just a "Y" with no other spaces. So the work around I came up with was to limit that user defined field to a field size of 1. The code then worked. So how do I get rid of those 29 trailing spaces? Some kind of TRIM command? I'm shaky on the TRIM command so any help would be appreciated.

 

[abaldwin]

Okay,

1st things first. NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER (Get the picture) Change or modify in any way the Macola tables in Enterprise manager.

Macola does not make straight sql calls. They have a conveluted middle layer that does all sorts of coversions and what not to make their product run on SQL.

Any other tables that you may have modified should be fixed either by you or a qualified VAR, or consultant.

As for you OE problem, I can say that there is a VERY simple fix.

Given the example code below.

declare an ado conneciton and an ado recordset object

Stsql = "SELECT user_def_fld_1 FROM arcusfil_SQL where cus_no = '*MISC'

recordset.open stsql, your adoc conn, etc etc etc.

You can then say

If TRIM(recordset.fields("user_def_fld_1"))]/b] = "Y" then
Do you msgbox code here
else
do whatever else.
end if

Trim is a vb function that strips the leading / trailing spaces (not the middle ones though) from the string parameter given.

YOu can post your code here if you would like to have someone take a look at it for you.

Most importantly though, you are asking for trouble to the millionth degree if you start messign with the Macola tables. You can create your own tables but
NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER (Get the picture)

mess with Macolas built in tables. Macola is not an object oriented program with data objects like many SQL apps out there.

Can not stress that enough.

If you need any help or flex work feel free to holler.

Andy "Dont Muck With Macola Tables" Baldwin

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[atlanticdit]

Andy,

Thanks for the TRIM syntax. All code is working correctly now.

Presently, I'm writing "I will never mess with Macola tables" a 100 times on the whiteboard in the conference room. When I first started with IT many moons ago, I had an outside vendor that I worked with who was impressed with how quickly I had learned some things. He then went on to say, though, that I knew enough now to be really dangerous. I think I'm back at that level with Flexibility and Macola databases.

Thanks for all the help.

Best,
Dave, the reformed mucker

 

[abaldwin]

Would love to see a pic of the 100 times it was written on your board! LOL

Anyway. We all started somewhere. If you need flex help just give out a shout.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[AKPolarBear]

Andy,

Are you sure about never changing a Macoal table?

 

[abaldwin]

Not to be too sarcastic but what part of

NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER NEVER (Get the picture)

was I not very clear on my stance about?

What in particular do you have in mind?

Thanks
Really I am not trying to be too mean here.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[AKPolarBear]

I was being sarcastic. That was being brought on by yet another round of Macoal woes on my end.

Yes, never change a Macoal table or much anything else.

In another post, it mentioned information found on the stone Macoal tablets that Moses used. I understand that on the back side of one of those, in hand written scrawl it said "thou shall never change thee tables lest it shall break for thee

 

[abaldwin]

To reinforce my stance here I would have to say that you should never change the structure of the macola tables unless directed to do so by Exact themselves. If you VAR has made data type or size changes, they were VERY wrong to do so unless Exact had instructed them to to.

The only reason I can see that Exact would have made changes on an individual companies system is if they had done something very particular in a dll for that customer, or if some upgrade process did not function properly.

One thing that I can say for the Macola product is that their database structure has been solid for a few years. Solid as in they keep it pretty much unchanged version to version and are not constantly messing with the structure of individual tables. They may add new ones for additional functionality that comes along (such as CC processing etc) but have not touched Item master for what.....EONS.

Why on Earth would an end user assume they could change the core data structure of a table and expect the system to function normally. I would say that if I am wrong someone from Exact or another expert here can tear me up on this post but I do not see that happening.

In the 10 years or so that I have been dealing with Macola I have NEVER had a reason to perform an unauthorized change to the macola data structure. I mess with the DATA daily. I add tables to the db all the time. I sometimes mess with these tables to add functionality to flex or vb apps. But I have never had a reason to change the structure.

Again. Have you too man making structure changes?????? And if so WHY?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[abaldwin]

Sorry did not see your post. Thought you were serious as I did not detect the sarcasm. Lack of a LOL or something.

Later.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[AKPolarBear]

You bring up a good point. We have several of the Peak enhancements installed and used daily.

Not that I think there is a problem with them but I wonder if any of those violated the rules? I don't think so but then I am not a programmer.

One of the reasons we have not moved to v300 are those enhancements and some other customizations they did for us. Nobody can guarantee me they won't break or work in the new version. Worse, some of those enhancements are now incorporated into that version which complicates things on a couple of levels and not just the program.

 

[kscheeler]

3rd party programmers who modify the source code will and have changed the tables, the MSL files that go along with them and the actual source code looking at all of that information. That's why some users can't upgrade to a new version until the 3rd party programmer updates their code.

I personally don't agree with it and we've stayed away from doing that. There's enough that can be done with Flex, VB, stored procedures, custom user interfaces, etc.. that you don't need to modify the source code / core tables anymore.

Kevin Scheeler