Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


Breck Carter
Last modified: January 1, 1997
mail to: bcarter@bcarter.com
[Home]



Tip 69: Eschewing Annoyance

(Yes, this really is "Tip Number 69" even though the previous one was called "Number 57". That's because over the past few months 11 tips were put in files that didn't follow the "tipxxx.htm" naming convention. Since the very first file was one of the misnamed, everything's been screwed up since Day One: Even tip001.htm is really Number 2. Maybe all the old files will get renamed someday, maybe they won't.
But starting today new ones are gonna be numbered correctly.)


How do I get rid of the "Specify Retrieval Arguments" dialog box for a DropDownDataWindow that uses a retrieval argument? The script has all the necessary code to retrieve the DDDW before the main DataWindow is retrieved.

The Powersoft Faxline #2307 has the breathtaking title "Suppressing Retrieval Arguments on Datawindows Which Include Drop Down DataWindows While Performing An InsertRow Or Retrieve." In that document you can read all about calling GetChild() and Retrieve() ahead of time for DDDWs that use retrieval arguments. That logic usually gets rid of the dialog box shown in Figure 1, but sometimes not.

Figure 1: The Annoying Dialog Box


The "sometimes not" happens when the DDDW still contains no rows when the main DataWindow is retrieved. In other words, if the DDDW is empty, even if it's supposed to be empty, the annoying dialog box of Figure 1 will appear. This situation can be really confusing, especially if there are several DDDWs with retrieval arguments or if the main DataWindow also has retrieval arguments: It's hard to tell which DataWindow the dialog box applies to, and it's easy to spend a long time debugging the wrong code.

The ugly Brute Force solution works like this:

  • Call GetChild and retrieve the DDDW.
  • Check if the DDDW is still empty.
  • If the DDDW is empty, add a dummy row with InsertRow.
  • Retrieve the main DataWindow.
  • If the DDDW was empty, get rid of the dummy row.

Figure 2 shows the PowerScript version complete with error checking (which was what the REAL Tip Number One was all about.)

Figure 2: The cb_Retrieve.clicked Script
 integer         li_RC
 long            ll_row_count
 DataWindowChild dwc_table2
 boolean         lb_dwc_table2_is_empty
 long            ll_row

 // Get addressability to the DDDW.

 li_RC = parent.dw_table1_a.GetChild ( "column1", dwc_table2 )
 if li_RC <> 1 then
    MessageBox ( "Error", "GetChild failed" )
    halt close
 end if

 // Fill the DDDW.

 li_RC = dwc_table2.SetTransObject ( SQLCA )
 if li_RC <> 1 then
    MessageBox ( "Error", "SetTransObject dwc_table2 failed" )
    halt close
 end if

 ll_row_count = dwc_table2.retrieve ( sle_retrieval_argument.text )
 if ll_row_count < 0 then
    MessageBox ( "Error", "Retrieve dwc_table2 failed" )
    halt close
 end if

 // Check to see if the DDDW is still empty.

 if dwc_table2.RowCount() = 0 then
    lb_dwc_table2_is_empty = true
 else
    lb_dwc_table2_is_empty = false
 end if

 // Temporarily put something in the DDDW, if necessary
 // to prevent a "Specify Retrieval Arguments" dialog box
 // from appearing when the main DW is retrieved.

 if lb_dwc_table2_is_empty then
    ll_row = dwc_table2.InsertRow ( 0 )
    if ll_row <> 1 then
       MessageBox ( "Error", "InsertRow dwc_table2 failed" )
       halt close
    end if
 end if

 // Load the main DataWindow.

 li_RC = parent.dw_table1_a.SetTransObject ( SQLCA )
 if li_RC <> 1 then
    MessageBox ( "Error", "SetTransObject dw_table1_a failed" )
    halt close
 end if

 ll_row_count = parent.dw_table1_a.retrieve()
 if ll_row_count < 0 then
    MessageBox ( "Error", "Retrieve dw_table1_a failed" )
    halt close
 end if

 // Set the DDDW back to empty again, if necessary.

 if lb_dwc_table2_is_empty then
    li_RC = dwc_table2.reset();
    if li_RC <> 1 then
       MessageBox ( "Error", "Reset dwc_table2 failed" )
       halt close
    end if
 end if

The same annoying dialog box will appear if you call InsertRow() on the main DataWindow instead of Retrieve() (if SetTransObject() was called first for the main DataWindow, that is.)

Figure 3 shows how the same technique can be used to solve the InsertRow problem.

Figure 3: The cb_InsertRow.clicked Script
 integer         li_RC
 DataWindowChild dwc_table2
 boolean         lb_dwc_table2_is_empty
 long            ll_row

 // Get addressability to the DDDW.

 li_RC = parent.dw_table1_a.GetChild ( "column1", dwc_table2 )
 if li_RC <> 1 then
    MessageBox ( "Error", "GetChild failed" )
    halt close
 end if

 // Check to see if the DDDW is empty.

 if dwc_table2.RowCount() = 0 then
    lb_dwc_table2_is_empty = true
 else
    lb_dwc_table2_is_empty = false
 end if

 // Temporarily put something in the DDDW, if necessary
 // to prevent a "Specify Retrieval Arguments" dialog box
 // from appearing when InsertRow is called for the main DW.

 if lb_dwc_table2_is_empty then
    ll_row = dwc_table2.InsertRow ( 0 )
    if ll_row <> 1 then
       MessageBox ( "Error", "InsertRow dwc_table2 failed" )
       halt close
    end if
 end if

 // Insert a new row into the main DW.

 li_RC = parent.dw_table1_a.SetTransObject ( SQLCA )
 if li_RC <> 1 then
    MessageBox ( "Error", "SetTransObject dw_table1_a failed" )
    halt close
 end if

 ll_row = parent.dw_table1_a.InsertRow ( 0 )
 if ll_row < 1 then
    MessageBox ( "Error", "InsertRow dw_table1_a failed" )
    halt close
 end if

 // Set the DDDW back to empty again, if necessary.

 if lb_dwc_table2_is_empty then
    li_RC = dwc_table2.reset();
    if li_RC <> 1 then
       MessageBox ( "Error", "Reset dwc_table2 failed" )
       halt close
    end if
 end if


Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.