Concatenate issue in vb6

[firoz.raj]

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 .

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"))"

[RhinoBull]

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):

    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'"

[mikorians]

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)

[firoz.raj]

what is a uses of Chr$(34) in the following query?

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) + "))"

[mikorians]

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 "

[mikorians]

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)

[DataMiser]

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.

[si_the_geek]

& 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.

[DataMiser]

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 +

[mikorians]

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.

[firoz.raj]

even we remove &vbcrlf& it is working fine ? .Why ?

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&

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'"

[techgnome]

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

[mikorians]

Thank you. I'm tired.

[si_the_geek]

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.

[mikorians]

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

[mikorians]

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$

[DataMiser]

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.

[firoz.raj]

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.

   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'"

[VBClassicRocks]

Here's a couple of functions I use that may be helpful. For me, they
ease the coding process in DB programming.

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

[MarkT]

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.

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, " ")

[techgnome]

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

[AECode]

Hi firoz.raj,

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

Thanks.

A

[techgnome]

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

[MarkT]

You aren't using the StringBuilder in VB6.

[firoz.raj]

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 ?

[DataMiser]

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.

   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.

[MarkT]

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

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

[DataMiser]

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

[MarkT]

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.

[firoz.raj]

as MarkT Suggested .The following way also deserve worth .

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)