ASP MSSQL ntext Problems

[PCSAARON]

I have a table with 3 ntext fields in an MSSQL database. When connecting and displaying the conents of these fields, the ntext fields are showing EMPTY in the ASP page.

This happens with ntext fields. ASP and MSSQL is horrible. It's times like this where MySQL/PHP shows it true colors...

The issue is with these 3 ntext fields. ASP WOULD NOT show all three. I changed it from "*" to the field names, put them all last, and even put them into variables right away. Until I changed one of them to a varchar 255 (which will only allow 255 chars), the other two then displayed.

Can anyone shed any light on this one?

Qry = "SELECT ID,Name,Sort,Prefinished,Logo,ID,Keywords,Description,mDescription FROM Manufacturers Where ID="&ID
rsManu.Open Qry, Application("Cart_ConnectionString")
var_sDesc=rsManu("Description")
var_mDesc=rsManu("mDescription")
var_sKeywords=rsManu("Keywords")
var_mName=rsManu("Name")
......
(KeywordsmDescription,mDescription were all ntext)
I remember reading a MS KB about this, but can't remember. Is there another field that it can be converted to? Thanks in advance.

Aaron

 

[damber]

Aaron,

1. Remember you're in an ASP forum!

ASP and MSSQL is horrible. It's times like this where MySQL/PHP shows it true colors...

Comments like these are likely to put off potential posters - technology can be a pain, and the grass may look greener on the side that you know best - but you should try to keep an open mind.. as that's what will help you find a solution in the end. Just some friendly advice. ;-)

2. I've found that ODBC connections can be a common cause of these problems, so use a DSNLess connection if you aren't already.

3. Putting the fields last in an explicitly stated SELECT statement and retrieving them first IN THE ORDER THEY ARE IN THE SELECT LIST will usually help resolve the issue, if you must use ODBC..

4. Varchar in MS SQL Server can hold up to 8,000 chars (ntext is relatively 'unlimited')- your assumption of 255 is based on MySQLs limited standard - or maybe MS Access. If you spend time understanding both you will soon see that SQL Server outclasses MySQL in many areas, which is why it is more common in enterprise environments. Oracle and SQL Server are pretty much top of their class - with Oracle just pipping SQL Server in most scenarios. Points like: MySQL doesn't support Stored Procedures until v5.0 is fully released and still supports ISAM (text file) based data storage should alert you to the fact that it has a way to go before becoming a serious challenger - though it is making some good progress and is a good open source solution for many requirements.

5. You should use GetRows to make the content retrieval more efficient in most cases anyway - this will return a 2 dimensional array of the recordset, which you can use as a normal array - this can resolve the issue in some cases as well as being more efficient.

Personally I use DSNless connections and GetRows - and do not have a problem. (Though I have previously used ODBC and encountered this very same problem)

The main thing is get rid of ODBC if you are using it, and secondly, try to follow point 3 exactly.

Hope that helps

A smile is worth a thousand kind words. So smile, it's easy!

 

[PCSAARON]

Damber:

Thanks for the advice, I will try what you suggested.

Aaron

P.S.> My intention isn't to ward of potential posters. I don't want to argue with anyone over MySQL/MSSQL and I definitely don't want to discourage use of any product, there are good points to both, and I use both. But with the experience thus far with overall goals of projects, MySQL/ASP compared to MySQL/PHP, by far MySQL/PHP has ruled over the competition. However I do agree with your comments, and I appreciate your help!

 

[Tarwn]

Generally the language you feel most comfortable with (or the one you were forced to painfully use the longest*) is the one that beats the competition. And there is nothing saying you can't use MySQL with ASP (or even ASP.Net) or use Javascript instead of VBScript (or Python, or Perl...been therem done that).
It also depends what measurement your using to determine what "ruling" means. And it also matters where you previous programming experience lies.

Personally, despite previous experience with C, C++, Java, etc I still prefer ASP over ASP.Net and PHP. And I've used them all in the past week, so it isn't even a time thing. Part of that may be more experience with ASP (as opposed to say, CF, which I have very little experience with).

* While writing this I had a strange thought: there should be something similar to Stockholm syndrome for programming languages, ie languages you hate to use for years on end until eventually that hate and painful experience begins to turn into a fascination...

 

[damber]

Couldn't agree more. For the individual, you should choose the language/tool/technology that you can get the job done best with. It doesn't matter if language A is 10x more efficient at processing than language B, if you can't even get the solution working with language A.

Everyone has their favourites - I tend to favour different languages for different things, and I would suspect quite a few people do.

However, when looking at it from a solutions or technical architecture point of view and considering the needs of the solution - these things become very important. Performance in an enterprise environment is critical to success - the more you can do with less (infrastructure) the better your ROI will be. Architectural Design at a conceptual level should be tool/technology agnostic, interchanging language A and language B as necessary. It is then the detailed architecture design that will establish the right tool for the job, and create a best of breed solution - which is not necessarily founded only on technical merit, but can be influenced by many business needs.

An enterprise systems integration architecture I've worked on in the past, and am currently working on a database redesign for, uses Oracle as the data repository (and various IBM and Java products for the core execution engines). The global solution architecture in it's baseline state has the capacity to process about 900,000,000 business messages per annum (and can scale up by cluster) - this can equate to more than 300 million rows of data in the database across various tables at any given time (many containing BLOBs). Add to that the fact that this equates roughly to db transactions, you can see that a fair amount of thought is needed to ensure the tools can meet the need. MySQL would just not be able to cut it as it currently stands, and we aren't pushing Oracle to its limits.

But I wouldn't try to use an Oracle Database for my cd collection... right tool for the job and all that.

Interestingly enough when leading the architecture design for a web user interface to the above, I focused on JSP / Apache Tomcat on a load balanced (BigIP) cluster of Sun WebServers.... I use ASP for my personal projects because it's easier (and cheaper) to get hosting.. and is somewhat easier for me to program in quickly - even though I do a lot of programming in Java.. again, a weird comfort thing - but it wouldn't mean I chose that over something else in a business environment - only if it appealled on its relative merits and 'fit' for its purpose.

So, for personal use - getting the job done to the best of your ability, regardless of the technology / language you use is paramount - don't let anyone put you off - but consider objectively the benefits and pitfalls of each method from your own perspective and needs.

In a business environment, it's not that easy. )

But with the experience thus far with overall goals of projects, MySQL/ASP compared to MySQL/PHP, by far MySQL/PHP has ruled over the competition

For many things - considering the developer had equal competence in each technology set - MySQL/PHP and MSSQL/ASP would achieve the same quality of solution in the same amount of time... the main difference would be cost - but this is not as big a difference as some portray, and can be minimal in some circumstances.

For some things, PHP/MySQL would be a better choice - cost being one, dynamic inline graphic creation another (though ASP.NET helps with that)

For other things, ASP/MSSQL would be a better choice - scalability and performance being one - even cost..... MSDE is free and can support a small number of users (but more than MS Access I believe) - ASP 3.0 comes with XP Pro - so for a theoretical zero investment you have the tools... at the same 'price' as MySQL/PHP.

As Tarwn said.. you can mix and match if that is what you prefer.

It's all about choice.....! Problem is: society gives us more than we know how to cope with....

Anyway, we're a little off topic, but thought that it was worth putting a few thoughts up in case they were of use.

A smile is worth a thousand kind words. So smile, it's easy!

 

[Sheco]

I didnt see anyone mention that ntext is a BLOB-esque data type so try using ADO the field's GetChunk and AppendChunk methods instead of its default Value property.