Re: ActualSize returns -1 for Text fields


"Rod da Silva" <RodDaSilva@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23M4VNcEwFHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
> Hi all,
>
> I have a table called 'mytable' with two fields in it Fld1, and Fld2.
> Fld1
> is a text field and Fld2 is char(10) . If I create an ADORecordSet object
> using the following query:
>
> select * from mytable
>
> I can't read the contents of the text field (Fld1). ActualSize of this
> field is always -1 even though I can see the data in the table from SQL
> Enterprise Manager. Same is true if I use the query explicitly listing
> the
> fields:
>
> select Fld1, Fld2 from mytable
>
> ** However *** if I query for:
>
> select Fld2, Fld1 from mytable
>
> it works (notice the field order is different)!! I can access the
> contents
> of the Fld1 text field using this query.
>
> So I do some digging on the internet and find out that this is a farily
> well
> known issue. Text fields must be the last field in the field list of a
> select statement to be returned properly. This is why the above workaround
> (placing FLD1 last in the field list) works.
>
> But while this workaround works in this trivial case, my real problem is
> that I have tables with multiple text fields so there appears no single
> query that will return all my text data.
>
> My question is ... is there a hotfix for this?? I am running MDAC 2.8 RTM
> under Windows 2000 against SQL Server Developer Edition ver (8.00 763
> (SP3)).
>
> I really need a fix for this since using varchar instead of text fields
> isn't an option for me (I need CLOBs). Any help would be greatly
> appreciated.

Technically, you're supposed to use either field.GetChunk(), or streams to
handle text/ntext/image columns. It only works as you describe if the total
size of the row, including the text, is under 8060 bytes anyway.

Personally I prefer streams:

Dim s
s = CreateObject("ADODB.Stream")
s.Mode = adModeReadWrite ' 3
s.CharSet = "iso-8859-1"
s.Open

[...]

' read data from text field
s.WriteText rs.Fields("MyTextCol1").Value

' copy data to string buffer
s.Position = 0
Dim stringbuf
stringbuf = s.ReadText()


-Mark




> Rod
>
>
>


.

Relevant Pages

  • ActualSize returns -1 for Text fields
    ... I can't read the contents of the text field (Fld1). ... Same is true if I use the query explicitly listing the ... select Fld1, Fld2 from mytable ... But while this workaround works in this trivial case, ...
    (microsoft.public.data.ado)
  • Re: Order within Group - possible?
    ... determines the lowest sequence number in the group. ... the English equivalent of the query might go something like ... value of Fld1 is equal to the minimum value of Fld1 over all records in Your ... Table having the given value of Fld3. ...
    (microsoft.public.access.queries)
  • Re: Update Query
    ... "John Spencer" wrote: ... If you do not know how to use the SQL view to enter the query. ... -- Drag from fld1 to fld1 ... -- Drag from fld2 to fld2 ...
    (microsoft.public.access.queries)
  • Re: Update Query
    ... UPDATE tblB INNER JOIN TblA ... If you do not know how to use the SQL view to enter the query. ... -- Drag from fld1 to fld1 ... -- Drag from fld2 to fld2 ...
    (microsoft.public.access.queries)