I'me sure the answer is on the forum somewhere, but I haven't been able to find it.
I'm using version 10 and running against SQL Server tables.
I'm trying to update a record in one table based on the selections in a multi-select dropdown box in a different table. I've gotten everything to work partially, but I can't seem to get it to work like I need it.
The multi-select dropdown has several options, but if "FF" is selected, it should update the "LastFFMV" field in the other table; if "HV" is selected, it should update the "LastHV" field in the other table; if both are selected (saving "FF,HV"), it should update both fields in the other table. This is true for a couple of other dropdown options as well. I was trying to use the $ operator, but maybe I'm off on that. I've also tried with various [1] arrangements simply because I don't fully understand some of the underlying concepts...
I'm a newbie, so I can promise there's better and cleaner ways to code this, but I just need it to get it to work. Thanks in advance.
I'm using version 10 and running against SQL Server tables.
I'm trying to update a record in one table based on the selections in a multi-select dropdown box in a different table. I've gotten everything to work partially, but I can't seem to get it to work like I need it.
The multi-select dropdown has several options, but if "FF" is selected, it should update the "LastFFMV" field in the other table; if "HV" is selected, it should update the "LastHV" field in the other table; if both are selected (saving "FF,HV"), it should update both fields in the other table. This is true for a couple of other dropdown options as well. I was trying to use the $ operator, but maybe I'm off on that. I've also tried with various [1] arrangements simply because I don't fully understand some of the underlying concepts...
I'm a newbie, so I can promise there's better and cleaner ways to code this, but I just need it to get it to work. Thanks in advance.
Code:
DIM cn as SQL::Connection dim flagResult as l flagResult = cn.open("::Name::SQL Server Express") 'Specify that we are using Portable SQL syntax cn.PortableSQLEnabled = .t. 'Dim a SQL arguments object, create arguments and set their values DIM args as sql::arguments args.add("Ind_ID",e.DataSubmitted.AMC_Ind_ID) args.add("LastFFMV",e.DataSubmitted.AMC_DATE) args.add("LastHV",e.DataSubmitted.AMC_DATE) if "FF" $ e.DataSubmitted.AMC_CODE[1] then dim sqlUpdateStatement as c sqlUpdateStatement = <<%txt% UPDATE [individuals 2] SET LastFFMV = :LastFFMV WHERE (Ind_ID = :Ind_ID) %txt% end if if "HV" $ e.DataSubmitted.AMC_CODE[1] then dim sqlUpdateStatement as c sqlUpdateStatement = <<%txt% UPDATE [individuals 2] SET LastHV = :LastHV WHERE (Ind_ID = :Ind_ID) %txt% end if 'Now, close the connection cn.close() end function