>>>
>>>cSqlUpdate = "update mytable set field1 = ?cNewFld1Val , field2 = ?cNewFld2Val where pk = 10"
>>>lnResult = SQLEXEC( nSqlHandle, cSqlUpdate)
>>
>>Do the variables cNewFld1Val and cNewFld2Val have to be Private? I have them Local and they are in scope with the above command. But I am not getting results I expect. So, I thought maybe this is the problem.
>>Please let me know. Thanks.
>
>Dmitry,
>
>I'll step in to reinforce what Dragan said, the variables you use must be in scope, being irrelevant if they are Local, Private, or Public. But I also would like to emphasize that a parameter does not have to be a variable. In fact, you should see it as an expression, and the value of an expression in VFP can be highly dependent on the context.
>
>For instance, in your case I would prefer to prefix the variable names with m-dot, to be sure the parameter value comes from a variable, and not from a field in the currently open work area.
>
>Consider this:
>
>
>CREATE CURSOR TestCursor (TestField Integer)
>
>INSERT INTO TestCursor VALUES (10)
>
>LOCAL TestField AS Integer
>
>m.TestField = 15
>
>SQLEXEC(m.ConnectionHandle, ;
> "SELECT * FROM RemoteTable WHERE RemoteColumn BETWEEN ?TestField AND ?m.TestField;", ;
> "Result")
>
>
>What is passed to the remote server is the SQL statement
>
>SELECT * FROM RemoteTable WHERE RemoteColumn BETWEEN 10 AND 15;
>
>
>Of course, I'm not saying or suggesting that this explains the results you're getting, but hopefully it will make a bit more clear how you should evaluate the parameter, that is, the expression that follows ? until it finds the end of a valid expression.
>
>In the next statement, INT(m.TestField * (1 + RAND())) is at first evaluated by VFP and only then passed to the ODBC driver as a parameter, and sent to the server.
>
>
>SQLEXEC(m.ConnectionHandle, ;
> "SELECT * FROM RemoteTable WHERE RemoteColumn BETWEEN ?TestField AND ?INT(m.TestField * (1 + RAND()));", ;
> "Result")
>
Hi Antonio,
Thank you for clarifying and for the sample code. The reason I never use a prefix m. before a variable is because I am always careful in naming my variables different from fields or expression. But I agree, it does not hurt.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham