Re: ActualSize returns -1 for Text fields

Rod wrote on Tue, 27 Sep 2005 00:46:54 -0400:

> Has anyone out there have a table with more then one Text field? Seems to
> me this problem would affect anyone with such a table that needed to run a
> query that returned two or more of these fields.

I have many tables with multiple text fields, and don't seem to have any
problems no matter what order I return them in, so long as I read from left
to right.

>> IIRC, using a server side cursor the
>> first time you read the field ADO will then discard the underlying
>> pointer to that text field, and all fields to the left of it in the
>> recordset.
> Then
>> if you attempt to read the field or it's properties a second time it will
>> appear as empty with a size of -1. I always read ADO recordsets from left
> to
>> write, and if I need to check the value of a field more than once then I
>> read it into a variable, and use the variable for subsequent tests.
>
> What exactly do you mean by "I always read ADO recordsets from left to
> right"?

Say you have:

col1 - integer
col2 - text

select col1, col2 from table

Reading from "left to right" is ensuring that you read the value of col1
before col2. If col2 is a text field, and you read it first, ADO in certain
circumstances will discard the value in col1. eg.

adoRec.Open "select col1, col2 from table"
vMyVal2 = adoRec("col2")
vMyVal1 = adoRec("col1")

vMyVal1 will contain a null, rather than the actual value that was in col1.

This can also cause problems:

adoRec.Open "select col1, col2 from table"
vMyVal1 = adoRec("col1")
If Not IsNull(adoRec("col2")) Then
vMyVal2 = adoRec("col2")
End If

The IsNull returns false as col2 is not null, but ADO discards the
underlying data, IIRC it's an efficiency tweak so that memory footprints of
recordsets are kept as small as possible. The subsequent assignment of the
value of col2 to vMyVal2 results in a null being stored in the variable
because the IsNull call has already read the data.

Dan


.

Relevant Pages

  • Re: Inserting multiple records into two tables...with a twist
    ... I assume that you have an IDENTITY column, ... reason would typically be a high rate of concurrent insertions.) ... INSERT tblB(id, col1, col2, ... ...
    (comp.databases.ms-sqlserver)
  • Dynamic PreparedStatements with Variable In Parameters
    ... AND col2=? ... AND col3=? ... I have to start coding tomorrow so I'm eager ...
    (comp.lang.java.programmer)
  • Re: Required property of Primary Key fields
    ... col1 INTEGER NOT NULL, ... PRIMARY KEY (col1, col2) ... it's a bit pointless because the INSERT will fail with a "primary key ...
    (microsoft.public.access.tablesdbdesign)
  • RE: tough query (for me)
    ... query, I had to write VB code which used one query which obtained all of the ... would obtain data from both Col1 and Col2 into a recordset. ... Col1 and Col2 data to an array. ...
    (microsoft.public.access.queries)
  • Re: Resettin Running Total
    ... GROUP BY col1, col2 ... >> report writers and format data in the front end. ... > Now the same correlated subquery idea can, of course, when used ...
    (microsoft.public.sqlserver.programming)