Schultz’s PowerBuilder Notes

Dynamic DataWindows


How do I build a DataWindow dynamically?

  1. Build the SQL statement for the DataWindow object as a string and describe he form and presentation style of the DataWindow object
  2. Associate the new DataWindow object with a DataWindow control

SyntaxFromSQL( )

  1. Declare a string variable to hold the SQL statement. The SQL statement must be a complete executible statement. Arguments are not allowed.
  2. Declare a string variable to hold the presentation string. This holds information about the appearance of the DataWindow, such as grid, fonts, units of measure, and color.
  3. Declare a string variable to hold any errors returned and another to hold the returned DataWindow object syntax
  4. Create the DataWindow object syntax using SyntaxFromSQL( ).
String ls_sql_select, ls_present_str, ls_errmsg, ls_syntax_str

ls_sql_select  = “select name from employee where name like ‘%s%’
ls_present_str = “style(type=form)”
ls_syntax_str  = SQLCA.SyntaxFromSQL(ls_sql_select, ls_present_str, ls_errmsg)
IF  ls_syntax_str = “” THEN
   MessageBox(“Error”, “SyntaxFromSQL error”)
END IF

or

ls_dw_syntax = SQLCA.SyntaxFromSQL(ls_sql_syntax, ls_presentation, ls_dw_err)

Any tips on building the presentation string?

Use the DwSyntax utility, or consult the DataWindow reference’s DataWindow Object propeties chapter.

ls_presentation =    &
    "style( type=Grid                          &
            Horizontal_spread = 25             &
            Header_bottom_margin = 15          &
            Header_top_margin = 15 )           &
     datawindow( units=2                       &
             Color= 67108864)              &
     column( Font.Face='MS Sans Serif'         &
         Font.Height=-8                    &
         Font.Weight=700)                  &
         text(   Font.Face='MS Sans Serif' &
         Font.Height=-8                    &
         Font.Weight=700                   &
             Border=6)"

How do you associate DataWindow object syntax with a DataWindow control?

Dw_1.Create(ls_syntax_str, ls_errmsg)
Dw_1.SetTransObject(SQLCA)

How do I dynamically create a DataWindow Control?

OpenUserObject(<userobject variable>, x, y)

Be sure to use CloseUserObject( )

How can I dynamically modify just the Select statement

Finding the current select statement is useful model for creating the new statement.

ls_= ids_notes.Describe(“DataWindow.Table.Select”)

Ls_select = &
   “SELECT QUAD0009.dbo.WORKER_COMMENT.LN_TXT,” + &
   “               QUAD0009.dbo.WORKER_COMMENT.LN_NBR, “ + &
   “               QUAD0009.dbo.WORKER_COMMENT.WRKR_CMT_HIST_ID” + &
   “ FROM    QUAD0009.dbo.WORKER_COMMENT” + &
   “               QUAD0009.dbo.WORKER_CMT_HIST,” + &
   “               QUAD0009.dbo.WORKER, ” + &
   “               QUAD00222.dboEMPLOYEE” + &
   “ WHERE QUAD0009.dbo.WORKER.WRKR_ID = “ +  String(ll_wrkr_id) + “and ” + &.
   “               QUAD0009.dbo.WORKER.WRKR_ID = AUAD0009.dbo.WRKER_CNMT_HIST.WRKR_ID) and “ + &
   “               QUAD0009.dbo.WORKER.EMP_ID     = QUAD0022.dbo.EMPLOYEE.EMPLOYEE_ID and “ + &
   “               QUAD0009.dbo.WORKER_CMT_HIST.WRKR_CMT_HIST_ID =
                   QUAD0009.dbo.WORKER_COMMENT.WRKR_CMT_HIST_ID)”

ls_mod = “DataWIndow.Table.Select=” + ls_select + ‘”’
ls_rc = lds_notes.Modify(ls_modify)

About these ads

May 26, 2008 - Posted by | 2. Datawindows | , , , , , , , , , , ,

5 Comments »

  1. sorry but it doesn’t work

    Comment by Flavio Silva | February 14, 2011 | Reply

  2. This is really helpful, Thanks!

    Comment by Arjun Varma | January 19, 2012 | Reply

  3. The presentation string example worked great but how do you edit and update data in a datawindow that was created dynamically using SYNTAXFROMSQL

    Comment by Robert McHodgkins | August 6, 2012 | Reply

  4. Just follow the code below,
    as_TableName variable is the table you want to update..

    // 2. set each column as updateable
    li_colcount = Integer(This.Object.DataWindow.Column.Count)
    FOR li_col = 1 TO li_colcount
    This.Modify(This.Describe( “#”+string(li_Col)+”.Name”) + ‘.Update=Yes’)
    //ids_data.Modify(lds_TabTo.Describe(“#”+string(li_Col)+”.Name”) + ‘.Key=Yes’)
    NEXT
    //set the key for the update
    This.Modify(This.Describe( “#”+string(1)+”.Name”) + ‘.Key=Yes’)
    //This.Modify(This.Describe( “#”+string(2)+”.Name”) + ‘.Key=Yes’)
    //3. set table name and set as updateable and set parameters for default where
    DWObject ldw
    ldw = This.Object.DataWindow
    ldw.Table.UpdateTable = as_TableName
    ldw.Table.UpdateWhere = 0 // key columns only in where
    ldw.Table.UpdateKeyInPlace = ‘yes’ // key changes treated as Updates

    Comment by Stanley | March 18, 2014 | Reply

  5. […] Dynamic DataWindows « Schultz’s PowerBuilder Notes – May 26, 2008 · How do I build a DataWindow dynamically? Build the SQL statement for the DataWindow object as a string and describe he form and presentation style of the …… […]

    Pingback by Fix Powerbuilder Error Windows XP, Vista, 7, 8 [Solved] | December 1, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: