not displaying data out of recordset

[msimmons]

not sure if this is an asp problem, database problem, or a looking at the screen to long on a friday problem but here goes.
My company has two sites that are exactly the same except for one gets its data from an access database and the other gets its info from a sql server 7 database. the access one works fine. the one hooked to the sql server is only showing part of its data.

first i select one record from the database and then run through a list of this:

	<% tempvar = rs_main("overview")
		if len(tempvar)>1 then %>
		<%=tempvar%><BR><BR>
	<% end if %>				
	
	<% tempvar = rs_main("service")
		if len(tempvar)>1 then %>
		<B>Service</B><BR>
		<%=tempvar%><BR><BR>
	<% end if %>

but for some reason overview will show and service will not. (on the site getting from access it has the same data and works fine so i dont think it is the data). In the access db the fields are type memo and in the server they are type ntext.
Has this happened to anyone before, anyone have any ideas?
Thanks in advance,
Michael

[kovan]

here is the solution (your luck you get it solved soon, took me 2 weeks to figure it out)

sql server doesnt properly return the text when your using "TEXT" and "NTEXT" fields

so what you have to do is
make that field the LAST in yoru sql statement

so if you did "SELECT * FROM MYTABLE"
that wont work properly
you have to say

"SELECT overview, service FROM...."
notice that the NTEXT FIELD MUST always be the last selected..

this will fix your problemo

[JoshT]

Maybe you could use CAST or CONVERT to change the data to varchar in the SQL query:

SELECT CAST(NumericData AS varchar) FROM Table1

[msimmons]

Originally posted by JoshT
Maybe you could use CAST or CONVERT to change the data to varchar in the SQL query:

SELECT CAST(NumericData AS varchar) FROM Table1

But varchar can only hold 8,000 charicters correct? It is possible that some of the fields will have more than that.
Thanks,
Michael

[JoshT]

Then I think you need to read the data from those fields as a BLOB or a stream, in chunks at a time...

[msimmons]

Originally posted by JoshT
Then I think you need to read the data from those fields as a BLOB or a stream, in chunks at a time...

Could you elaborate a little more on that
Michael

[JoshT]

How's this:

http://msdn.microsoft.com/library/de...thgetchunk.asp

[msimmons]

I tried:

tempvar = rs_main("service").GetChunk(5)

and received:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

thanks,
MIchael