Retrieve data from SQL Server 2000's TEXT column

No problem with SQL Server 7, does not work with Server 2000

[markusa]

Hi,
I'm trying to store the contents of a TEXT columns into a variable to compare it against another string. Works great with SQL Server 7, ASP 3.0, IIS4 on WinNT4. Same script does not work on SQL Server 2000, ASP.NET, IIS5, Win2k.

To put it more precise a code snippet:

Set dbObj = Server.CreateObject("ADODB.Connection")
dbObj.Open connstr
Set rsObj = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT col1, col2, col3 FROM table"
rsObj.Open sql, dbObj
If Not rsObj.Eof Then
dbo_col1 = rsObj("col1")
dbo_col2 = rsObj("col2")
dbo_col3 = rsObj("col3")
...

col1, col2 are of type VARCHAR, col3 is a TEXT column.

In ASP, dbo_col3 contains the data from the table, but on the other server the variable is empty (of course, in both cases all table columns have some data in them).

Any idea what's wrong here?

I have no experience with ASP.NET and unfortunately no direct access (VNC or something) to the remote Win2k Server, so I cannot alter the server configuration in any way.

[Dreamquick]

That isn't ASP.net as I understand it (it looks like standard ASP to me), so aside from the extra DDLs a .net install brings there shouldn't be that much difference.

Code looks good, the only thing that springs to mind is that I've always tried to use rstObject("X").value when I want the value of a field as otherwise some *very* funky, not to mention hard to explain, stuff can happen (mostly objects being returned instead of literals).

The only other thing I can think of is to check what happens when you directly query the SQL Server rather than using ASP to execute the SQL on your behalf.

- Tony

[markusa]

Thanks,
rsObj("x").value did the work.

[aspdaddy]

Have you tried setting the cursortype to 3 - adOpenStatic? This gives a you local copy of data ( I believe)

<added> Thats interesting, goes to show you cant trust the default properties</added>

[duckhunter]

The ".value" is a change that used to be optional in ASP 1.0 and seems to be required in .NET