SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Select * query > comes back with empty fields

    I am pretty frustrated. I had a nice working cms system until I moved to sql server recently. I am amazed at this problem I am having.

    I have a table, very simple structure. In this table, I am trying to store articles and a few numeric values. When I try and retrieve the information, some of the values are returned to my query but not all.

    Sample Query:
    SELECT * FROM Article WHERE ArticleID = 9

    Comes back with a faulty recordset. I only get parts of it back. The attached screen shot has the 4 fields marked that simply dont return in my query. ( I try to output them to the page and its blank )

    Looked at the tables permissions, checked all the boxes for all users. Didn't help.
    The data IS THERE .. why cant I have it back?
    Attached Images Attached Images

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh dear. try this

    http://support.microsoft.com/default.aspx/kb/175239

    Quote Originally Posted by msdn
    When dealing with BLOB fields from Microsoft SQL Server, you must put them to the right of non-BLOB columns in the resultset. To be safe, you should also read the columns in left-to-right order, so if you have two BLOB columns as the last two columns in your resultset, read the first one and then the second. Do not read them in the reverse order.

  3. #3
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edit: I think I understand now. By the way, is ntext considered a "blob" field. I was using select * so I guess that was ordering them in ABC order not how they are in the DB design. I guess I should modify my queries to select each field I need now that I am using SQL server.

    ------------------------------------------------------

    I ended up fixing this by changing the data types to Text and it starts working.

    What a wierd problem right? I see the data, just cant have it back using a select * query (returned 1/2 the records) .. ntext data type wouldn't show.

    I wanted to use the ntext because of your post "in europe we almost have to (use unicode)" .. well my system will likely be translated and .. well i guess I am just not going to be able to worry about that right now.

    Man .. I was breaking my head on the wall this morning. Once again, I can always thank ms for supplying developers with such .. cough cough .. superior products.

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah. Don't blame me! My advice is free

    Your problem is almost certainly one associated with your MDAC components. This is usually the first BUG people come across, when they try to access Unicode fields from a SQL Server by going through the TCP/IP network library and socketing to port 1434.

    There are two things to do:
    1) Ensure that you are using the correct client protocol, this must be set up on the client machine
    2) Make sure that you are using the latest and greatest (stable!) MDAC components, you can get those here:
    http://support.microsoft.com/kb/290211

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    TEXT and NTEXT are "BLOB / CLOB" fields, along with IMAGE.

    BLOB = Binary Large OBject
    CLOB = Character Large OBject

  6. #6
    SitePoint Wizard mcsolas's Avatar
    Join Date
    Jul 2004
    Location
    Hermosa Costa Rica
    Posts
    1,710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    Ah. Don't blame me! My advice is free
    Oh I wasn't placing blame. I was only saying that I wanted to do the unicode fields because your post made me relalize that I would either have to do it now or convert the fields later. Since thats a future spec on the project, I figured I might as well skip the second step and just use that to start with.

    Then in doing so, I ran into a microsoft problem. Hehe .. no worries. At least I understand why now, also thanks to you.


Bookmarks

Posting Permissions

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