Hello All,
I have 2 sql tables (let's call them TableA & TableB). I'm trying to code an x-basic script to an action button so that a comon field between the 2 tables (PO_Status) will be updated from TableA into TableB based on another common field between the 2 tables (EEID). I've been spinning my wheels over the last couple of days trying to figure this out. I reviewed the following article which was very useful: http://www.downloads.alphasoftware.c...SQLTables.html. I tested the following Update Statements and none of them are working properly (rows affected in result is 0):
"UPDATE TableB SET TableB.PO_Status = TableA.PO_Status WHERE TableB.EEID = TableA.EEID"
"MERGE INTO TableB USING TableA ON TableB.EEID = TableA.EEID WHEN MATCHED THEN UPDATE SET PO_Status = TableA.PO_Status"
"UPDATE TableB SET PO_Status = (SELECT TableA.PO_Status FROM TableA WHERE TableA.EEID = TableB.EEID) WHERE TableB.EEID = (SELECT TableA.EEID FROM TableA.EEID WHERE TableB.EEID = TableA.EEID)"
I have been replacing these in the "Update Statement" of the SqlCommand = Line in the code below to no avail.
function serverside_d88d3bd629fb47e89d34ebb1a18dc54c as c (e as p)
'connect to the SQL database
dim cn as sql::connection
flag = cn.open("::Name::HRBase")
if flag = .f. then
serverside_d88d3bd629fb47e89d34ebb1a18dc54c = "alert('Could not connect to database.');"
exit function
end if
'execute command
dim sqlCommand as c
SqlCommand = "Update Statement"
'show confirmation of rows updated
dim rowsUpdated as n
rowsUpdated=cn.CallResult.rowsAffected
cn.Close()
serverside_d88d3bd629fb47e89d34ebb1a18dc54c = "alert('Post Operation Complete. Records Updated: " +rowsUpdated + ". Refresh browser to see updates.');"
end function
Any Tips? Thanks
I have 2 sql tables (let's call them TableA & TableB). I'm trying to code an x-basic script to an action button so that a comon field between the 2 tables (PO_Status) will be updated from TableA into TableB based on another common field between the 2 tables (EEID). I've been spinning my wheels over the last couple of days trying to figure this out. I reviewed the following article which was very useful: http://www.downloads.alphasoftware.c...SQLTables.html. I tested the following Update Statements and none of them are working properly (rows affected in result is 0):
"UPDATE TableB SET TableB.PO_Status = TableA.PO_Status WHERE TableB.EEID = TableA.EEID"
"MERGE INTO TableB USING TableA ON TableB.EEID = TableA.EEID WHEN MATCHED THEN UPDATE SET PO_Status = TableA.PO_Status"
"UPDATE TableB SET PO_Status = (SELECT TableA.PO_Status FROM TableA WHERE TableA.EEID = TableB.EEID) WHERE TableB.EEID = (SELECT TableA.EEID FROM TableA.EEID WHERE TableB.EEID = TableA.EEID)"
I have been replacing these in the "Update Statement" of the SqlCommand = Line in the code below to no avail.
function serverside_d88d3bd629fb47e89d34ebb1a18dc54c as c (e as p)
'connect to the SQL database
dim cn as sql::connection
flag = cn.open("::Name::HRBase")
if flag = .f. then
serverside_d88d3bd629fb47e89d34ebb1a18dc54c = "alert('Could not connect to database.');"
exit function
end if
'execute command
dim sqlCommand as c
SqlCommand = "Update Statement"
'show confirmation of rows updated
dim rowsUpdated as n
rowsUpdated=cn.CallResult.rowsAffected
cn.Close()
serverside_d88d3bd629fb47e89d34ebb1a18dc54c = "alert('Post Operation Complete. Records Updated: " +rowsUpdated + ". Refresh browser to see updates.');"
end function
Any Tips? Thanks
Comment