Trying to wrap long SQL string

Getting Started
1

Hi All,

I am very new to Xojo and I am having trouble figuring out where I am going astray in trying to wrap a long string I am using to retrieve SQL data

Current code - which works but is hard to look at is

[code] dim db as new ODBCDatabase

lstWO.DeleteAllRows

dim strSQL as string
db.DataSource=""+uDSN+""
if db.connect then
strSQL = “SELECT WONO,SHIPNAME,UnitNo,ServiceVan,ShopDateIn,ShopEstimatedCompletionDate,(case when ShopStatus = ‘In Process’ then ‘In Shop’ when ShopStatus = ‘Need Order’ then ‘Need Parts’ when ShopStatus = ‘Complete’ then ‘Complete’ when ShopStatus = ‘Need Addl’ then ‘Hot Job’ else ‘Waiting’ end ) as color FROM WO where Disposition=1 AND SALEDEPT = ‘40’ AND SALEBRANCH = '” + str(BranchID) + “’ order by WONO”
Dim rs As RecordSet = db.SQLSelect(strSQL)
if rs <> nil then[/code]

I am trying to wrap the long strSQL string so that it is easier to see on screen. I tried to wrap using underscores as shown below, but the code kicks out a syntax error

[code] dim db as new ODBCDatabase

lstWO.DeleteAllRows

dim strSQL as string
db.DataSource=""+uDSN+""
if db.connect then
strSQL = “SELECT WONO,SHIPNAME,UnitNo,ServiceVan,ShopDateIn,ShopEstimatedCompletionDate,”_
"(case when ShopStatus = ‘In Process’ then ‘In Shop’ when ShopStatus = ‘Need Order’ then ‘Need Parts’ when ShopStatus = ‘Complete’ then “_
" ‘Complete’ when ShopStatus = ‘Need Addl’ then ‘Hot Job’ else ‘Waiting’ end ) as color FROM WO where Disposition=1 AND SALEDEPT = ‘40’ AND SALEBRANCH = '” + str(BranchID) + “’ order by WONO”
Dim rs As RecordSet = db.SQLSelect(strSQL)
if rs <> nil then
While Not rs.EOF[/code]

I am sure I am missing something quite basic, but I cannot seem to quite put my finger on it.

thanks

Joe

2

You’re on the right track.

Just imagine that the underscores don’t exist… that the lines are pulled up and smooshed back together without the underscore. So

"...ShopEstimatedCompletionDate,"_
    "(case..."

becomes

"...ShopEstimatedCompletionDate,""(case..."

Do you see the syntax error now?

Add a + in there to concatenate it and you should be fine:

"...ShopEstimatedCompletionDate," + "(case..."

with underscore:

"...ShopEstimatedCompletionDate,"_
 + "(case..."
3

Marc,

Thank you so much, that was exactly what I was missing. I try to help myself by reading the documentation and forums first but I just could not put a finger on the +. I even tried an ampersand in front of the line.

Joe

4