Help on comparison problem

[stm]

Hi,

I'm using ADO2.1 + SQL7 before, and everything is fine.
Until now, SQL server is upgraded to SQL2K, and now I met a problem on field with "char" datatype.

When inserting a value (e.g. "ABC") to a say char(5) field, sql2k will pad space to the field automatically, i.e. "ABC ".

If running SQL, comparison is fine, but once I retrieve records from that table using ADO and compare the value w/rs,
i.e.
if rs("field1") = "ABC" then ...
will return False now.

I need to either change the statement to
if trim(rs("field1")) = "ABC" then ...

or change the type to varchar(5), but both are not good idea.

Is there any method to solve this problem?

Thx a lot!

[lintz]

Why wouldn't Trim be a good idea? It would remove any additional spaces...

[RobDog888]

As I posted in the duplicate thread in the Databases forum,
using the char(5) will take up more space in the database. using
some other type like nvarchar(5) would be better.

varchar: Variable-length character data type, length 0 to 8000 bytes.
nvarchar: Unicode variable-length character data type.

[stm]

Hi,

Yes, I know the difference between char and varchar, but some old database was built using char and it don't need to trim() in those vb programs before the upgrade.

Now I need to fix this problem, update program to trim() every field is impossible, and update char field to varchar is also cumbersome as there are many many fields in databases.

So, I'm looking for any other better solutions.

Thx!

[RobDog888]

Kinda seems like your screwed either way, huh.

[Deepak Sakpal]

Originally posted by stm
Hi,

Now I need to fix this problem, update program to trim() every field is impossible, and update char field to varchar is also cumbersome as there are many many fields in databases.

So, I'm looking for any other better solutions.

There are only two ways to go, there is no other way. Either u modify ur program or change DataType. Modifying program code will be painfull for u, so u have to choose a way that will be less painfull.

[RobDog888]

You could also write a small program to execute ALTER TABLE
statements changing the filed types programmatically. Or shell
out query analyzer passing it the statements to execute and
pass the result message back to a file.