Missing data after TEXT field

Spudhead · #1 (**permalink**) 08-22-06, 11:10

SQL Server 2005 table:

id int
title nvarchar 150
bodycopy text 16
image nvarchar 150
summary text 16
author nvarchar 150

Query:

SELECT [id], [title], [image], [author], [bodycopy], [summary] FROM [tblStories] WHERE [id]=2

Problem:

Any field following a text field in the query is.. empty. There's data in the table, I can run the query in Query Analyser direct and get everything. I've tried swapping the order of fields round in the query, and the problem persists.

I suspect that this isn't a SQL Server issue, but an ADODB one. And I'm sure I've seen it before. But Google doesn't appear to have done, and I'm at a loss as to how to fix it (other than running seperate queries - not ideal).

Anyone shed any light?

Teddy · #2 (**permalink**) 08-24-06, 13:14

Can you post a sample of a record you get in your application vs. the record received in QA as well as the method you are using to pull in the recordset?

buzzter66 · #3 (**permalink**) 08-29-06, 02:57

I encountered this problem earlier this year and found a workaround (never did figure out what the root cause was, but I got it working). The trick is to always request ALL the fields by name (do NOT use *) and ALWAYS put the text field LAST in the SELECT statement.

Try this:

SELECT [id], [title], [image], [author], [summary], [bodycopy] FROM [tblStories] WHERE [id]=2

If you have a table with two text fields, you're gonna be out of luck, I'm afraid.

Spudhead · #4 (**permalink**) 08-29-06, 06:42

Quote:

Originally Posted by Teddy

Can you post a sample of a record you get in your application vs. the record received in QA as well as the method you are using to pull in the recordset?

Code:

class databaseInterface

public oCn	'	global connection object

private sub Class_Initialize()
	set oCn = Server.CreateObject("ADODB.Connection")
  	oCn.Open "DRIVER={SQL Server};SERVER=******;DATABASE=******;UID=******;PWD=******"
end sub

private sub Class_Terminate()
	if isObject(oCn) then
  		oCn.close
		set oCn = nothing
	end if
end sub

public function QuerySQL(ByVal sSQL)
	dim oRs
	set oRs = oCn.execute(sSQL)
	if not oRs.EOF then
		QuerySQL = oRs.getRows()
	else
		QuerySQL = sSQL
	end if
end function

end class


'=======================

set oDB = new databaseInterface
aStoryInfo = oDB.QuerySQL("SELECT [id], [title], [image], [author], [summary], [bodycopy] FROM [tblStories] WHERE [id]=2")
set oDB = nothing

Results-wise... what can I say? In QA, I see the text data. In my application, the value comes back as an empty string.

Internet Services

Web Development

Digital Marketing

Consumer Tech