Defined sizes and Data types

R. Choate · Nov 7, 2003

I have been getting a "multi-step operation" error when using ADO to query AS400 and save the recordset to a .dat file. I tried an
error trap "On Error Resume Next", and was able to get my recordset, but it still will not save/persist for me. I looked at the
properties of the fields to see where I might have problems, and found a couple of things I don't know how to fix. First, one of the
fields has a defined size of -1. This doesn't seem right, but I don't know what to do about it. The field is a string and I did get
a value for at least the 1st record. Can I alter the defined size of a field somehow for my query?

Next, all of the fields are variants. I think that all of my data is text, except for a customer number field. How can I code this
to convert whatever needs to be converted so I can save my recordset? Hopefully this isn't too complicated, but here is my SQL. I
can't control the wrapping. Also, naturally, I cannot change anything on the AS400 or the properties of the AS400 data tables.

SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG

FROM WEORU.CUST CUST LEFT OUTER JOIN WEORU.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#}

WHERE (CPRD.CPCHG In ('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO','BE','BS')) AND (SUBSTR(CSPCL4,1,2) In
('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO','BE','BS')) AND (CUST.CCYCLE In
('RW','RR','RC','TR','WR','SB','SC','ZB','1','2','10','11','12','13','14','15','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))

Thanks in advance

Val Mazur · Nov 9, 2003

Hi,

Do you get variant datatypes for all the fields returned from a query? I
think this is a root of the problem. How you fields declared in a database?

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

R. Choate said:
I have been getting a "multi-step operation" error when using ADO to query

AS400 and save the recordset to a .dat file. I tried an

error trap "On Error Resume Next", and was able to get my recordset, but

it still will not save/persist for me. I looked at the

properties of the fields to see where I might have problems, and found a

couple of things I don't know how to fix. First, one of the

fields has a defined size of -1. This doesn't seem right, but I don't know

what to do about it. The field is a string and I did get

a value for at least the 1st record. Can I alter the defined size of a field somehow for my query?

Next, all of the fields are variants. I think that all of my data is text,

except for a customer number field. How can I code this

to convert whatever needs to be converted so I can save my recordset?

Hopefully this isn't too complicated, but here is my SQL. I

can't control the wrapping. Also, naturally, I cannot change anything on

the AS400 or the properties of the AS400 data tables.

SELECT CUST.CCMPNY, CUST.CSPCL4, CUST.CCYCLE, CUST.CCUST#, CPRD.CPDESC, CPRD.CPCHG

FROM WEORU.CUST CUST LEFT OUTER JOIN WEORU.CPRD CPRD ON CUST.CCUST# = CPRD.CPCST#}

WHERE (CPRD.CPCHG In

('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO','BE','BS')) AND
(SUBSTR(CSPCL4,1,2) In

('CW','DF','MC','MK','CK','WE','LI','GA','GR','CO','BE','BS')) AND (CUST.CCYCLE In

('RW','RR','RC','TR','WR','SB','SC','ZB','1','2','10','11','12','13','14','1
5','16')) AND (CUST.CCMPNY In ('1','3','4','5','6'))

R. Choate · Nov 9, 2003

Yes, I believe I did get variant datatypes. How can I do what I need to do to make this work?
--
RMC,CPA

Hi,

Do you get variant datatypes for all the fields returned from a query? I
think this is a root of the problem. How you fields declared in a database?

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp