SQL nVarchar(MAX) with ASP no text displayed? [Answered]RSS

4 replies

Last post Jul 13, 2012 02:20 PM by escarcha

  • SQL nVarchar(MAX) with ASP no text displayed?

    Dec 06, 2006 11:06 AM|gcannell|LINK

    To anyone who can help.

    I have created an SQL 2005 Database with an nVarChar(Max) field however when creating a connection and related recordset through ADODB to access to this field and output its text I get nothing.

    Response.Write rsOut("TheField")

    or

    StrTmp = rsOut("TheField") with Response.Write StrTmp also gives me a nothing

    Unusualy I find that Len(rsOut("TheField") or Len(rsOut("StrTmp ") gives me the correct number of characters.

    I have run the same asp code with an SQL 2000 server and found that there are no problems. I am aware that nVarChar(MAX) is new to SQL server 2005 and thus I believe that it may be what is causing the problem but am lost to find a solution as I do not wish to change this field type in the database.

    Any help on this problem would be greatly appreciated.

  • Re: SQL nVarchar(MAX) with ASP no text displayed?

    Dec 07, 2006 06:44 AM|gcannell|LINK

    To all who may read this post.

    Through some further research I have found that ASP does not support the new nVarchar(MAX) or Varchar(MAX) SQLServer 2005 database field types and that there are no SQL Server 2005 data types that can be read by an ASP page that hold more than 8000 characters.

    You can't fix the problem by casting to string with CStr. You can't
    fix it by taking the first n characters of the field with a Mid
    function. The data simply is not read into your
    rs.Fields.Item("longdata").

    I think it's a backward-compatibility dead end.

  • Re: SQL nVarchar(MAX) with ASP no text displayed?

    Jul 25, 2007 11:06 AM|i_robo|LINK

    Hi,

    I've had a similar problem this afternoon.

    My ASP page displayed all the fields from a recordset, until I changed the nvarchar(2000) field to nvarchar(max).  It would display the field that was set to nvarchar(max) but not any subsequent fields, which in my case it was two datetime fields.

    I found that you have to change your SQL server connection string to the SQL Native Client provider, i.e. DRIVER={SQL Server}; becomes Provider=SQLNCLI;.

    This might work in your case as well. 

    See http://msdn.microsoft.com/en-us/library/ms130978.aspx and http://msdn.microsoft.com/en-us/library/ms130822.aspx.


     

  • Re: SQL nVarchar(MAX) with ASP no text displayed?

    Oct 10, 2008 11:37 PM|aarongregory|LINK

    Very nice, i_robo... worked for me with a similiar issue. Worked like a champ, thank you!

    Aaron

    ASP no text displayed varchar(max) SQL nVarchar(MAX)

  • Re: SQL nVarchar(MAX) with ASP no text displayed?

    Jul 13, 2012 02:20 PM|Escarcha|LINK

    I made a cast(fieldname as nvarchar(1000)) as fieldname and that work for me.