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

Posted on 2008-11-06
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

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

    Author Comment

    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" "">
    <html xmlns="">
    <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

    Change this:



    if not rsmeminfo.eof and not rsmeminfo.bof then
    response.write rsMemInfo("PageContent")
    end if
    LVL 4

    Accepted Solution

    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

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

    Author Comment

    Thanks! That is exactly what I needed!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now