Search

PowerBuilder – Populate a dropdownlistbox from a SQL statement

Posted on Thursday, March 11th, 2010 at 8:20 pm in

Here is a method to populate a dropdownlistbox control with values from a sql statement.  Since it is based on a datastore you can easily apply filters, sorts, etc. prior to populating the dropdownlistbox with values.  The code assumes the first column in the SQL is going to be used to populate the dropdownlistbox .

When you call the function you send the dropdownlistbox and the sql string.

public function integer uf_populate_ddlb (dropdownlistbox ddlb_param, string sql_param);
// populate a listbox with results from a sql statement
// returns number of rows from datastore retrieve
long ll_ddlb_length
long    ll_rc
long    ll_i
string ls_ds_sql
string ls_error
string    ls_val
datastore lds
lds = CREATE datastore
ls_ds_sql = SQLCA.syntaxfromsql( sql_param, 'Style(Type=Form)', ls_error)
IF (Len(ls_error) > 0 ) THEN
   MessageBox ("DataBase Error!", ls_error)
   return sqlca.sqlcode
ELSE
   lds.create(ls_ds_sql, ls_error)
   IF (Len(ls_error) > 0 ) THEN
      MessageBox ("Datastore create Error!", ls_error)
      return -1
   END IF
   lds.settransobject(SQLCA)
   ll_rc = lds.retrieve()
//now if you need to filter or change the rows in the datastore do it here - just remember to update ll_rc (the rowcount) if you changed it.
END IF
SetRedraw (ddlb_param, false)
Reset (ddlb_param)
FOR ll_i = 1 to ll_rc
   // if column 1 in sql is not a string you would need to convert it
   ls_val = lds.getitemstring(ll_i,1)
   Additem (ddlb_param, ls_val)
NEXT
SetRedraw (ddlb_param, true)
/************************************************************
*    size of ddlb is calculated to show the count of the items.
*    note:  117 is the space to show ddlb.text
************************************************************/
if ll_rc < 12 then
   ll_ddlb_length = ll_rc * 63 + 117
   ddlb_param.height = ll_ddlb_length
else
   // size will be set to show 12 items if count is over 12
   ddlb_param.height = 873
end if
DESTROY lds
return ll_rc

end function

Updated March 2021

Tags:

Top