Populate list box with records from database

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Lazy-legs
Posts: 71
Joined: Mon Oct 08, 2007 1:33 am
Location: Århus-Berlin

Populate list box with records from database

Post by Lazy-legs »

Hello,

Here is a simple macro that populates the ListBox1 in the Dialog1 dialog window with words from the "Word" column in the "wordlist" table in the BaseDB database.

Code: Select all

Sub PopulateListBox()

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")

 If not DBContext.hasByName("WriterDB") then
   MsgBox (ConnectionFailedMessage, , "Connection failed!") : End
 End If

DataSource=DBContext.getByName("BaseDB")
ConnectToDB=DataSource.GetConnection ("","")

SQLResult=createUnoService("com.sun.star.sdb.RowSet")

SQLQuery="SELECT ""ID"", ""Word"" FROM ""wordlist"""
SQLResult.activeConnection = ConnectToDB
SQLResult.Command = SQLQuery
SQLResult.execute

exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
DialogLibraries.LoadLibrary("Standard")
Library=DialogLibraries.GetByName("Standard")
TheDialog=Library.GetByName("Dialog1")

Dialog=CreateUnoDialog(TheDialog)

DialogField=Dialog.GetControl("ListBox1")

While SQLResult.next
ListBoxItem = SQLResult.getString(2)
DialogField.additem(ListBoxItem, DialogField.ItemCount)
Wend

If Dialog.Execute=exitOK Then
CurrentItemName=DialogField.SelectedItem
End If

End Sub
I hope you will find it useful.

Kind regards,
Dmitri
batonac
Posts: 1
Joined: Mon Nov 09, 2009 9:34 pm

Re: Populate list box with records from database

Post by batonac »

Hey, this code is great! You've saved me alot of time from figuring this out manually.

There's just one extra thing I'd like to do that I can't quite seem to figure out how.

In a form, you can use a listbox to display one value (or database column) but record another. (as an example, display cutomerName, record customerID)

I don't know how to do this with macro code.

Any help?
OOo 3.1.1 on (K)ubuntu 9.10
Post Reply