ibbm


I am trying to use this statement

thisform.lstSearchList.RowSource = "select branch,clientcode,alltrim(lastname)+ '", "' + firstname,ffid from &lcf where upper(lastname) = alltrim(upper(lcCheckValue)) order by lastname, firstname,branch,clientcode into cursor temp"

but it doesn't like the + ", " obviously as it's already enclosed in a string. Can I do this somehow I thought you enclosed it in either double quotes or single quotes but it doesn't like either

Thanks in advance!




Re: how to enclose quotation marks in a string...

Markish


Hi,

This line should be something like

Code Snippet

thisform.lstSearchList.RowSource = "select branch,clientcode,alltrim(lastname)+ "+"', '" +" firstname,ffid from &lcf where upper(lastname) = alltrim(upper(lcCheckValue)) order by lastname, firstname,branch,clientcode into cursor temp"

Rowsource is a simple string. I hope it solves your issue.

Best regards,

Markish







Re: how to enclose quotation marks in a string...

surinder singh

Hi Markish,

I think you have missed one + sign in your expression.

thisform.lstSearchList.RowSource = "select branch,clientcode,alltrim(lastname)+ "+"', ' + " +" firstname,ffid from &lcf where upper(lastname) = alltrim(upper(lcCheckValue)) order by lastname, firstname,branch,clientcode into cursor temp"

With Warm Regards

Surinder Singh







Re: how to enclose quotation marks in a string...

Markish

Hi,

It's a typo......I missed out. Thanks anyhow...

Best regards,

Markish






Re: how to enclose quotation marks in a string...

CetinBasoz

Ibbm,

Sorry to be sounding as lecturing but at least might help to someone else.

Double quotes, quotes and square brackets can be interchangebly used in a VFP string literal (with one exception - within square brackets any #define constants are evaluated). So this is a legal string:

"Hello there, [John]. Press 'F2' to continue."

[Hello there, "John". Press 'F2' to continue.]

'Hello there, [John]. Press "F2" to continue.'

and would mean:

Hello there, [John]. Press 'F2' to continue.

Hello there, "John". Press 'F2' to continue.

Hello there, [John]. Press "F2" to continue.

respectively (considering no #define is in effect - square brackets case).

Your SQL string could therefore be written as:

Code Snippet
"select branch,clientcode,alltrim(lastname)+ ', ' + firstname,ffid from &lcf where upper(lastname) = alltrim(upper(lcCheckValue)) order by lastname, firstname,branch,clientcode into cursor temp"

But your SQL string's problem is not just with the quotes:

1) String literals cannot be over 255 in length. If you need such a string instead of calculating size while coding write the string as a series of concatanated strings.

2) alltrim(lastname) +', ' + firstname

Is dangerous and unnecessary in a SQL as column definition. If first record has "Li" as last name and firstname field size is 10, then in your resulting SQL fieldsize for that column would be 2 + 2 + 10 = 14. That may be cutting out longer lastname+firstname for example "Buchanan, Stev" when it should be "Buchanan, Steven". You can also experience "View definition has changed ..." error if you use such an SQL for view definition. Correct way is to have a size len(lastName)+len(firstName) + len(', '). You can easily do that if you use the other concatenation operator (-). So your string becomes:

Code Snippet

"select branch,clientcode,lastname -(', ' + firstname),ffid"+;

" from &lcf"+;

" where upper(lastname) = alltrim(upper(lcCheckValue))"+;

" order by lastname, firstname,branch,clientcode"+;

" into cursor temp"

3) from &lcf

in the string. Has 2 problems.

First, it's a cursor/table/on disk file "name". Think twice before using & and use only where it's better to use (there are few places where it's better like fieldname list part, where etc parts of the SQL). Here since you're trying to use it for a "name" use a name expression instead (that's remove the & and surround the variable with parentheses).

Second, when this string is evaluated (list needs to fill items, querying or requerying the rowsource), variable lcf should be in scope. Same applies to lcCheckValue as well.

And lastly apply the rule memory variables should be prefixed with mdot (m.).

So your string becomes

(if you didn't mean to have those variables later for requery too but just there during init):

Code Snippet

"select branch,clientcode,lastname -(', ' + firstname),ffid"+;

" from ('"+m.lcf+"')"+;

" where upper(lastname) == upper('"+m.lcCheckValue-"')"+;

" order by lastname, firstname,branch,clientcode"+;

" into cursor temp"

(if you meant to have them for a requery too where you'd supply values):

Code Snippet

"select branch,clientcode,lastname -(', ' + firstname),ffid"+;

" from (m.lcf)"+;

" where upper(lastname) == upper(m.lcCheckValue)"+;

" order by lastname, firstname,branch,clientcode"+;

" into cursor temp"

Also note that I removed alltrim() in where and used == there thinking you meant to do an exact match on lastName.

Finally, using text ... [textmerge] ... endtext you can create more readable code. Something like (depending on your intention with those variables):

Code Snippet

TEXT TO thisform.lstSearchList.RowSource TEXTMERGE PRETEXT 15

select branch,clientcode,lastname -(', ' + firstname),ffid

from ('<<TRIM(m.lcf)>>')

where upper(lastname) == '<<upper(TRIM(m.lcCheckValue))>>'

order by lastname, firstname,branch,clientcode

into cursor temp

endtext





Re: how to enclose quotation marks in a string...

Fox Me Up

two ways to represent a string
MyString = "hello world"
MyString = 'hello world'

if you want to put quations in a string then all you have to do is alternate them. for example

MyMacro = "select * from ff_tables where ItemName = 'daizy flowers' into cursor c1"

simply as that.





Re: how to enclose quotation marks in a string...

ibbm

thanks for all the advise. I will dig further into what've you added.

What's the difference between mdot variables and not mdot variables I used to use m. variables but it seemed to me that all the samples I see don't have the m. in front of them.

I used the alltrim(lastname) as I wanted the screen to start listing the lastnames on keypress. It's not an exact match but it brings the lastname closer to what's being typed.

I realized my string length was a problem but by concatenating all those strings don't they just become one long string = the same amount of characters

when would you use "&"

thanks for the caution for the lastname + firstname. I have not experienced any cutoff. I have gotten the full lastname and firstname no matter what size they are. ie first listed is GABLE, second is GABRIOLA HARVARD etch.

I really appreciate the lessons!





Re: how to enclose quotation marks in a string...

ibbm

Now that's KISS! Thanks everyone. THis worked like a charm!