Alpha Video Training
Results 1 to 6 of 6

Thread: update Query not working?

  1. #1
    Member
    Real Name
    Prashant
    Join Date
    Aug 2011
    Posts
    69

    Default Debugging Query?

    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!!

  2. #2
    Member
    Real Name
    Prashant
    Join Date
    Aug 2011
    Posts
    69

    Default update Query not working?

    hi everyone below is my code in the afterUpdate event.....
    Code:
    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!

  3. #3
    Member
    Real Name
    Jim Coltz
    Join Date
    Oct 2005
    Location
    Western Pennsylvania
    Posts
    597

    Default Re: update Query not working?

    Quote Originally Posted by prashantvarma View Post
    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.

    Code:
    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:

    Code:
    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

  4. #4
    Member
    Real Name
    Prashant
    Join Date
    Aug 2011
    Posts
    69

    Default 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

  5. #5
    Member tekri's Avatar
    Real Name
    Alexander Krišš
    Join Date
    Oct 2010
    Location
    Bratislava, Slovakia
    Posts
    145

    Default Re: Debugging Query?

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

  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,563

    Default Re: Debugging Query?

    Quote Originally Posted by tekri View Post
    Just put debug(1) at the begin of your Xbasic code
    And run the component in the Working Preview tab
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

Similar Threads

  1. Update operation not working
    By carlgiff in forum Alpha Five Version 5
    Replies: 1
    Last Post: 08-22-2009, 01:43 PM
  2. Query not working
    By gaby_h in forum Alpha Five Version 9 - Desktop Applications
    Replies: 4
    Last Post: 03-18-2009, 05:43 PM
  3. dbf field update ... not working .. .why?
    By BEAMER in forum Alpha Five Version 8
    Replies: 4
    Last Post: 12-07-2007, 06:44 PM
  4. Update not working
    By Steve Wood in forum Web Application Server v6
    Replies: 5
    Last Post: 03-01-2005, 07:08 PM
  5. Update Func not working
    By Art Cordaro in forum Alpha Five Version 4
    Replies: 1
    Last Post: 12-30-2004, 08:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •