variable-length string

Posted on 2000-01-31
Medium Priority
Last Modified: 2010-05-02
Anybody come accross this before?

I'm building a string like this:

Dim strUpdate

strUpdate = "update MY_TABLE " & _
              "set " & _
              "COL1 = " & strExportData(4) & ", " & _
              "COL2 = " & strExportData(5) & ", " & _
              "COL3 = " & strExportData(6) & ", " & _
              "COL4 = " & strExportData(7) & ", " & _
              "COL5 = " & strExportData(8) & ", "
  strUpdate = strUpdate & _
              "COL6 = " & strExportData(9) & ", " & _
              "COL7 = " & strExportData(10) & ", " & _

and so on. (There are 230 columns in total.)

My problem is that once the string gets 255 characters in it, it stops growing.

Grateful for any help!

Question by:doghund

Expert Comment

ID: 2471842
From the MSDN help files:

String Data Type

There are two kinds of strings: variable-length and fixed-length strings.

A variable-length string can contain up to approximately 2 billion (2^31) characters.

A fixed-length string can contain 1 to approximately 64K (2^16) characters.
Note   APublic fixed-length string can't be used in aclass module.

The codes forString characters range from 0–255. The first 128 characters (0–127) of the character set correspond to the letters and symbols on a standard U.S. keyboard. These first 128 characters are the same as those defined by theASCII character set. The second 128 characters (128–255) represent special characters, such as letters in international alphabets, accents, currency symbols, and fractions. Thetype-declaration character for String is the dollar sign ($).

Expert Comment

ID: 2472322
It may be that your database engine does not support SQL statements which are more than 255 characters.  If this is an Access db than that is definitely the case.  Because Dr.Delphi is right string data-types do hold more characters but using this size of a string is your problem, I would guarantee it.

You might just have to look at making two or three different statements and then executing them.

Expert Comment

ID: 2472362
When i saw your question i thought it was because of the strUpdate declaration but i tried to remake the problem and wrote the following code:

Dim a
Dim s As String
Dim i As Integer

a = ""
For i = 0 To 2000
  a = a & i Mod 10
  s = s & i Mod 10

Debug.Print "a = " & Chr(34) & a & Chr(34)
Debug.Print "len(a) = " & Len(a)
Debug.Print "s = " & Chr(34) & s & Chr(34)
Debug.Print "len(s) = " & Len(s)

In both cases the variable didn't stop to grow.
i sugest you to execute this code on your machine and watch what hapens, then tell me if the len of 'a' and 's' isn't 2001

but i still think the problem is on declaration
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 18

Accepted Solution

deighton earned 400 total points
ID: 2477488
Are you displaying the screen in some way that limits what you see, or is a control character getting in there.  Check out the len(strupdate)
LVL 12

Expert Comment

ID: 2477836
Your problem is that you didn't declare the variable a STRING, but a variant.
Declare it as string !

Dim strUpdate as String

Author Comment

ID: 2478020
The problem was pretty dumb. When I ran the SQL string there was an error. So I debugged to see what was written in the string using 'Add Watch'.
This shows only the first 255 chars of the string.
Using the Imediate window and writing
'?strUpdate' I got the entire string and when I ran the sql string direct in the DB I found that the problem was a constraint which had been broken.
I honestly thought that I had found a bug in VB. Sorry about that, and thanks for your help.


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

597 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