Mover Class Help

[crewchiefpro6]

I am trying to construct an SQL command from a mover list and wonder what the problem is.

The mover list is populated fine, and I can see the results in a messagebox, however, when I try to construct a usable SQL command from the list it fails to run.

Here is the code I am using (Click Event From Button):

LOCAL nCnt, nTotalListCount, lcAnswer

nTotalListCount = thisform._supermover1.lstRight.ListCount
lcAnswer = ""

FOR nCnt = 1 to nTotalListCount
lcAnswer = IIF(EMPTY(lcAnswer),ALLTRIM(thisform._supermover1.lstRight.List(nCnt)) + ;
IIF(nCnt < nTotalListCount," .or. session = ", ""), ;
lcAnswer + ALLTRIM(thisform._supermover1.lstRight.List(nCnt)))
ENDFOR
SET STEP on


SELECT * from icard ;
where session = lcAnswer ;
into cursor csrTestMover


Don Higgins

http://www.crewchiefpro.com

 

[crewchiefpro6]

Forgot to include this question, What is the best way to construct a where clause from a mover list so I can use it in an SQL command?



Don Higgins

http://www.crewchiefpro.com

 

[crewchiefpro6]

Found a working answer:

FOR nCnt = 1 TO nTotalListCount

IF EMPTY(lcAnswer)
lcAnswer = "'"+ALLTRIM(THISFORM._supermover1.lstRight.LIST(nCnt)) + "'" + " .or. session = "
ELSE
lcAnswer = lcAnswer + IIF(nCnt = 2,""," .or. session = ") + ;
"'" + ALLTRIM(THISFORM._supermover1.lstRight.LIST(nCnt)) + "'"
ENDIF

ENDFOR



SELECT * FROM icard ;
WHERE SESSION = &lcAnswer ;
INTO CURSOR csrTestMover
BROWSE LAST NOCAPTION


Don Higgins

http://www.crewchiefpro.com

 

[alvechurchdata]

Seeing as you posed and answered your own question in such a fast and efficient manner I suggest you award yourself a star.

I forget how many "OR"s can be concatenated together. Two other approaches would be to build a Where clause like:

[TT]Where session $ ("This, That, Another")[/TT]

or

[TT]Where Session IN ("This", "That", "Another")[/TT]

The "OR" approach should be faster though.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[crewchiefpro6]

I had to modify the code, especially if the user selected only one record, so now it looks like this. I am quite sure I need to modify it more but right now it seems to work.

FOR nCnt = 1 TO nTotalListCount

IF EMPTY(lcAnswer)
lcAnswer = "'"+ALLTRIM(THISFORM._supermover1.lstRight.LIST(nCnt)) + "'" + IIF(nTotalListCount = 1,""," .or. session = ")
ELSE
lcAnswer = lcAnswer + IIF(nCnt = 2,""," .or. session = ") + ;
"'" + ALLTRIM(THISFORM._supermover1.lstRight.LIST(nCnt)) + "'"
ENDIF

ENDFOR

***

I guess I will have to limit the number of choices to 5 which should be fine for my purposes.



Don Higgins

http://www.crewchiefpro.com