Defined (field) size = -1

[R. Choate]

I've really been going insane on this one:

I'm using ADO to open a recordset and save (persist) to a file. The recordset consists of 6 fields. There are 5 text fields and one
numeric field. I get the "multi-step operation generated errors Check each status value" error when it tries to save the recordset.
I have narrowed the problem down to the numeric field, that is, I know that if I leave out the numeric field, everything goes
perfectly. When I interrupt the code and look at the properties, I see that the "defined size" = -1. I know that the "precision" is
5. It says the "Actual Size" = 19 and the "Attributes" = 20. The original value is "Variant/Decimal", and is a 4-digit number (at
least for the current record, which is the 1st record). The "Type" is "adNumeric", and the numeric scale = 0. Here are the key lines
of code (with actual user & pw changed of course):

mySQL = "SELECT CUST.CCMPNY as CPY, CUST.CSPCL4 as Spcl, CUST.CCYCLE as Cycle, CUST.CCUST# as Cust, CPRD.CPDESC as Desc,
CPRD.CPCHG as Chg " & _
"FROM SFDJLK.CUST CUST LEFT OUTER JOIN SFDJLK.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'))"

cnt.CursorLocation = adUseClient

cnt.Mode = adModeReadWrite

cnt.Open "provider=IBMDA400;data source=" + MYSYSTEM + ";", "USER", "PASSWORD"

rst.Open mySQL, cnt, adOpenStatic, adLockBatchOptimistic

rst.Save "H:\Subdirectory\MyRecords.dat", adPersistADTG

Please help. Again, everything works like a charm if I don't include "CUST.CCUST# as Cust" in my SELECT, so I have a working
connection and SQL. What can I do to get rid of the "-1" defined size and save this recordset? I am going to go crazy very soon.

Thanks !

 

[Val Mazur]

Hi,

Did you check my reply on your previous posting?

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

I've really been going insane on this one:

I'm using ADO to open a recordset and save (persist) to a file. The

recordset consists of 6 fields. There are 5 text fields and one

numeric field. I get the "multi-step operation generated errors Check

each status value" error when it tries to save the recordset.

I have narrowed the problem down to the numeric field, that is, I know

that if I leave out the numeric field, everything goes

perfectly. When I interrupt the code and look at the properties, I see

that the "defined size" = -1. I know that the "precision" is

5. It says the "Actual Size" = 19 and the "Attributes" = 20. The original

value is "Variant/Decimal", and is a 4-digit number (at

least for the current record, which is the 1st record). The "Type" is

"adNumeric", and the numeric scale = 0. Here are the key lines

of code (with actual user & pw changed of course):

mySQL = "SELECT CUST.CCMPNY as CPY, CUST.CSPCL4 as Spcl, CUST.CCYCLE

as Cycle, CUST.CCUST# as Cust, CPRD.CPDESC as Desc,

CPRD.CPCHG as Chg " & _
"FROM SFDJLK.CUST CUST LEFT OUTER JOIN SFDJLK.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'))"

cnt.CursorLocation = adUseClient

cnt.Mode = adModeReadWrite

cnt.Open "provider=IBMDA400;data source=" + MYSYSTEM + ";", "USER", "PASSWORD"

rst.Open mySQL, cnt, adOpenStatic, adLockBatchOptimistic

rst.Save "H:\Subdirectory\MyRecords.dat", adPersistADTG

Please help. Again, everything works like a charm if I don't include

"CUST.CCUST# as Cust" in my SELECT, so I have a working

connection and SQL. What can I do to get rid of the "-1" defined size and

save this recordset? I am going to go crazy very soon.

 

[Unicorn]

-1 simply means DEFAULT

Matt

I've really been going insane on this one:

I'm using ADO to open a recordset and save (persist) to a file. The

recordset consists of 6 fields. There are 5 text fields and one

numeric field. I get the "multi-step operation generated errors Check

each status value" error when it tries to save the recordset.

I have narrowed the problem down to the numeric field, that is, I know

that if I leave out the numeric field, everything goes

perfectly. When I interrupt the code and look at the properties, I see

that the "defined size" = -1. I know that the "precision" is

5. It says the "Actual Size" = 19 and the "Attributes" = 20. The original

value is "Variant/Decimal", and is a 4-digit number (at

least for the current record, which is the 1st record). The "Type" is

"adNumeric", and the numeric scale = 0. Here are the key lines

of code (with actual user & pw changed of course):

