Handling very long strings

Hello Experts:

I have seem to run into a problem that I don't know how to handle.

I am using Sql 7.0 for the database backend, and I store very long strings in a field defined as text.  When I pull this data into a string variable, it only retrieves about 1300 characters of data.  Im not sure if its the field definition or the variable definition that I'm having a problem with.  As long as the data is smaller than 1300 characters, it works fine.

Any ideas?

Data is stored as such:


I pull this into a string variable then run a sql statement as such:

Select * from tablename where fieldname in (Variable).
Who is Participating?

Improve company productivity with a Business Account.Sign Up

bob_onlineConnect With a Mentor Commented:
That's not what is happening.

when you use the In (list of items) syntax you are limited to 255 values in the list.  Note that 1300 / 5 = 260 so when you say about 1300 characters my guess is that it's really 1275 which, with 5 digit numbers, results in 255 items.

You're going to have to do something like this:

select * from tablename where fieldname in (Variable1) or fieldname in (Variable2) or fieldname in (variable3).

I'm assuming you're actually concatenating the string such as

"Select * from tablename where fieldname in (" & Variable1 & ") or fieldname in (" & Variable2 & ")"

If you know the max number of values, you can just dim the number of variables you need at design time.  Otherwise, I would use an array of strings and dynamically dimension it on the fly.  Then do something like

SQL = "Select * from tablename where "
For I = 1 to arrNames.Ubound
   SQL = SQL & "Fieldname in (" & arrNames(I - 1) & ") Or "
SQL = Left$(SQL, Len(SQL) - 4) ' to cut of the last " or "
To continue, you can build the array like this

done = false
ArrayIndex = -1
While not done
   redim preserve arrNames(arrNames.Ubound + 1)
   ArrayIndex = ArrayIndex + 1
   arrNames(ArrayIndex) = Left$(StringFromDatabase, 1275)
   StringFromDatabase = Mid$(StringFromDatabase, 1276)
   If Len(StringFromDatabase = 0 Then
      Done = True
   End If
You probably need to use the GetChunk method for retrieving the data... This is well documented.  Look up ADO/data types/managing long data types in your help files
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.

Finally, the 1275 value in my parsing routine won't work because it isn't allow for commas but you can easily calculate the corrrect value.

don't use getchunck - use ADO streams.
escheiderAuthor Commented:
Sorry its been awhile since I've checked on this, my wife is pregnant and haven't dedicated time to this site.

eosu, please give me an example on ADO streams..
escheiderAuthor Commented:
Decided to close this question.  I used your example and it works just fine...thanks for your time and input
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.