Solved

Classic asp limit on database field size?

Posted on 2008-10-11
5
730 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 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