[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

Classic asp limit on database field size?

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
jmestep
Asked:
jmestep
  • 2
1 Solution
 
jmestepAuthor Commented:
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
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
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
 
jmestepAuthor Commented:
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
 
Göran AnderssonCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now