loop and concatenate SQL 1

[Cloonalt]

I have the below code, that loops through a data grid and writes a SQL string for each row. I need to concatenate all the SQL strings that get created into one string for later update to the database.

Can anyone help me with that? Any help appreciated.

Thanks.



'Loop through the grid
For lngRowIndex = 1 To ssQueued.MaxRows

ssQueued.Row = lngRowIndex

If Not IsNull(ssQueued.CellValue(ssQueued.Col, ssQueued.Row)) Then
ssQueued.Col = 7
strCell = ssQueued.CellValue(ssQueued.Col, ssQueued.Row)
ssQueued.Col = 1

Select Case ssQueued.CellValue(ssQueued.Col, ssQueued.Row)
Case "F"
strsql = "UPDATE folder SET flocation = '" & sUserLoc & "', fcurrloc = ""In: " & sUserLocfullname & "(" & sUserLoc & ")"" where fbarcode = '" & strCell & "' "

Case "I"
strsql2 = "update incert set icurrloc = ""In: " & sUserLocfullname & "(" & sUserLoc & ")"" where iparent = '" & strCell & "' "
End Select

End If

Next lngRowIndex

Set DB = Nothing

sUserLoc = ""
sFolderLocfullname = ""
sFolderLoc = ""
Exit Sub

 

[CautionMP]

Cloonalt,
Something like [tt]strConcatenated = strConcatenated & "|" & strSQL[/tt]

...
'Loop through the grid
For lngRowIndex = 1 To ssQueued.MaxRows
    
ssQueued.Row = lngRowIndex
        
If Not IsNull(ssQueued.CellValue(ssQueued.Col, ssQueued.Row)) Then
    ssQueued.Col = 7
        strCell = ssQueued.CellValue(ssQueued.Col, ssQueued.Row)
        ssQueued.Col = 1
         
Select Case ssQueued.CellValue(ssQueued.Col, ssQueued.Row)
    Case "F"
        [b]strConcatenated = strConcatenated & "|[/b]UPDATE folder SET flocation = '" & sUserLoc & "', fcurrloc = ""In: " & sUserLocfullname & "(" & sUserLoc & ")"" where fbarcode = '" & strCell & "' "

    Case "I"
        [b]strConcatenated = strConcatenated & "|[/b]update incert set icurrloc = ""In: " & sUserLocfullname & "(" & sUserLoc & ")""  where iparent = '" & strCell & "' "
End Select
                 
End If
            
Next lngRowIndex
...

Then to get the strings seperated again you could use split:

Dim i As Integer
Dim strSQL() As String
strSQL = Split(strConcatenated, "|")
'Start at 1 not 0 becasuse we added a bar when the string was empty
For i = 1 to UBound(strSQL)
  'Do something with strSQL(i)
Next i

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Cloonalt]

That worked great. Thank you!