Solved

Classic asp limit on database field size?

Posted on 2008-10-11
5
722 Views
Last Modified: 2012-05-05
I have created a classic asp page pulling data from a SQL Server 2005 database. (I'm generating these files with DBtoHTML Express and it doesn't allow .aspx file extension)
If I set the field size in the database to something like nvarchar(2000), then the data is not pulled from that field, but it is pulled from fields that have a size nvarchar(150). Does classic asp have a limit to the size of the fields that it pulls from?
0
Comment
Question by:jmestep
  • 2
5 Comments
 

Author Comment

by:jmestep
Comment Utility
Here is the connection string in case that has something to do with it:
<%
Dim objDBConn
Dim strConn
Dim rsData
Dim strSQL
Dim strState, strCity, strStateAbbr
Set objDBConn=Server.CreateObject("ADODB.Connection")
strConn= "Driver={SQL Server};Server=THEGGLLC;Database=Static;Uid=<user>;Pwd=<password>;"
'strConn= "Driver={SQL Server};Server=GEEK\GEEK3;Database=Static;Uid=<user>;Pwd=<password>;"
objDBConn.Open strConn
Set rsData=Server.CreateObject("ADODB.Recordset")
rsData.ActiveConnection = objDBConn
strCity="<!--CITY.Value-->"
strStateAbbr="<!--STATEABBR.Value-->"
strSQL="SELECT Data.*, States.State FROM  Data INNER JOIN States ON Data.State_Abbr = States.State_Abbr where Data.State_Abbr ='" & strStateAbbr & "' and Data.City = '" & strCity & "'"
rsData.Open strSQL, objDBConn
If not rsData.EOF Then
strState=rsData("State")
%>
0
 
LVL 95

Accepted Solution

by:
Lee W, MVP earned 250 total points
Comment Utility
As for your issue, I have seen problems with text fields that are "too long", but never seen an issue with varchar/nvarchar fields.

I would suggest progressively increasing the field size and testing your code that way - as well as running the query in Query Analyzer (I assume that's still the tool in SQL 2005 - I still use 2000).

To test the query, for example, RIGHT BEFORE your rsData.Open line, include a line like this:
Response.Write "<br>" & strSQL & "<br>"
This will show you your entire SQL string and you can copy and paste that into Query Analyzer (or equivalent 2005 tool).
0
 

Author Comment

by:jmestep
Comment Utility
Thanks for removing that info.
I did some research last night and it turns out it was the nvarchar(MAX) field and that I would need to use a different kind of connection.
http://bytes.com/forum/thread589355.html

0
 
LVL 29

Expert Comment

by:Göran Andersson
Comment Utility
There is a size limitation on the record, but not the separate fields in the record. The entire record has to fit in the data buffer, which usually is 8 kb.

If you use any blob fields (text/image/varchar(max)), they are not sent as part of the record, but in a separate stream. Those fields can only be read once, and they have to be read in the exact order that they come in the stream, i.e. in the order that they are written in the select query.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

6 Experts available now in Live!

Get 1:1 Help Now