mySQL = "SELECT CUST.CCMPNY as CPY, CUST.CSPCL4 as Spcl, CUST.CCYCLE

as Cycle, CUST.CCUST# as Cust, CPRD.CPDESC as Desc,

CPRD.CPCHG as Chg " & _
"FROM SFDJLK.CUST CUST LEFT OUTER JOIN SFDJLK.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'))"

cnt.CursorLocation = adUseClient

cnt.Mode = adModeReadWrite

cnt.Open "provider=IBMDA400;data source=" + MYSYSTEM + ";", "USER", "PASSWORD"

rst.Open mySQL, cnt, adOpenStatic, adLockBatchOptimistic

rst.Save "H:\Subdirectory\MyRecords.dat", adPersistADTG

Please help. Again, everything works like a charm if I don't include

"CUST.CCUST# as Cust" in my SELECT, so I have a working

connection and SQL. What can I do to get rid of the "-1" defined size and

save this recordset? I am going to go crazy very soon.

 

[R. Choate]

Here is about all I know about Defined Size:

The definedSize should always return the MAXIMUM length a column can have and
never be -1, as you can see in following excerpt from the ADO documentation:
------------------------------------------------------------------------------------
DefinedSize Property
Indicates the data capacity of a Field object.

Return Value
Returns a Long value that reflects the defined size of a field as a number of bytes.

Remarks
Use the DefinedSize property to determine the data capacity of a Field object.

The DefinedSize and ActualSize properties are different. For example, consider a
Field object with a declared type of adVarChar and a DefinedSize property value
of 50, containing a single character. The ActualSize property value it returns
is the length in bytes of the single character.

So now I'm really confused.
--
RMC,CPA


-1 simply means DEFAULT

Matt

I've really been going insane on this one:

I'm using ADO to open a recordset and save (persist) to a file. The

recordset consists of 6 fields. There are 5 text fields and one

numeric field. I get the "multi-step operation generated errors Check

each status value" error when it tries to save the recordset.

I have narrowed the problem down to the numeric field, that is, I know

that if I leave out the numeric field, everything goes

perfectly. When I interrupt the code and look at the properties, I see

that the "defined size" = -1. I know that the "precision" is

5. It says the "Actual Size" = 19 and the "Attributes" = 20. The original

value is "Variant/Decimal", and is a 4-digit number (at

least for the current record, which is the 1st record). The "Type" is

"adNumeric", and the numeric scale = 0. Here are the key lines

of code (with actual user & pw changed of course):

mySQL = "SELECT CUST.CCMPNY as CPY, CUST.CSPCL4 as Spcl, CUST.CCYCLE

as Cycle, CUST.CCUST# as Cust, CPRD.CPDESC as Desc,

CPRD.CPCHG as Chg " & _
"FROM SFDJLK.CUST CUST LEFT OUTER JOIN SFDJLK.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'))"

cnt.CursorLocation = adUseClient

cnt.Mode = adModeReadWrite

cnt.Open "provider=IBMDA400;data source=" + MYSYSTEM + ";", "USER", "PASSWORD"

rst.Open mySQL, cnt, adOpenStatic, adLockBatchOptimistic

rst.Save "H:\Subdirectory\MyRecords.dat", adPersistADTG

Please help. Again, everything works like a charm if I don't include

"CUST.CCUST# as Cust" in my SELECT, so I have a working

connection and SQL. What can I do to get rid of the "-1" defined size and

save this recordset? I am going to go crazy very soon.

 

[Unicorn]

Perhaps, what I said was neither clear, nor entirely accurate.

AFAIK the value -1 is returned, when the defaults size of the field type is
not modified from the default of the RDBS . i.e. is set to maximum.

For experimental purposes, you could try XML output using adPersistXML in
your save statement. This might help identify the problem.

Matt

 

[R. Choate]

Thanks, Matt.
I have thought about going with the XML option to see if it works. I'll continue trying to go with ADTG for a couple more hours, and
then I'll experiment with the XML. I just can't believe how much trouble this has been, just for one stupid field. Anyway, thanks
for responding again. If I get anything at all to work, you can bet I will post it.
--
RMC,CPA


Perhaps, what I said was neither clear, nor entirely accurate.

AFAIK the value -1 is returned, when the defaults size of the field type is
not modified from the default of the RDBS . i.e. is set to maximum.

For experimental purposes, you could try XML output using adPersistXML in
your save statement. This might help identify the problem.

Matt