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.
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").
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.
2 Posts
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.
2 Posts
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.
1 Post
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.
1 Post
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)
1 Post
Re: SQL nVarchar(MAX) with ASP no text displayed?
Jul 13, 2012 02:20 PM|Escarcha|LINK