-
Sep 24th, 2012, 07:25 AM
#1
Thread Starter
Frenzied Member
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"))"
-
Sep 24th, 2012, 07:29 AM
#2
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'"
Last edited by RhinoBull; Sep 24th, 2012 at 07:34 AM.
-
Sep 24th, 2012, 05:51 PM
#3
Lively Member
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.
-
Sep 24th, 2012, 05:54 PM
#4
Thread Starter
Frenzied Member
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) + "))"
-
Sep 24th, 2012, 05:57 PM
#5
Lively Member
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 "
-
Sep 24th, 2012, 05:58 PM
#6
Lively Member
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.
-
Sep 25th, 2012, 07:33 AM
#7
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.
-
Sep 25th, 2012, 08:22 AM
#8
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.
-
Sep 25th, 2012, 09:58 AM
#9
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 +
-
Sep 25th, 2012, 12:53 PM
#10
Lively Member
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.
-
Sep 25th, 2012, 04:50 PM
#11
Thread Starter
Frenzied Member
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.
-
Sep 25th, 2012, 05:12 PM
#12
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
-
Sep 25th, 2012, 05:14 PM
#13
Lively Member
Re: Concatenate issue in vb6
Thank you. I'm tired.
-
Sep 25th, 2012, 05:45 PM
#14
Re: Concatenate issue in vb6
Originally Posted by mikorians
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.
-
Sep 25th, 2012, 05:57 PM
#15
Lively Member
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.
-
Sep 25th, 2012, 06:03 PM
#16
Lively Member
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.
-
Sep 25th, 2012, 06:48 PM
#17
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.
-
Sep 26th, 2012, 03:56 AM
#18
Thread Starter
Frenzied Member
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'"
-
Sep 26th, 2012, 07:35 AM
#19
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
-
Sep 26th, 2012, 07:39 AM
#20
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, " ")
-
Sep 26th, 2012, 07:55 AM
#21
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
-
Sep 26th, 2012, 07:57 AM
#22
New Member
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
-
Sep 26th, 2012, 08:07 AM
#23
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
-
Sep 26th, 2012, 08:11 AM
#24
Re: Concatenate issue in vb6
You aren't using the StringBuilder in VB6.
-
Sep 26th, 2012, 08:25 AM
#25
Thread Starter
Frenzied Member
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.
-
Sep 26th, 2012, 08:32 AM
#26
Re: Concatenate issue in vb6
Originally Posted by 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.
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.
-
Sep 26th, 2012, 08:48 AM
#27
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
-
Sep 26th, 2012, 08:51 AM
#28
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
-
Sep 26th, 2012, 08:59 AM
#29
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.
-
Sep 26th, 2012, 02:47 PM
#30
Thread Starter
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|