>>>>>>>Hi,
>>>>>>>
>>>>>>>I am testing a parameterized update command (for SQL Server database) and having a problem. Here is my code:
>>>>>>>

>>>>>>>cSqlUpdate = "update mytable set field1 = ?" + cNewFld1Val + ", field2 = ?" + cNewFld2Val + " where pk = 10"
>>>>>>>lnResult = SQLEXEC( nSqlHandle, cSqlUpdate)
>>>>>>>

>>>>>>>I get a prompt to enter a value (as if I didn't specify the values cNewFld1Val or cNewFld2Val.
>>>>>>>Is this possible because the value of cnewFld2Val has a question mark ("?")?
>>>>>>>
>>>>>>>TIA
>>>>>>

>>>>>>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.
>>>>
>>>>Yes, this is the only case I know of where you need to declare the variables as private.
>>>
>>>I was just surprise that I was not getting an error. And the SQL Server does get updated and, from the cursory look, the values in the SQL table are equal to the Locally declared variables. I trust that you and Naomi know what you are talking about. Just find it strange.
>>
>>As Dragan says, undeclared variables are private by default. I always declare my variables, it's a part of my BeautifyX setup. It has saved my day many times.
>
>I do too. And in this case, as I wrote to Dragan, I have the variables declared LOCAL.

For the SQLEXEC command the variables can be LOCAL:

LOCAL cNewFld1Val, cNewFld2Val
cNewFld1Val = "Value1"
cNewFld2Val = "Value2"
cSqlUpdate = "update mytable set field1 = ?cNewFld1Val , field2 = ?cNewFld2Val where pk = 10"
lnResult = SQLEXEC( nSqlHandle, cSqlUpdate)

However, I advise you to wrap the sqlExec into a class, because that gives you a lot of possibilities to do proper error handling and lots of other needful functionality, including logging, messaging etc. For that purpose then the variables must be declared as Private, because they get handed down to the method:

LOCAL loSQLQuery AS SQLQuery OF DataHandler.vcx
loSQLQuery = NewObject("SQLQuery","DataHandler.vcx")

PRIVATE cNewFld1Val, cNewFld2Val
cNewFld1Val = "Value1"
cNewFld2Val = "Value2"
cSqlUpdate = "update mytable set field1 = ?cNewFld1Val , field2 = ?cNewFld2Val where pk = 10"
loSQLQuery.ExecuteNonQuery(cSqlUpdate)

?loSQLQuery.Success
?loSQLQuery.ErrorNr
?loSQLQuery.ErrorMessage
?loSQLQuery.nSqlHandle

Class SQLQuery:
PROCEDURE ExecuteNonQuery(queryString)
LOCAL lRetVal
lRetVal = SQLEXEC( THIS.nSqlHandle, queryString) > 0
IF !m.lRetVal
    LOCAL ARRAY laError[1]
    AERROR(laError)
    THIS.ErrorNr = laError[1]
    THIS.ErrorMessage = laError[3]
ENDIF
RETURN m.lRetVal
<PRE>