adChar vs adVarChar

Question

adChar vs adVarChar

scott clemons

Mr or Mrs. 500

Points: 550
More actions
March 6, 2006 at 3:50 pm

#167996

Using ADO to send value to a SQL 2000 table with field named PNParent of nvarchar(25).
Initially the page used adChar as the datatype specified in the input parameter. Changed to adVarChar.
Now, PNParent contains some values that use all 25 characters. The data is to the left with white spaces completing the 25 character length to the right.
Was the adChar responsible for the extra white space?
Can the white space be removed with:
update TableName
set PNParent = RTRIM(PNParent)
Are there issues that I have not considering?
Also, PNParent is a primary key in this table and referenced in other tables. Can the same method be used to remove the white space from PNParent in those tables?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Yes, adChar is similar to T-SQL CHAR data type, and it will pad the data with white-spaces to the length defined by adChar.

You can use TRIM() or RTRIM() function to remove the white-spaces, but the point is to be consistent!

Either you define your field as CHAR and therefore use adChar or change the column schema to VARCHAR and use adVarchar; Don't mix and match, or you might get into a whole lot of data integrity issues.

scott clemons Mr or Mrs. 500 Points: 550 More actions · Answer 2

Thanks for the clarification.

PNParent is varchar in all the SQL tables where it is used. I got sloppy with the ADO datatype and used adChar instead of adVarChar on a few pages.

Is it OK to use:

update tableName

set PNParent = RTrim(PNParent)

to clean up the white space issue in any affected tables containing the PNParent field? The asp pages are now consistent and I am looking to clean up the values in the tables to make them consistent.

Thanks again

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

Yes, but just make sure that all related tables referencing this field is also updated using the same update sql statement.

In addition, you should use alter table statements to change the fields in the parent table and all related tables to a varchar field. You may have to drop the constraint before you can make these changes.