Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Missing data after TEXT field

    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?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Suggested workaround: put text field last...

    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.

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •