Results 1 to 30 of 30

Thread: Concatenate issue in vb6

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Question Concatenate issue in vb6

    HI Friends, i always getting compiler issue .as i have totally misunderstood at concating sql statement in more than one line . when concatenate using vbcrlf with & _
    can anyone tell me where i am missing something and why ? becuase it is in red at code window.so i never been confess this issue onwards in my life in cocatenation sql statement .let me know please any help would be highly appreciated .
    Code:
    SqlQuery = " SELECT TradingSummaryStatus.*, TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, TradingSummaryStatus.YEAR, TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME, TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID, TradingSummaryStatus.BRANDDETAIL, " & vbCrLf & _
    " From TradingSummaryStatus" & vbCrLf & _
    " Where (((TradingSummaryStatus.Status) <> "C"))"

  2. #2
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,132

    Re: Concatenate issue in vb6

    You need to substitute dbl quotes with single quote in the Where clause; also it looks like there was an extra comma (I removed it) before the From keyword and finally parentesis are not necessary as well (removed):
    Code:
        SqlQuery = " SELECT TradingSummaryStatus.*, TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, " & _
                        "TradingSummaryStatus.YEAR, TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME,  " & _
                        "TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID, TradingSummaryStatus.BRANDDETAIL" & vbCrLf & _
                    " From TradingSummaryStatus" & vbCrLf & _
                    " Where TradingSummaryStatus.Status <> 'C'"

  3. #3
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    Code:
    SqlQuery = " SELECT TradingSummaryStatus.*, TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, " & _
                    "TradingSummaryStatus.YEAR, TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME,  " & _
                    "TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID, TradingSummaryStatus.BRANDDETAIL" & vbCrLf & _
                    " From TradingSummaryStatus" & vbCrLf & _
                    " Where TradingSummaryStatus.Status <> " + Chr$(34) + "C" + Chr$(34) + "))" + Chr$(34)
    also works if you need the quotes - you can leave the quote off the end if you want + Chr$(34)
    Last edited by mikorians; Sep 24th, 2012 at 05:55 PM.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Concatenate issue in vb6

    what is a uses of Chr$(34) in the following query?
    Code:
    SqlQuery = " SELECT TradingSummaryStatus.*, TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, " & _
                    "TradingSummaryStatus.YEAR, TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME,  " & _
                    "TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID, TradingSummaryStatus.BRANDDETAIL" & vbCrLf & _
                    " From TradingSummaryStatus" & vbCrLf & _
                    " Where TradingSummaryStatus.Status <> " + Chr$(34) + "C" + Chr$(34) + "))"

  5. #5
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    You've added quotes the the string SqlQuery (This is Visual Basic 6 we're talking about, isn't it?)
    Chr$(n) allows any of the 256 characters to be added to a string
    34 happens to be the double quote "

  6. #6
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    I also use + and not & for my strings (just personal preference, I don't remember the difference...
    There's also a String(quantity,n) function for lots of chr$(n) aka chr(n)
    Last edited by mikorians; Sep 24th, 2012 at 06:07 PM.

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Concatenate issue in vb6

    Best to use & for concantonation and + for addition since that is the way they are meant to be used.
    Makes for code that is easier to read and easy to tell if your intent is to add or concantonate.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Concatenate issue in vb6

    & means concatenate, + means add.

    While using + for strings will usually concatenate them, in many circumstances it wont, and instead will creates bugs and/or errors.

    One example is where one or both items contain something that can be converted to a numeric value, in which case there is a good chance that numeric addition will be attempted instead, and that will either give you an unwanted value, or give you an error. Based on previous tests, I was left unsure of what the deciding factor(s) are which cause it.

    Using + to join strings is not a good idea, and it is a bad habit to get in to.

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Concatenate issue in vb6

    I think in cases where you are working with variants that contain numeric data but are meant to be strings you would run into problems with +

  10. #10
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    I ONLY use it with strings and numerics, so I've never had a problem. It differentiates between & as a variable type and avoids confusion for me.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Concatenate issue in vb6

    even we remove &vbcrlf& it is working fine ? .Why ?
    Code:
    SqlQuery = " SELECT TradingSummaryStatus.*, TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO," & _
    " TradingSummaryStatus.YEAR, TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME, TradingSummaryStatus.STATUS," & _
    " TradingSummaryStatus.BRANDID, TradingSummaryStatus.BRANDDETAIL, " & _
    " From TradingSummaryStatus" & _
    " Where (((TradingSummaryStatus.Status) <> 'C'))"
    the following code also gives the same result like the above .so what is the uses of &vbcrlf&
    Code:
    SqlQuery = " SELECT  TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, " & vbCrLf & _
                        " TradingSummaryStatus.YEAR,TradingSummaryStatus.CSALESLEVEL, TradingSummaryStatus.WEEKNO," & vbCrLf & _
                        " TradingSummaryStatus.UPDATEDTIME, " & vbCrLf & _
                        " TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID,TradingSummaryStatus.LVLRECDATE," & vbCrLf & _
                        " TradingSummaryStatus.BRANDDETAIL " & vbCrLf & _
                        " From TradingSummaryStatus" & vbCrLf & _
                        " Where TradingSummaryStatus.Status <> 'C'"
    Last edited by firoz.raj; Sep 25th, 2012 at 04:55 PM.

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Concatenate issue in vb6

    it's strictly a cosmetic thing... if you were to print out the statment with the vbcrlf, then your sql would be "nicely" formatted on multiple lines... if you skip the vbrclf, then it gets appended as one big long line. Thing is SQL doesn't care if it's on one line or multiple lines... all it cares about is the syntax. as long as you leave a space on the end so that your words don't slam into each other, then you can skip the vbrclf. SQL won't care.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    Thank you. I'm tired.

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Concatenate issue in vb6

    Quote Originally Posted by mikorians View Post
    I ONLY use it with strings and numerics, so I've never had a problem.
    No, you've never had a problem because you have been lucky - so far.

    There have been many examples on the forums where it has failed with a String and a numeric data type, and even some cases where it was just two Strings. Worst of all, in most cases you wont know about it unless you manually check the values (and thus could easily use/store lots of bad data).
    It differentiates between & as a variable type and avoids confusion for me.
    I don't see how you could confuse a & on its own with one attached to a variable/number (and it seems especially strange as instead you are choosing to need to differentiate between + [add] and + [concatenate/accidentally add]), but each to their own.

  15. #15
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    Well, I've been around since all you COULD use was +
    I'm adept at avoiding the problems you describe.

  16. #16
    Lively Member mikorians's Avatar
    Join Date
    Jun 2010
    Location
    In a fog
    Posts
    83

    Re: Concatenate issue in vb6

    My BASIC directory listing (heh) - remove this if you like
    -Test Sprite Playback
    2dedit
    2d project
    2DSkel
    2d Stars
    3d
    3DS MAX Colored Vertex Recovery Tool
    Accessing Other Windows - Simple Sample
    Accessing Other Windows
    ADF reader
    Amort
    AntiScribe
    Apple Fixer Dos 3.3 (Alpha 1.0)
    Apple SPACE
    Appointments
    Arcade Marquee Stitcher
    Ascii Stripper
    Atari map tiler
    Autohypno
    Beep PC Speaker
    BigKeys
    Blunt Mallet
    BMP2WAV
    Branchdoc
    Broken Shortcut Fixer
    Budget Manager
    Byte space counter
    Calc
    CatToy
    CD Covers
    CD Experiment 1
    CD experiment
    Command Line Arguments
    Compatibility Battery
    ComWatch Screen Saver
    Construction Planner
    Convert Textures
    Copier Multi-Target
    CopyAlpha
    Cosmos (Space lookalike)
    Countdown
    Cue Cat
    Database Experiment#1
    DDE
    Delete
    DirectMusic8
    DirectMusic7
    DmgTga Recovery
    DPG
    DPG rpg
    DPG space game
    Encoder
    Encoder2
    EVA9000
    Expand (Batch)
    Eye file processor
    fade
    Fantasy 2D Animator
    Fantasy II
    File Compare Dup Removal
    File recovery tool
    File System Use
    Fisheye to Merc (Cheap, Wrong)
    Folder Calculator
    Folder Compare
    Fractions
    Ged Batch Converter
    Hex draw
    Hexplot
    HTML Table Extractor
    Image Compare
    Inches
    KentrosDoor
    Kitchen Timer
    Language Buttons & DVD
    Life (NG)
    Little Lotto Picker
    Lores Tools
    Lotto Picker
    Low Level Disc Activity
    Lyric Player w-face
    Magic 8 Ball
    Master Game Tilesets
    Material Optimizer
    Max Unicode Object Renamer
    Mazer
    Mikron
    MikSymb2BMP
    MorseSender
    Mouse Controller
    Mouse Controller (Generic)
    Mouse Orbiter
    Multi-dimensional Contact Application
    Multithreading comparison
    Musicbox
    Music Master (Promising, unfinished)
    MyDialer
    Netflix Queue Scrambler - Works!
    Net Pong
    Network game proto
    Newton Compiler
    Newton Project
    Norn
    Pack Scheduler
    Pallette Sorter
    Picture - Invert Intensities
    Plasma Protector
    Play movie
    Play movie2
    Precise Printing
    Project Display
    Quicktime Image to Bmp Batch Converter
    Raw Wav Ripper
    Raw Wav Stripper
    Rendezvous
    Resource Example
    Roman2Arab
    Rude Caller ID
    Runner
    Scrolling Pictures
    Shell Super
    Slideshow
    Smooth Scroller
    SNTP Time Server (NG)
    SNTP Time Sync
    Sony LDP-1450
    Space (Mine)
    Speckler
    Speech
    Spline From BMP
    Sprites
    Sprite Editor
    Sprite flipper
    SRM Simulator
    Stamp Control
    Stegoaudio
    Stegoattempt
    Sticky
    Stretch test
    Super Dungeon Pics
    Tackon
    Teddy Sez
    TextView (ANSI)
    Tile Tester
    TinyFont
    TreeView
    Treeview2
    Trip calculator
    Ultima III Project
    Unknown Alphabet Hex Search
    Unlimited Math Package
    Unwarper Lens (Mine)
    VB_CRC32
    VCE Text-to-speech (MS TTS) file creator
    Virtual resolution
    voice recognition
    VR Suit Aquisition
    VR Suit Interface
    Watchbook
    Wave Image Plaything (unfinished)
    Wavepaint - destructive!
    Whitespace Stripper
    WIF Batch Converter
    X10 Seasons
    XBox c HDD code
    _Basic-A
    _Classic BASIC Games
    _Convenience Menu
    _DVD
    _Joe's Debugging Tool
    _Latest Games
    _Old Programs
    _Old program converts
    _Pic stuff
    _QBasic
    _Reference
    _Stamp
    _Windows Improvement Project
    ansmach.zip
    avicreate.txt
    BRACE COUNTER.BAS
    Clipboard Trimmer.exe
    crc32.txt
    DeviceTree_demo.zip
    DeviceTree_src.zip
    Direct3D Tutorials.lnk
    EDITING SPRITES.txt
    fade.zip
    fantasynotes.txt
    getmem.txt
    HardwareInfo.zip
    ico format.txt
    imalloc.txt
    IShellFolder_Type_Library.zip
    Priority table.txt
    Rec2Pol.txt
    rottext.zip
    setbitmappixels.zip
    Spidy.exe
    Sprite example1.zip
    t.txt
    Using_Malloc_in_VB.zip
    VB6_AVI_Creator.zip
    VB6_AVI_Extractor.zip
    x10 format.txt


    Now of course, I don't condone poor programming habits, but let's remember what BASIC WAS, too.
    Old habits for old timers, remember.
    Remember A$
    Last edited by mikorians; Sep 25th, 2012 at 06:14 PM.

  17. #17
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Concatenate issue in vb6

    I too have been around since + was the only option [wrote my first little program on a Timex Sinclair 1000 then moved on to Commodore C64 before moving to Basic and GWBasic] but as of VB4 the & was the proper way to append strings and that is what I have used in VB4 5 6 and .net versions.

  18. #18

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Concatenate issue in vb6

    now i think i am totally understood on concatenate issue . i think concatenate string using the following way is the best amoung all the above way.
    Code:
       SqlQuery = "SELECT  TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, "
             SqlQuery = SqlQuery & " TradingSummaryStatus.YEARs,TradingSummaryStatus.CSALESLEVEL,"
             SqlQuery = SqlQuery & "TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME,"
             SqlQuery = SqlQuery & " TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID,"
             SqlQuery = SqlQuery & " TradingSummaryStatus.LVLRECDATE, TradingSummaryStatus.BRANDDETAIL"
             SqlQuery = SqlQuery & " From TradingSummaryStatus"
             SqlQuery = SqlQuery & " Where TradingSummaryStatus.Status <> 'C'"

  19. #19
    Fanatic Member
    Join Date
    Mar 2009
    Posts
    804

    Re: Concatenate issue in vb6

    Here's a couple of functions I use that may be helpful. For me, they
    ease the coding process in DB programming.
    Code:
    Public Function Quote(ByVal Str As String) As String
     Str = Replace$(Str, "'", "''") 'escape embedded single quotes
     Quote = "'" & Str & "'"
    End Function
    'for DAO
    Public Function QuoteLike(ByVal Str As String) As String
     QuoteLike = Quote("*" & Str & "*")
    End Function
    'for ADO
    Public Function QuoteLike(ByVal Str As String) As String
     QuoteLike = Quote("%" & Str & "%")
    End Function
    
    Private Sub Form_Load()  'Example usage
     Dim Sql As String
     Sql = "Select * From SomeTable Where SomeField = " & Quote(Text1.Text)
     Sql = "Select * From SomeTable Where SomeField Like " & QuoteLike(Text1.Text)
    End Sub

  20. #20
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Concatenate issue in vb6

    I have always been a fan of using an array for concatenation of large strings. I don't think this one really falls into that category but it is still a pretty good solution.
    Code:
    Dim sqlLines(6) As String
    Dim SqlQuery As String
    
        sqlLines(0) = "SELECT TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO,"
        sqlLines(1) = "TradingSummaryStatus.YEARs, TradingSummaryStatus.CSALESLEVEL,"
        sqlLines(2) = "TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME,"
        sqlLines(3) = "TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID,"
        sqlLines(4) = "TradingSummaryStatus.LVLRECDATE, TradingSummaryStatus.BRANDDETAIL"
        sqlLines(5) = "From TradingSummaryStatus"
        sqlLines(6) = "Where TradingSummaryStatus.Status <> 'C'"
        
        SqlQuery = Join(sqlLines, " ")

  21. #21
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Concatenate issue in vb6

    Personally.... I go for storing the queries in the database itself if I can... in cases where that isn't feasible for what ever reason, I use StringBuilder...

    Additionally, I will try to use parameters when possible... that eliminates confusing code, and eliminates the need for things like quoting functions. I should just be able to plug the data in... I shouldn't need to fiddle with it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  22. #22
    New Member
    Join Date
    Sep 2012
    Posts
    3

    Re: Concatenate issue in vb6

    Hi firoz.raj,

    Why you want to use the "VBCrLf"? I mean the sql command can work without it.

    Thanks.

    A

  23. #23
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Concatenate issue in vb6

    because...
    it's strictly a cosmetic thing... if you were to print out the statment with the vbcrlf, then your sql would be "nicely" formatted on multiple lines
    which is why I use StringBuilder.AppendLine ...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  24. #24
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Concatenate issue in vb6

    You aren't using the StringBuilder in VB6.

  25. #25

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Concatenate issue in vb6

    I have always been a fan of using an array for concatenation of large strings. I don't think this one really falls into that category but it is still a pretty good solution.

    Code:
    Dim sqlLines(6) As String
    Dim SqlQuery As String

    sqlLines(0) = "SELECT TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO," 'This is string
    sqlLines(1) = "TradingSummaryStatus.YEARs, TradingSummaryStatus.CSALESLEVEL," 'This is string
    sqlLines(2) = "TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME," 'This is string
    sqlLines(3) = "TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID," 'this is string
    sqlLines(4) = "TradingSummaryStatus.LVLRECDATE, TradingSummaryStatus.BRANDDETAIL" 'this is string
    sqlLines(5) = "From TradingSummaryStatus" 'This is string
    sqlLines(6) = "Where TradingSummaryStatus.Status <> 'C'" 'this is string

    SqlQuery = Join(sqlLines, " ")
    Why any need to join " " ? . because i don't see any "" in all the string .so how this join works ?
    Last edited by firoz.raj; Sep 26th, 2012 at 08:39 AM.

  26. #26
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Concatenate issue in vb6

    Quote Originally Posted by firoz.raj View Post
    now i think i am totally understood on concatenate issue . i think concatenate string using the following way is the best amoung all the above way.
    Code:
       SqlQuery = "SELECT  TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, "
             SqlQuery = SqlQuery & " TradingSummaryStatus.YEARs,TradingSummaryStatus.CSALESLEVEL,"
             SqlQuery = SqlQuery & "TradingSummaryStatus.WEEKNO, TradingSummaryStatus.UPDATEDTIME,"
             SqlQuery = SqlQuery & " TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID,"
             SqlQuery = SqlQuery & " TradingSummaryStatus.LVLRECDATE, TradingSummaryStatus.BRANDDETAIL"
             SqlQuery = SqlQuery & " From TradingSummaryStatus"
             SqlQuery = SqlQuery & " Where TradingSummaryStatus.Status <> 'C'"
    This is the way I normally do it [but without the table name attached to every field] when I need to build the string through code.

    I had never thought to use the array option, would be interesting to see how the speed compares to building the string like this.

    Note: There is no need to specify the table name for all your fields here. You are only dealign with one table so it is clear what the table is without adding it to them all.

  27. #27
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Concatenate issue in vb6

    In a string the size of this query, concatenating the strings would problaby be faster than using Join. The more you use SqlQuery = SqlQuery & in your string building the slower the concationation will become.

    here is a sample for you
    Code:
    Option Explicit
    Private Declare Function GetTickCount& Lib "kernel32" ()
    
    Private Sub Command1_Click()
    Dim strLines() As String
    Dim strString As String
    Dim i As Long
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim ub As Long
    
        ub = 5000
    
        lngStart = GetTickCount
        ReDim strLines(ub)
        For i = 0 To ub
            strLines(i) = i
        Next i
        strString = Join(strLines, " ")
        lngEnd = GetTickCount
        
        Debug.Print "Join - " & lngEnd - lngStart
        
        
        lngStart = GetTickCount
        For i = 0 To ub
           strString = strString & i
        Next i
        lngEnd = GetTickCount
        
        Debug.Print "concatenation - " & lngEnd - lngStart
    End Sub

  28. #28
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Concatenate issue in vb6

    Interesting and makes sense as well. As a side benifit there would be less characters to type and more of the line would be related to the sql statement making it eaiser to read.

    I'll have to remember this next time I need to build a large string

  29. #29
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Concatenate issue in vb6

    If you really want to see the differences, in the above example try setting ub to 500. I would think you should get pretty much 0 for both methods. Then try setting ub to 50,000 and go get a cup of coffee while it runs. That is where you are going to see a huge difference.

    @firoz.raj - the delimiter for the join function is optional. If you don't specify one the " " (space) is used. In this case
    SqlQuery = Join(sqlLines, " ")
    and
    SqlQuery = Join(sqlLines)

    would result in the same string being created.

  30. #30

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Concatenate issue in vb6

    as MarkT Suggested .The following way also deserve worth .
    Code:
    Sqlline(0) = " SELECT  TradingSummaryStatus.ID, TradingSummaryStatus.GROUP_NO, "
               Sqlline(1) = " TradingSummaryStatus.YEAR,TradingSummaryStatus.CSALESLEVEL,"
               Sqlline(2) = " TradingSummaryStatus.WEEKNO,"
               Sqlline(3) = " TradingSummaryStatus.UPDATEDTIME, "
               Sqlline(4) = " TradingSummaryStatus.STATUS, TradingSummaryStatus.BRANDID,"
               Sqlline(5) = " TradingSummaryStatus.LVLRECDATE,"
               Sqlline(6) = " TradingSummaryStatus.BRANDDETAIL "
               Sqlline(7) = " From TradingSummaryStatus"
               Sqlline(8) = " Where TradingSummaryStatus.Status <> 'C'"
               
            SqlQuery = Join(Sqlline)

Posting Permissions

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



Click Here to Expand Forum to Full Width