NickJPhillips
asked on
SQLServer NText datatype not displaying in ASP Pages
I just upsized the backend to an ASP site (was MS Access) to SQL Server Express 2005 for lcoal testing purposes so I could work out the bugs before moving my production ASP site to the companies SQL Server... for the most part everything is working perfect. I had to change a few "Date()" values to "GetDate()" but otherwise I was pretty close to the mark when writing the code in the first place...However I have hit a weird gllitch I was hoping someone would know how to fix and what I did wrong.
In my table I have four fields:
News_ID (SQLSvr: int // Access: number)
NewsTitle (SQLSvr: nvarchar(50) // Access: text)
NewsBody (SQLSvr: ntext // Access: memo)
NewsDate (SQLSvr: datetime // Access: date/time)
IF I put the NewsBody on an ASP page by itself it is displayed when I run it. BUT if I put the NewsDate field BEFORE the NewsBody, then the NewsBody is NOT displayed, however if I plaec the NewsDate field AFTER the NewsBody field then everything IS displayed. To make things even weirder, if I try to do something similar with NewsTitle (placing it after NewsDate) nothing is affected... it is displayed just fine!!
Similarly, I have some other tables that use are set to NText and I am having problems displaying them in different pages...especially ones that pull these field through Javascript functions (like pop-up hover displays and such)
Not too bad for doing an auto-upsize with the Access wizard, but still irksome to deal with... any thoughts on how to correct?
In my table I have four fields:
News_ID (SQLSvr: int // Access: number)
NewsTitle (SQLSvr: nvarchar(50) // Access: text)
NewsBody (SQLSvr: ntext // Access: memo)
NewsDate (SQLSvr: datetime // Access: date/time)
IF I put the NewsBody on an ASP page by itself it is displayed when I run it. BUT if I put the NewsDate field BEFORE the NewsBody, then the NewsBody is NOT displayed, however if I plaec the NewsDate field AFTER the NewsBody field then everything IS displayed. To make things even weirder, if I try to do something similar with NewsTitle (placing it after NewsDate) nothing is affected... it is displayed just fine!!
Similarly, I have some other tables that use are set to NText and I am having problems displaying them in different pages...especially ones that pull these field through Javascript functions (like pop-up hover displays and such)
Not too bad for doing an auto-upsize with the Access wizard, but still irksome to deal with... any thoughts on how to correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is an ADO "feature" you need to place your BLOBs at the end of the Select statement and in the order that they are listed in the table.
But the real question is why you are using ntext. Yes, I realize that you used the upsize "wizard" and that is what it produced. However, you would be wise to reconsider for two reasons:
1. Unless you are using a double-byte language there is no reason to use n[anytype].
2. Second unless your MS Access memo fields were used for values longer than 8000 characters you would be better off using (n)varchar.
But the real question is why you are using ntext. Yes, I realize that you used the upsize "wizard" and that is what it produced. However, you would be wise to reconsider for two reasons:
1. Unless you are using a double-byte language there is no reason to use n[anytype].
2. Second unless your MS Access memo fields were used for values longer than 8000 characters you would be better off using (n)varchar.
ASKER
OK, using variables IN THE CORRECT ORDER works, but I don't understand WHY...
>>but I don't understand WHY...<<
Look on MSDN there is an article on the subject.
But you should really lose the ntext column...
Look on MSDN there is an article on the subject.
But you should really lose the ntext column...
ASKER
I did...and I posted as such in my second post in the thread...
==========================
Just to add something to the mix, I just read on MSDN that the NText datatype is being fazed out...instead I should be using "nvarchar(max)" as the datatype... I made the swith in my database and I am still experiancing the same problem as described above.
==========================
Thanks for the direct to MSDN...I will have to search around
==========================
Just to add something to the mix, I just read on MSDN that the NText datatype is being fazed out...instead I should be using "nvarchar(max)" as the datatype... I made the swith in my database and I am still experiancing the same problem as described above.
==========================
Thanks for the direct to MSDN...I will have to search around
>>I did...and I posted as such in my second post in the thread... <<
I know, I read it and I also read that you were "still experiancing the same problem", I was not suggesting you use varchar(MAX), but rather varchar. It would help if you read up on the differences.
But my real point is that you have evidentally unwisely IMHO used the upsize wizard to migrate to MS SQL Server. Unfortunately the choices made in that tool are what might be called the lowest common denominator. There is no way that you can fully take advantage of MS SQL Server using it like some over-grown brother of MS Access. You will not only be frustrated, but disappointed at the results. They are totally different animals.
Good luck in your project.
I know, I read it and I also read that you were "still experiancing the same problem", I was not suggesting you use varchar(MAX), but rather varchar. It would help if you read up on the differences.
But my real point is that you have evidentally unwisely IMHO used the upsize wizard to migrate to MS SQL Server. Unfortunately the choices made in that tool are what might be called the lowest common denominator. There is no way that you can fully take advantage of MS SQL Server using it like some over-grown brother of MS Access. You will not only be frustrated, but disappointed at the results. They are totally different animals.
Good luck in your project.
ASKER
Thanks, I appreciate the pointers. Lots of reading to do!
ASKER