doghund
asked on
variable-length string
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!
DH
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!
DH
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.
You might just have to look at making two or three different statements and then executing them.
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
Next
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
[]
Feres
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
Next
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
[]
Feres
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your problem is that you didn't declare the variable a STRING, but a variant.
Declare it as string !
Dim strUpdate as String
Declare it as string !
Dim strUpdate as String
ASKER
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.
DH
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.
DH
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 ($).