• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Make Simple SQL Dropdown Without DataWindow Like C# ?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Make Simple SQL Dropdown Without DataWindow Like C# ?

  1. How-to
0
Votes
Undo
  1. Marc James
    Marc James
  2. PowerBuilder
  3. Friday, 31 January 2020 11:51 AM UTC

Can you make a simple drop down menu using data from an SQL source without having to make multiple datawindows and retrieving them all the time?

Below is how you would do it with a few lines of code in C#, can it be done quickly like this in PB?

            using (ODBC.connect = new OdbcConnection(ODBC.connectionString))
            {
                try
                {
                    ODBC.connect.Open();
                    OdbcCommand command = new OdbcCommand("SELECT MyGroup FROM UserGroups", ODBC.connect);
                    OdbcDataReader dataReader = command.ExecuteReader();

                    while (dataReader.Read())
                    {
                        combobox_UserGroup.Items.Add(dataReader[0]);
                    }
                }
                catch (OdbcException exception)
                {
                    MessageBox.Show(exception.Message);
                }

 

PB 2017 license switch to trial and does not go ba...
Powerscript vs C#
Responses (3)
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 31 January 2020 15:09 PM UTC
  2. PowerBuilder
  3. # 1

you can use syntaxfromsql to create dw syntax at run time using sql.   then ds.create to create it from the syntax.

sqlca.SyntaxFromSQL( ls_sql, ls_presentation, ls_error)

ds_1.Create( ls_syntax , ls_error)
ds_1.settransobject( sqlca)

ds_1.retrieve()

 

 

but you can not assign that created syntax as a drop down datawindow (datawindowchild) - in powerbuilder.   

you can in powerserver however: dwchild.dynamic create( syntax, error)

this should really be added to powerbuilder.

 

anyway, you can use the dynamically created datastore to retrieve the data and then add those values as drop down list box values to either a ddlb column of datawindow, or just  a drop down control, or whatever.

so, yes you can do all this via script without have an already created set of datawindows.  

Comment
Michael Kramer
  1. Michael Kramer
  2. Friday, 31 January 2020 17:29 PM UTC
You can obtain syntax of DW object using dw.Describe("DataWindow.Syntax"). That source you can LibraryImport to a PBL/PBD in your app's library list. Now it has same status as any DW object in any PBD - so it can be referenced as DDDW inside another DW object.

If your SQL statements provide same resultset structure (same columns but different set of rows) you may simply replace the SELECT statement of an existing DataWindow. DDDW can be secondary DW to a DataStore via ShareData so even things you can't do directly on DDDW you can still provide albeit a little more code.

  1. Helpful
There are no comments made yet.
Kevin Ridley
Kevin Ridley Accepted Answer Pending Moderation
  1. Friday, 31 January 2020 13:28 PM UTC
  2. PowerBuilder
  3. # 2

You can do like Luiz says below with a Cursor and Fetch, but in PowerBuilder you'd use a DropDownListBox.

 

KR

Comment
There are no comments made yet.
Luiz Ribeiro
Luiz Ribeiro Accepted Answer Pending Moderation
  1. Friday, 31 January 2020 12:54 PM UTC
  2. PowerBuilder
  3. # 3

Hi Marc.

 

Yes, it is possible. Try this:

 

DECLARE lcur_myGroups DYNAMIC CURSOR FOR SQLSA;
ls_query = "SELECT MyGroup FROM UserGroups"
PREPARE SQLSA FROM :ls_query;
OPEN DYNAMIC lcur_myGroups;

combobox_UserGroup.Reset()

FETCH lcur_myGroups INTO :ls_group;
DO WHILE SQLCA.SQLCODE = 0
  combobox_UserGroup.AddItem(ls_group);
  FETCH lcur_myGroups INTO :ls_group;
LOOP
CLOSE lcur_myGroups;

combobox_UserGroup.SelectItem(1)

Comment
Marc James
  1. Marc James
  2. Friday, 31 January 2020 15:21 PM UTC
Thanks, not sure why the combobox_UserGroup.Reset() is required though? mine works without this
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.