update Query not working?

[prashantvarma]

Hi,
I was trying some code in the afterUpdate Event of the grid.
But i am not getting the desired result.
can anyone tell me how to use a debugger in the afterUpdate Event of the Grid
Please help!!

[prashantvarma]

update Query not working?

hi everyone below is my code in the afterUpdate event.....

Dim cn as sql::connection
Dim sqlCommand as c
DIM args as sql::arguments
dim rs as SQL::ResultSet
dim count as n
dim flagResult as l 

cn.open("::Name::Intranet")

cn.PortableSQLEnabled = .t.


sqlCommand = <<%longstring%
select count(imCustomer.nUnitIDFK) as Count from imCustomer imCustomer 
INNER JOIN imOperationalMIS
ON imCustomer.nCustomerID = imOperationalMIS.nCustomerIDFK
where imOperationalMIS.nOperationalMISID = :ID

%longstring%

args.add("ID", convert_type(e.OldDataSubmitted.nOperationalMISID,"N"))

cn.BeginTransaction()

cn.Execute(sqlCommand, args)
rs = cn.ResultSet
count = convert_type(rs.data("Count"),"N")

cn.CommitTransaction()


if count <> 0 then

sqlCommand = <<%txt% 
UPDATE imOperationalMIS SET imOperationalMIS.nUnitIDFK = imCustomer.nUnitIDFK FROM imOperationalMIS INNER JOIN
imCustomer ON imOperationalMIS.nCustomerIDFK = imCustomer.nCustomerID where imOperationalMIS.nOperationalMISID = :ID
%txt%

args.set("ID", convert_type(e.OldDataSubmitted.nOperationalMISID,"N"))
cn.BeginTransaction()

cn.Execute(sqlCommand, args)
dim txt as c


txt = "alert('" + "count <> 0 works!!" + "');"
aa = e.rtc.A_AjaxResponses
aa[].text = txt


cn.CommitTransaction()

end if
 	
cn.Close()

the select query in the above code works fine..........also the alert box pops up nicely but my table is not getting updated
i used a sql profiler and according to it update query is not getting fired...
the query is fine but i dint know why it wont work
if anyone can help?
Do reply!

[Jim Coltz]

Re: update Query not working?

sqlCommand = <<%txt%
UPDATE imOperationalMIS SET imOperationalMIS.nUnitIDFK = imCustomer.nUnitIDFK FROM imOperationalMIS INNER JOIN
imCustomer ON imOperationalMIS.nCustomerIDFK = imCustomer.nCustomerID where imOperationalMIS.nOperationalMISID = :ID
%txt%

I think you're missing a select in your update clause. I also added an alias to keep from getting a circular reference error.

sqlCommand = <<%txt% 
UPDATE imOperationalMIS SET imOperationalMIS.nUnitIDFK = (Select imCustomer.nUnitIDFK FROM imOperationalMIS IMO INNER JOIN
imCustomer ON IMO.nCustomerIDFK = imCustomer.nCustomerID where IMO.nOperationalMISID = :ID)
where imOperationalMIS.nOperationalMISID = :ID

Also, if I'm correctly understanding what your trying to do, you can shorten up your code and save a trip to server by using something like this:

Dim cn as sql::connection
Dim sqlCommand as c
DIM args as sql::arguments
dim rs as SQL::ResultSet
dim count as n
dim flagResult as l 

cn.open("::Name::Intranet")

cn.PortableSQLEnabled = .t.

sqlCommand = <<%longstring%
select count(imCustomer.nUnitIDFK) as Count from imCustomer imCustomer 
INNER JOIN imOperationalMIS
ON imCustomer.nCustomerID = imOperationalMIS.nCustomerIDFK
where imOperationalMIS.nOperationalMISID = :ID
%longstring%

args.add("ID", convert_type(e.OldDataSubmitted.nOperationalMISID,"N"))

cn.Execute(sqlCommand, args)
'you should add error trapping here
rs = cn.ResultSet

if rs.nextrow() then 'will only return true if the result set has a row in it. 
'if the row does exist, then we can just use the value in the column(s) we need to do the update.
'Since :ID is already defined as an argument above, we'll just reuse it.
sqlCommand = "UPDATE imOperationalMIS SET imOperationalMIS.nUnitIDFK ="+ rs.data("nUnitIDFk") +"where nOperationalMISID = :ID ;"	
cn.BeginTransaction()
cn.Execute(sqlCommand)
'you should add error trapping here.
cn.CommitTransaction()
end if

cn.FreeResult() 'good idea to get in the habit of using this 	
cn.Close()

txt = "alert('" + "count <> 0 works!!" + "');"
aa = e.rtc.A_AjaxResponses
aa[].text = txt

[prashantvarma]

Re: update Query not working?

Hi thanks for the reply....but it is still not picking up the update query....what i am done now is that i have enclosed my update query(the same Query used earliar) in a stored procedure and called the stored procedure int the after update event and it works fine.....but still i would like to know why it was not picking my update query.... please do reply...
thanks

[tekri]

Re: Debugging Query?

Just put debug(1) at the begin of your Xbasic code

[Al Buchholz]

Re: Debugging Query?

Just put debug(1) at the begin of your Xbasic code

And run the component in the Working Preview tab