ForwardOnly/ReadOnly ADO Problem with SQL Joins

[Rick Roemer]

I've recently come accross a strange problem. Everytime I attemp to
open a ForwardOnly/ReadOnly recordset with any kind of a join in it, I
can only retrieve the fields which i explicitly named in the select
statement.

Example:

sSQL = "select a.*, b.field1 from...."
oRs.Open sSQL, oConn, 0, 1
-------------------------------------

The only column i will be able to retrieve is the 'field1' column.
The rest return nothing. Although, they don't return an error either,
like it normally would if you'd put in a bad column name. If I change
either the cursor type or the lock type, it returns correctly. If I
connect to a different SQL server, it returns correctly. It also
returns correctly from query analyzer. It seems like the SQL Server
is the culprit, and not ADO, because i've tried connecting to the
problem SQL server from 2 different web servers with the same results.
Any ideas what could possibly cause this? And I realize I can get
around this by explicitly declaring my columns, changing cursor/lock
types or changing servers, but that would be difficult with that
amount of pages and data we currently have. And it also wouldn't
satisfy my intellectual curiousity.

Thanks.

[Bob Barrows [MVP]]

Rick Roemer wrote:

> I've recently come accross a strange problem. Everytime I attemp to
> open a ForwardOnly/ReadOnly recordset with any kind of a join in it, I
> can only retrieve the fields which i explicitly named in the select
> statement.

The solution is simple, and is widely recommended as Good Programming
Practice: do not use Select * in production queries. Always explicitly name
all the fields you want the query to return.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.