Solved

Classic asp limit on database field size?

Posted on 2008-10-11
5
733 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
ID: 22695602
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
ID: 22697021
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
ID: 22697548
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
ID: 22700525
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Suggested Solutions

Title # Comments Views Activity
Voice recognition ASP or ASP.NET or JavaScript 2 70
ASP CDO - Limiting Connections 3 59
Select record with the most recent date 14 67
FileUp - Classic ASP 5 19
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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…

840 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