Hi I am trying to create a button on click action.
Copy2 function is working
Update3 function is not working
They both have almost same code except for the SQL Query
Test_A and Test_B are two tables I am trying to update a field in Test_B from Test_A on the matching criteria of A.ID field which is in both the tables
function Copy2 as c (e as p)
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::ABC_System")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A"
'execute the SQL
flag = cn.execute(sql)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()
end function
function Update3 as c (e as p)
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::ABC_System")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "Update [ABC_System].[dbo].[Test_B] set [Test_B].Phone = (select [Test_A].Phone_Number from [ABC_System].[dbo].[Test_A] where [Test_B].A_ID =[Test_A].A_ID and [Test_B].B_ID > 0)"
'execute the SQL
flag = cn.execute(sql)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()
end function
Copy2 function is working
Update3 function is not working
They both have almost same code except for the SQL Query
Test_A and Test_B are two tables I am trying to update a field in Test_B from Test_A on the matching criteria of A.ID field which is in both the tables
function Copy2 as c (e as p)
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::ABC_System")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "INSERT INTO Test_B(A_ID,First_Name,Last_Name) select Test_A.[A_ID] ,Test_A.[First_Name] ,Test_A.[Last_Name] from Test_A"
'execute the SQL
flag = cn.execute(sql)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()
end function
function Update3 as c (e as p)
dim cn as sql::connection
dim flag as l
flag = cn.open("::Name::ABC_System")
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
end
end if
'turn on portable SQL
cn.PortableSQLEnabled = .t.
dim SQL as c
sql = "Update [ABC_System].[dbo].[Test_B] set [Test_B].Phone = (select [Test_A].Phone_Number from [ABC_System].[dbo].[Test_A] where [Test_B].A_ID =[Test_A].A_ID and [Test_B].B_ID > 0)"
'execute the SQL
flag = cn.execute(sql)
if flag = .f. then
'there was an error
dim errorText as c
errorText = cn.callresult.text
cn.close()
end
end if
dim rowsInserted as n
'get the number of rows that were inserted
rowsInserted = cn.CallResult.rowsaffected
cn.close()
end function
Comment