Disconnectd ADO recordset problem 2

[Varco]

For use internally to my VB apps I sometimes create disconnectd recordsets, using the column properties of the connected recordset, then copy the desired data. The problem I have is that if the data is anything other than character, I get a "Multiple-step operation generated errors" error when it is being copied to the disconnected rs. Suggestions?

Jim Varco
jimv@varcconsulting.com

 

[HRoarke]

Often this message indicates a datatype mismatch, e.g. copying a numeric value into a bit column. Also, instead of creating a new RS programatically (a fairly resource-intensive operation), you may try simply setting the Connection property of the RS to NOTHING, effectively disconnecting it.

 

[Varco]

Thanks for the suggestions. Due to some special needs for this project, I need to use a programmatically created disconnected rs. The rs is created by reading and using the attributes of the source rs, so I am puzzled as to why I get the error when copying a numeric field value from one to the other. The problem field is type adNumeric, and the data in the source rs happens to be integer values. If I set the disconnected rs to type adInteger, the data copies fine. Set it to match the source at adNumeric (with the same attributes) and it errors out.

Jim Varco
jimv@varcconsulting.com

 

[Abelardo]

When Creating the recordset "on the fly" using the Fields.append method , it will set up everything except for two properties. Precision and Numericscale. These have to be entered seperately right after appending the Field.

This can be a problem in the case of working with adNumeric fields. You can either lose information or get a "Multiple-step generated error"

 

[Abelardo]

Here is a sample code(rstSource = Source Recordset, rstTarget = Target Recordset , manually created).


For Each objField In rstSource.fields
rstTarget.fields.Append objField.Name, objField.Type, objField.DefinedSize, objField.Attributes

rstTarget.fields.Item(rstTarget.fields.Count - 1).Precision = objField.Precision
rstTarget.fields.Item(rstTarget.fields.Count - 1).NumericScale = objField.NumericScale

Next objField

Hope this helps

 

[Varco]

Abelardo,

Thanks for the help. Your suggestions fixed the problem I was having with adNumeric fields. Are there other data types that should be dealt with in a similar manner?

Jim Varco
jimv@varcconsulting.com

 

[Abelardo]

Hi Jim,
To my knowledge only those two variables need manual modificaction.

The Microsoft Knowledge Base Article - 247868 "HOWTO: Manufacture an ADO Recordset Based on Another Recordset" shows an example, similar to the example i posted, but they fail to explain why those variables are not initialized automatically, or why they are not part of the Fields.Append method. But since this is how Microsoft creates a copy recordset I expect this will cover all the cases.

Hope this helps,
Abelardo

 

[Varco]

Abelardo & HRoarke,

Thanks for the great help!

Jim Varco
jimv@varcconsulting.com