String Append truncation - why can't I add more characters to this string?

Posted on 2003-02-25
Medium Priority
Last Modified: 2010-04-07
OK, in the code below I have 77 values in an array.  They are all valid and when accessed directly are fine.  The code was designed to add each element of the array to the string and add parenthesis and other appropriate SQL formatting.  Unfortunately at a point in the string (currently the 27th value in the array, but if the values are changed the problem will occurr in a different value, though at the same point in the string) the string will not allow any more info to be appended to it.  I inherited this code and I am simply switching from Sybase to Oracle.  I have no idea why this problem is occurring, and I cannot fathom that it has anything to do with the DB change.  

At the very least the final append should work (ie adding the ")" to the SQL.  The message boxes I added simply to verify that the data was in the array, which it was.

Any help would be appreciated.  If I haven't followed any posting protocols, I apologize, I've never used this before...

Public Function CreateInsertSQL(strTableName As String, ByRef arrPassedArray As Variant) As String
' Creates "Outgoing" INSERT SQL.                                            *
'                                                                           *
' Arguments:  strTableName - SQL Server table be appended too.              *
'             arrPassedArray - Array containing the values and formats of   *
'                              fields being inserted.                       *
'                                                                           *
' Returns: CreateInsertSQL - Returned SQL string.                           *
'                                                                           *
  Dim intI As Integer
  CreateInsertSQL = "INSERT INTO " & strTableName & " VALUES ("
  ' We start on 2nd XL column because
  ' the 1st is the include indicator,
  ' which is not stored in warehouse.
  For intI = 2 To UBound(arrPassedArray, 1)
    ' Check type and assign quotes if necessary
    If arrPassedArray(intI, const_XLCellFormat) = "@" Or _
        arrPassedArray(intI, const_XLCellFormat) = "General" Or _
        arrPassedArray(intI, const_XLCellFormat) = """ """ Or _
        arrPassedArray(intI, const_XLCellFormat) = "mm/dd/yy" Then
      CreateInsertSQL = CreateInsertSQL & _
          IIf(arrPassedArray(intI, const_XLCellValue) <> 0 And arrPassedArray(intI, const_XLCellValue) <> "" And _
              arrPassedArray(intI, const_XLCellValue) <> " " And Not IsNull(arrPassedArray(intI, const_XLCellValue)), """" & _
              strReplace(arrPassedArray(intI, const_XLCellValue), """", "'", const_XLCellValue) & """", "Null")
      CreateInsertSQL = CreateInsertSQL & arrPassedArray(intI, const_XLCellValue)
    End If
    If intI <> UBound(arrPassedArray, const_XLCellFormat) Then
      CreateInsertSQL = CreateInsertSQL & ","
    End If
  ' test area
  'MsgBox arrPassedArray(25, const_XLCellValue)
  'MsgBox arrPassedArray(26, const_XLCellValue)
  'MsgBox arrPassedArray(27, const_XLCellValue)

  CreateInsertSQL = CreateInsertSQL & ")"
End Function

Question by:pairoffives
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8021076

I'm basically gonna listen to see what anyone else says.  But, I'm also going to make one suggestion which may or may not work.

Do not use the Function name as your working string variable.  Instead, declare a string variable in this function, build your SQL statement with it and then on exiting the function, send the string variable back with the function.

   Dim stxt$

   Substitute stxt for CreateInsertSQL

   and finally:
   CreateInsertSQL = stxt



LVL 44

Expert Comment

ID: 8021557
just out of curiosity, have you set a BREAKPOINT in this Function, and then checked the actual string each time you append to it?  If so, wha is the LENGTH of the string when you reach the problem?

How long are the individual values in the array, that you are attempting to append to the String?


Expert Comment

ID: 8021583
Just how big is the string getting?  Could you be running up against an upper limit for the String data type in the platform you're running?

Not sure of the limitations of any of the systems you mentioned in regard to the size a single string can be but it's one place I'd check.  Perhaps you need to store your ever-growing value in some sort of large object until it's fully constructed.

Hope this helps,
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8022887
I have ran the code with a subsituted strReplace function.

I experienced no problems no matter if I assigned directly to the function or to a variable as Sweat suggested.

I think the problem has something to do with strReplace.

My last test had the SQL string with a length of 916901
as returned by len(str).  Surly your SQL statement cannot be that big.

Are youy getting an error message?

Can you post the code for strReplace and the code used to generate test data for the function.


Author Comment

ID: 8025892
Thanks for the replies.  I did try that first suggestion from Sweat, but to no avail.  Here's the data I'm throwing into the SQL INSERT statement.  This data is pulled from an Excel Spreadsheet, which has not changed since the App was run successfully against Sybase (developed by another programmer. offsite, who is not with us anymore).  My best guess is that there's something weird going on with my version of VB or some kinda bug that I need to fix.

Anyway here's the test data and an explanation of what I saw...It's coming from an Excel SS so it's tab delimited below...
-1     48     2028     200211     51     COMB     Production Strategy     GLC     North East Ins Co     Auto Liability     X     TR     N/A     0     0     0     ACTIVE     0     0     No     Yes     WWeighted     0     wweighted paid     Benchmark     Benchmark     0     0     0.80     0.80     0     0     1.25     1.25     All yr avg benchmark     All yr avg benchmark

Here's what We're seeing from the function...(I added a watch to the variable CreateInsertSQL and put a breakpoint at the Next and at the end of the function...

"INSERT INTO RES_XLS_RESERVING_KEY_TEMP VALUES (48,2028,200211,"51","COMB","Production Strategy","GLC","North East Ins Co","Auto Liability","X","TR","N/A",0,0,0,"ACTIVE",0,0,"No","Yes","WWeighted",0,"wweighted paid","Benchmark","Bencm"

I went in and altered the data in the test spreadsheet, changing the value of the cells, but it always cuts off at the same point...


Author Comment

ID: 8025926
one further note...the actual test data had more data, but it didn't seem worth posting all 77 values, I've checked the array with the message boxes and the array holds each cell's values correctly...

Accepted Solution

JohnMcCann earned 200 total points
ID: 8027007
I've mdofied the code slightly.  Replace your code with this (Save your code first, obviously).

Public Function CreateInsertSQL(strTableName As String, ByRef arrPassedArray As Variant) As String
Dim intI As Integer, strSQL As String, strFormat As String, strValue As String
Dim tmpSQL As String

   Debug.Print "Ubound Format", UBound(arrPassedArray, const_XLCellFormat)
   Debug.Print "Ubound Value", UBound(arrPassedArray, const_XLCellValue)
   strSQL = "INSERT INTO " & strTableName & " VALUES ("

   For intI = 2 To UBound(arrPassedArray, 1)
      strFormat = Trim(arrPassedArray(intI, const_XLCellFormat))
      strValue = Trim(arrPassedArray(intI, const_XLCellValue))
      If strFormat = "@" Or strFormat = "General" Or strFormat = """ """ Or strFormat = "mm/dd/yy" Then
         If strValue <> 0 And strValue <> "" And strValue <> " " And Not IsNull(strValue) Then
            tmpSQL = """" & strReplace(strValue, """", "'", const_XLCellValue) & """"
            tmpSQL = "Null"
         End If
         tmpSQL = strValue
      End If
      If intI <> UBound(arrPassedArray, const_XLCellFormat) Then
         tmpSQL = tmpSQL & ","
      End If
      Debug.Print intI, Len(tmpSQL), Len(strSQL)
      strSQL = strSQL & tmpSQL
   Next intI
   strSQL = strSQL & ")"
End Function

Press Ctrl + G (Brings up debug window)
Press F5 (Start)

Watch the debug window and monitor the three values.

The first is the index the second the length of the value and the third the length of the string.

What happens?

Author Comment

ID: 8028354

Thanks for you and the other's efforts.  I was able to track down the problem after looking at the string length as your code suggested.  It turns out that the string was not being concatenated at all.  the watch window was simply limited in size that it could display.  This was corresponding with a DB error b/c one of the 77 values in the spreadsheet contained a semi colon.  Since Sybase had no problem with the semicolon, it wasn't accounted for, but when passed into oracle without an escape character, it would blow up.  I've added ";" to the function as a character that needs to be watched out for.  Again, thanks for your help, and hopefully I don't look too foolish...I'm used to C++ and Sybase, VB and Oracle are both new to me.

Thanks again, and I'm going to be visiting this site much more often!


Author Comment

ID: 8028356
Thanks agin John

Expert Comment

ID: 8029514
No problem glad I could be of help.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month9 days, 9 hours left to enroll

762 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