ADO Question regarding loading data in character field

[u0107]

Hello,

Using ADO I have created a table and have also created fields.

To create fields, I have used the following:

.Columns.Append "Field_Name", adWChar, 6

I load records into this created table including into this field.

I notice that if the value that I load into this field is less than the full defined length of 6 chars, the balance is padded with spaces.

For example in this 6 length Char field, if I want to load the value "APP1" then what really gets loaded is "APP1 " which has 2 spaces padded at the end.

The value "APP1" is contained in another field and definitely has NO padded spaces at the right. Even using something like:

!Field_Name = Trim(Another_field)

does not help.

Any suggestions?

Thanks in advance.

Uttam

 

[dk]

Hi,

Try using adVarWChar (Variable Wide Character) instead of adWChar. There isn't much information in the help file but using adWChar seems to create a fixed length field.

 

[u0107]

Hello All,

I had posted this query on Google Groups too and got no response there. In the meantime, I did my own investigations and found a solutin for the problem which I published last night on Google. Although not very elegant, but it works. I am still hoping that the experts here have something more elegant.

The statement "tbl.Columns.Item(0)... etc" should be included immediately after the "Columns.Append" statement series.

This is what I posted on Google:

======================

Sorry everyone - I guess I need to gain more confidence i my own
abilities!

I have found the solution myself and this is the way -

tbl.Columns.Item(0).Properties.Item(3).Value = True

The Item(0) refers to the field number in my table definition. For
example in my table field No 1 is the one where I want to have not
fixed length but length depending on the value of the field. Hence
Item(0).

Properties.item(3) refers to the "adColNullable" Property of the
column - that is Fixed length or actual length of the data. I have
simply set it to true.

Thanks to all those who tried to help.

Cheers!

Uttam
===========================

Please do let this board know if this helps and if someone has something better.

Cheers!

Uttam

 

[dk]

Uttam,

Did you see my solution? Was there something wrong with it?

 

[u0107]

Hello Dan,

I don't think your solution has anything wrong in it. It is just that I found the solution prior to receiving the automatic notification from the MrExcel board in my email.

I looked up the Help text for ADO and found that

adVarChar is listed as "A null-terminated Unicode character string (Parameter object only)." I guess that means that just defining a field / variable as adVarWChar should do the trick as elegantly.

Thank you very much.

Regards,

Uttam