Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: String concatenation SQL Statement

    *** MODERATOR ****

    I posted this in the wrong forum. I meant to post in MSSQL. Sorry. I tried to delete the post but it won't let me.

    Can you move it so I don't have to double post?

    Hi all,

    I am looking to concatenate values in a SQL statement but a VB 'With' statement seems to be throwing me some errors. The syntax appears correct in the reference I am using but the live compiler does not like the string. Here is the code:

    Code:
        ' Get the user color controls recordset
        strSQL = "SELECT fldTextBoxB, fldTextBoxF, fldFrameB,  fldFrameF, fldLabelB, fldLabelF, fldComboBoxB, fldComboBoxF FROM tblUsers" & _
                 "WHERE fldUserID = 'JoeUser'"
            
        
        'Set cursor type and lock type?
        'Static is the only method available for .CursorLocation=adUseClient
        objRst.Open strSQL, objCnn, adOpenStatic, adLockPessimistic
        
        
        objRst.MoveFirst
        Do Until objRst.EOF
        
            strSQL = "BEGIN TRANSACTION INSERT INTO tblUsers VALUES('" & _
            With objRst
                !fldTextBoxB & "', '" & _
                !fldTextBoxF & "', '" & _
                !fldFrameB & "', '" & _
                !fldFrameF & "', '" & _
                !fldLabelB & "', '" & _
                !fldLabelF & "', '" & _
                !fldComboBoxB & "', '" & _
                !fldComboBoxF & "')"
                .MoveNext
                If .EOF Then
                    strSQL = strSQL & "COMMIT TRAN"
                End If
            End With
            
            Debug.Print strSQL
        Loop
    
        'Execute the SQL statement
        'objCnn.Execute (strSQL)
    Thanks in advance,
    b
    Last edited by b1205; 02-22-04 at 21:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •