This is bizarre.

I have a SQL table with three fields.  The first two are TEXT, while the 
final field is a DATE_TIME.

Using ASP/VBscript, I've got the following:
<%

'assume that in my script, Internet_Connect is a valid, open connection 
object.

Set objGetPR = Server.CreateObject("ADODB.RecordSet")
QPR = "SELECT field1, field2, field3 from myTable order by field3 desc"
objGetPR.Open QPR, Internet_Connect, 0, 1

Then I use the GetRows method thusly:

astrGetPR = GetPr.GetRows
	
GetPr.close
Set GetPr = nothing
	
intGetPRCount = ubound(astrGetPR, 2)
	
strField1 = astrGetPR(0, 0)
strField2 = astrGetPR(1, 0)
strField3 = astrGetPR(2, 0)
%>

The strField1 value is empty, though there's data in that recordset for 
that field! The other two fields are fine.

What is going on here?  I can only assume it's the cursor type, but why 
would adOpenForwardOnly, adLockReadOnly be a problem here?  I'm not moving 
back and forth in the recordset--it's one direction.  Incidentally, if I 
change the cursortype to adOpenKeyset, adLockOptimistic then it 
works...but since that's less efficient, I don't want to go that route, 
and there should be no reason why the ReadOnly lock should fail.

I'm stumped.

I just noticed I have a couple typos in my example, but what I have on my 
end is correct.

Where I say GetPR that should be objGetPR.  I wanted to clarify that 
before anyone says that's what is causing the problem.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q200124&
Accessing Text or Memo Fields in ASP Returns No Data

Try moving the text fields to the end of the select statement. If that
doesn't help, then use .AppendChunk. The article above has a link to another
article that explains why the problem is occuring.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Mike Taylor" <michaelctaylor@j...>
Subject: [asp_databases] Cursortype problems using GetRows method.


: This is bizarre.
:
: I have a SQL table with three fields.  The first two are TEXT, while the
: final field is a DATE_TIME.
:
: Using ASP/VBscript, I've got the following:
: <%
:
: 'assume that in my script, Internet_Connect is a valid, open connection
: object.
:
: Set objGetPR = Server.CreateObject("ADODB.RecordSet")
: QPR = "SELECT field1, field2, field3 from myTable order by field3 desc"
: objGetPR.Open QPR, Internet_Connect, 0, 1
:
: Then I use the GetRows method thusly:
:
: astrGetPR = GetPr.GetRows
:
: GetPr.close
: Set GetPr = nothing
:
: intGetPRCount = ubound(astrGetPR, 2)
:
: strField1 = astrGetPR(0, 0)
: strField2 = astrGetPR(1, 0)
: strField3 = astrGetPR(2, 0)
: %>
:
: The strField1 value is empty, though there's data in that recordset for
: that field! The other two fields are fine.
:
: What is going on here?  I can only assume it's the cursor type, but why
: would adOpenForwardOnly, adLockReadOnly be a problem here?  I'm not moving
: back and forth in the recordset--it's one direction.  Incidentally, if I
: change the cursortype to adOpenKeyset, adLockOptimistic then it
: works...but since that's less efficient, I don't want to go that route,
: and there should be no reason why the ReadOnly lock should fail.
:
: I'm stumped.

Ken:

Thanks for the link.  I followed the suggestions on that page but 
apparently my issue is unique.  It seems their solution isn't applicable 
to situations where the GetRows method is used.

I wonder if there isn't some sort of byte limitation to the adForwardOnly 
cursor type?  If I change this to MUCH slower 1, 3 cursortypes, then it 
works.  Ideally, however, I'd want to use the ForwardOnly/ReadOnly 
cursortypes since that is proven to be the fastest way to go, especially 
in combination with the GetRows method.

Thanks,
Mike


> http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q200124&
Accessing Text or Memo Fields in ASP Returns No Data

Cheers
Ken

Your problem is not "unique". It's funny how people always seem to think
that. :-)

There are millions of VB developers out there, plus probably another million
or so C++ developers. All using ADO. You think that you're the only person
to have more than 1 text field in your table? :-)

If you have muliple text fields in your table, you shouldn't rely on
.GetRows(). As I pointed out, there KB article links to another article
which explains the underlying mechanics of what happens when you extract
text (or BLOB) data from a database - the receiving layer sometimes doesn't
know how much there is, how long to wait for it, or how much of a buffer to
allocate to receive the data. An array, for example, doesn't work like a
database does in storing text data - a database stores the text data
/outside/ the record, and stores a pointer to the data pages in the record.
A VBScript array has no corresponding concept.

Changing the cursortype probably helps in your situation because:
a) it means that the database is assembling metadata about the complete
recordset before it sends anything to ADO
b) you don't have that much data in your text fields.

In order to keep the adOpenForwardOnly cursor, I suggest you do something
like (I know it means losing .GetRows, but that's the price you pay for lots
of text fields):

Set objField1 = objRS.Fields.Item("myTextField")

Do While Not objRS.EOF
    buffer = objField1.Value
    objRS.movenext
Loop

That all said, Jeff Mason is on the SQL Server list also at p2p.wrox.com -
you may wish to post this message (and your original problem) to that list.
He's probably the biggest guru at p2p.wrox.com. If you want more help I know
some SQL Server MVPs who might also be able to comment further...

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Mike Taylor" <michaelctaylor@j...>
Subject: [asp_databases] Re: Cursortype problems using GetRows method.


: Ken:
:
: Thanks for the link.  I followed the suggestions on that page but
: apparently my issue is unique.  It seems their solution isn't applicable
: to situations where the GetRows method is used.
:
: I wonder if there isn't some sort of byte limitation to the adForwardOnly
: cursor type?  If I change this to MUCH slower 1, 3 cursortypes, then it
: works.  Ideally, however, I'd want to use the ForwardOnly/ReadOnly
: cursortypes since that is proven to be the fastest way to go, especially
: in combination with the GetRows method.
:
: Thanks,
: Mike
:
:
: > http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q200124&
: Accessing Text or Memo Fields in ASP Returns No Data

Thanks again, Ken!  I found your information extremely helpful.  :-)