Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Parametrized update command
Message
 
 
To
03/04/2019 01:14:06
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
01667828
Message ID:
01667874
Views:
34
>>>>>>>>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>
>
>
Thank you for the code.
"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
Previous
Reply
Map
View

Click here to load this message in the networking platform