I'm embarrassed to say how many hours I've worked on this and variations of it and I'm no further in understanding why it's not working. The first code example works fine if I hardcode the CapSat_Main_Tbl.UID)=97, but it fails if I put in CapSat_Main_Tbl.UID)=:LastID
Another thing, when I hardcode the number, it does add the records to the table but when stepping through the code it hits the line below highlighted. I'm not sure why it adds them anyway if supposedly there is an error.
flag = cn.Execute(sqlInsertStatement)
if flag = .f. then
ui_msg_box("Error","Record was not inserted. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL)
else
if cn.AffectedRows() = 1 then
ui_msg_box("Notice","Record was created.",UI_INFORMATION_SYMBOL)
else
ui_msg_box("Error","Record was not inserted." ,UI_STOP_SYMBOL)
end if
end if
Can somebody see what's wrong with the code below?
Thanks
-John
dim args as sql::arguments
dim sqlInsertStatement as c
args.add("LastID","UID")
sqlInsertStatement = <<%txt%
INSERT INTO CapSat_Scores_Horiz ( YR, CapSat_Main_ID_fk, Component, Element, Element_ID_fk, PC_ID_fk, Program_ID_fk, Partner_ID_fk )
SELECT CapSat_Main_Tbl.YR, CapSat_Main_Tbl.UID, CapSat_Components_Tbl.Component_Name, CapSat_Elements_Tbl.Element, CapSat_Elements_Tbl.UID_Element, CapSat_Main_Tbl.PC_ID_fk, CapSat_Main_Tbl.PGM_ID_fk, CapSat_Main_Tbl.Partner_ID_fk
FROM CapSat_Main_Tbl, CapSat_Elements_Tbl INNER JOIN CapSat_Components_Tbl ON CapSat_Elements_Tbl.Component_ID_fk = CapSat_Components_Tbl.UID_Component
WHERE (((CapSat_Components_Tbl.ShowThis)=True) AND ((CapSat_Elements_Tbl.ShowThis)=True) AND ((CapSat_Main_Tbl.UID)=97))
%txt%
Another thing, when I hardcode the number, it does add the records to the table but when stepping through the code it hits the line below highlighted. I'm not sure why it adds them anyway if supposedly there is an error.
flag = cn.Execute(sqlInsertStatement)
if flag = .f. then
ui_msg_box("Error","Record was not inserted. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL)
else
if cn.AffectedRows() = 1 then
ui_msg_box("Notice","Record was created.",UI_INFORMATION_SYMBOL)
else
ui_msg_box("Error","Record was not inserted." ,UI_STOP_SYMBOL)
end if
end if
Can somebody see what's wrong with the code below?
Thanks
-John
dim args as sql::arguments
dim sqlInsertStatement as c
args.add("LastID","UID")
sqlInsertStatement = <<%txt%
INSERT INTO CapSat_Scores_Horiz ( YR, CapSat_Main_ID_fk, Component, Element, Element_ID_fk, PC_ID_fk, Program_ID_fk, Partner_ID_fk )
SELECT CapSat_Main_Tbl.YR, CapSat_Main_Tbl.UID, CapSat_Components_Tbl.Component_Name, CapSat_Elements_Tbl.Element, CapSat_Elements_Tbl.UID_Element, CapSat_Main_Tbl.PC_ID_fk, CapSat_Main_Tbl.PGM_ID_fk, CapSat_Main_Tbl.Partner_ID_fk
FROM CapSat_Main_Tbl, CapSat_Elements_Tbl INNER JOIN CapSat_Components_Tbl ON CapSat_Elements_Tbl.Component_ID_fk = CapSat_Components_Tbl.UID_Component
WHERE (((CapSat_Components_Tbl.ShowThis)=True) AND ((CapSat_Elements_Tbl.ShowThis)=True) AND ((CapSat_Main_Tbl.UID)=97))
%txt%
Comment