Trim required strings before post

[hughespa]

Hi,

Using D2010.

I'm running against both SQL Server and PostgreSQL DBs.

Is there a point in code where I can trim leading and trailing spaces from required (not null) fields to prevent users entering one or more spaces rather than valid characters (e.g. A..B, 0..9)

I know I can code for this in field validate events or dataset before post events but I would find the option to always trim required (not null) fields be very useful.

I have one table in particular that accepts a 4 character, user defined code where they can enter a "meaningful" code along with other details. It now has records with codes 'A', ' A', ' A' and various other combinations. They all work but aren't very friendly - I don't know what makes them do stuff like this or how they use them...

Thanks in advance.

Regards, Paul.

[tobias_cd]

Hi Paul,

you may try "Options.TrimFixedChar" of TUniQuery.

Regards,
Tobias

[hughespa]

Hi,

Thanks for your reply.

I have tried TrimFixedChar and TrimVarChar but I am still able to post a record containing all spaces in the required code field and it ends up in the DB as a valid record.

I'm also using a modified SQLServerUniProvider which has the line:
TOLEDBREcordSet(Obj).SetProp(prEnableEmptyStrings, False);

This normally treats an empty string as a null and prevents posting an empty string if the field is a 'not null'

I think maybe TrimFixedChar and TrimVarChar are occuring after the check for an 'empty string.

Regards, Paul.

[Dimon]

I'm also using a modified SQLServerUniProvider which has the line:
TOLEDBREcordSet(Obj).SetProp(prEnableEmptyStrings, False);

Was the problem solved?

[hughespa]

Hi Damon,

No, it's not solved.

I didn't realize that the TrimXX options only affected data being fetched. I'm sure when I tested it they were trimming on setting the values too.

What I was hoping for was to be able to automatically trim any required (not null) string fields when they are posted to the DB so that e.g. you could not post a value such as 'A ' (A plus 3 spaces) to a required field (it would always get trimmed to just 'A' . Similarly, you could not post ' ' (4 spaces) to a required field (it would be trimmed to an empty string or preferably cleared to null)

Can you understand my explanation?

I know this can be achieved for each individual query or field but it would be nice to have an option on the connection (or somehow I could amend the UniDAC source code) to apply this rule to every write of a required string.

Thanks for your time.

Regards, Paul.

[Dimon]

I can not reproduce the problem.
Please, make sure that the UniQuery.Options.TrimVarChar and UniQuery.Options.TrimFixedChar properties are set to True.