Dynamic Array populate and recall contents

Databases
1

Good Evening All,

As I always I preface my questions with the statement that I am quite new to programming in general and to Xojo in specific, so please forgive if my question seems too elementary.

Basically I am trying to populate a dynamic string array from a SQL table which I can then recall for use in later SQL statements. I am close but not quite able to get to the results.

To populate the array, I am using following code

[code] dim db as new ODBCDatabase

dim strSQL as string
db.DataSource=""+uDSN+""
if db.connect then
strSQL = “SELECT Dept from DEPT where DeptGroup = ‘Service’”
Dim rsB As RecordSet = db.SQLSelect(strSQL)
if rsB = nil then
msgbox "No Service Depts Found or There was an error in my sql: "+ db.ErrorMessage
else
While Not rsB.EOF
ServiceDept.Append(rsB.Field(“Dept”).StringValue)
rsB.MoveNext
Wend
rsB.Close
end if
end if
[/code]

It appears that I am properly getting the expected results which should be (“40”,“42”,“44”)

Question 1 - Is the actual ServiceDept array being stored as (“40”,“42”,“44”) or is it being stored as (40,42,44) ? Is there any quick way to display the contents of the array? I can show a specific element such a ServiceDept(1) but I want to see the whole thing.

After I populate the array I am trying to retrieve all records that are in one of the those departments using the following code but I am getting a type mismatch exception where I try to use the global variable — AND SALEDEPT in (’"+str(ServiceDept)+"’) – . If I simply hard code — AND SALEDEPT in (‘40’,‘44’) — everything works fine.

[code] Sub GetInfo()
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 in (’"+str(ServiceDept)+"’) AND SALEBRANCH = ‘" + str(BranchID) + "’ order by WONO"
Dim rs As RecordSet = db.SQLSelect(strSQL)
if rs <> nil then
While Not rs.EOF
lstWO.AddRow(rs.Field(“WoNo”).StringValue, rs.Field(“ShipName”).StringValue, _
rs.Field(“UnitNo”).StringValue, rs.Field(“ShopDateIn”).StringValue, rs.Field(“ShopEstimatedCompletionDate”).StringValue,_
rs.Field(“color”).StringValue,rs.Field(“ServiceVan”).StringValue)
if (rs.Field(“color”).StringValue) = “In Shop” then
lstWO.RowTag(lstWO.LastIndex) = “Green”
elseif (rs.Field(“color”).StringValue) = “Hot Job” then
lstWO.RowTag(lstWO.LastIndex) = “Red”
elseif (rs.Field(“color”).StringValue) = “Complete” then
lstWO.RowTag(lstWO.LastIndex) = “Blue”
elseif (rs.Field(“color”).StringValue) = “Need Parts” then
lstWO.RowTag(lstWO.LastIndex) = “Yellow”
else
lstWO.RowTag(lstWO.LastIndex) = “White”
end if

    rs.MoveNext
  Wend
  rs.Close
end if

end if
End Sub[/code]

Question 2 - Is there an error in the way I am attempting to use the ServiceDept array ?

Thanks as always,

Joe

2

ServiceDept appears to be a string array, so the elements would be strings, not numbers (if that is the question?).

You should use the Join() command to create your IN clause, or loop through the array and format each element individually.

AND SALEDEPT in (’"+join(ServiceDept, “’,’”)+"’)"

which will produce

AND SALEDEPT in (’"+“40’,‘42’,'44”+"’)"

resulting in

AND SALEDEPT in (‘40’,‘42’,‘44’)

3

Thanks Tim.

So if I am understanding your post correctly, when I am writing to the array, technically it does not put commas between the elements, hence the necessity for the join command to insert the commas in my IN statement.

4

That’s right, the array is just a sequence of strings. No quotes, no commas, just the string values.