How do I display html data stored in an ntext or nvarchar(MAX) data type?

Posted on 2008-11-06
Medium Priority
Last Modified: 2010-04-21
I am needing to store large amounts of HTML data in my SQL 2005 database. I have tried using ntext and nvarchar(MAX). I can get the data in the database but I have yet to be able to display it on an ASP web page.

Usually I would create a dataset and display data using this method:
This method works fine with a nvarchar(255) data type but this type is too small for what I am needing now. Can anyone please help???
Question by:RCairns54
LVL 21

Expert Comment

ID: 22900265
probably have to call it and then use javascript:  Document.Write(

Author Comment

ID: 22900455
Thank you, Im not sure how to do that. Here is a condensed version of the page and how I display data from a dataset:

<!--#include virtual="/members_secure.asp" -->
<!--#include virtual="/dbConn.asp" -->
Dim rsMemInfo__ColParam
rsMemInfo__ColParam = "1"
If (Session("Username") <> "") Then
  rsMemInfo__ColParam = Session("Username")
End If
Dim rsMemInfo
Dim rsMemInfo_cmd
Dim rsMemInfo_numRows

Set rsMemInfo_cmd = Server.CreateObject ("ADODB.Command")
rsMemInfo_cmd.ActiveConnection = dbConn_STRING
rsMemInfo_cmd.CommandText = "SELECT * FROM dbo.webpage WHERE User1 = ?"
rsMemInfo_cmd.Prepared = true
rsMemInfo_cmd.Parameters.Append rsMemInfo_cmd.CreateParameter("param1", 200, 1, 255, rsMemInfo__ColParam) ' adVarChar

Set rsMemInfo = rsMemInfo_cmd.Execute
rsMemInfo_numRows = 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Members Page</title>
<link href="/style.css" rel="stylesheet" type="text/css" />
Set rsMemInfo = Nothing

The line between the body tags: <%=(rsMemInfo.Fields.Item("PageContent").Value)%> would normaly display as html from smaller data types but for some reason when using ntext or nvarchar(MAX) it won't display.
LVL 39

Expert Comment

ID: 22900509
Change this:



if not rsmeminfo.eof and not rsmeminfo.bof then
response.write rsMemInfo("PageContent")
end if
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Accepted Solution

rhodesb earned 500 total points
ID: 22902220
There is a weird issue with the ADODB Recordset object in ASP where you can only access some large text field types (like memo in access and ntext in SQL server) one time and sometimes not to display.  The solution is to store the field in a variable first and then just display the variable.  Pretty straight forward and seems ridiculous but will likely fix the problem.
Dim PageContent
PageContent = rsMemInfo("PageContent")
<%=PageContent %>

Open in new window


Author Closing Comment

ID: 31514160
Thanks a million! I have been trying to figure this out for quite some time before posting on this website.

Author Comment

ID: 22906032
Thanks! That is exactly what I needed!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question