combobox with database values

[jalz]

Hi Guys,
I cant seem to figure this out, been searchin the forums for some code samples but getting a little lost. Firstly I've got a combobox called cb_customer_id. I've got a database with a table called customer with fields called customerID,customer.

When I run the script below, I seem to be getting a number, which doesnt relate to anything into the combo box field. I want to get a list of all the customer names in the drop down and store the customerID once the user selects the list.

Anyone please help me to move further along.

Thanks
Jalz

Code: Select all

on preOpenCard
if gConnID is a integer then
   ## Query the database for data
   
   answer gConnID
   
   put "SELECT customerID, customer FROM customer ORDER BY customer" into tTheSQLQuery
   put revQueryDatabase( gConnID, tTheSQLQuery) into tTheComboBoxData
   answer tTheComboBoxData
   put tTheComboBoxData into button "cb_customer_id"
end if
pass preOpenCard
end preOpenCard

[SparkOut]

From the docs:

The revQueryDatabase function returns a record set ID which designates the record set (database cursor) selected by the SQLQuery. The record set ID is an integer.

This maintains a database cursor which operates over the recordset.
I believe you are looking for the command: revDataFromQuery which returns the values from the tables, not the recordset with cursor. Try

Code: Select all

put revDataFromQuery(comma, return, gConnID, tTheSQLQuery) into tTheComboBoxData

That should bring back the data itself.

[jalz]

Thanks Sparkout,

That query has loaded my variable tTheComboBoxData with the values I was expecting. The problem now seems to be getting the data into the cb_customer_id combobox as a drop down when I hit the down arrow on this control.

i thought naively that put tTheComboBoxData into button "cb_customer_id" would do the job, but it seems like its putting in the fourth row value in the field.

Is there another command I need to use to load all these values as options as a drop down list in a combo box?

Jalz

[bangkok]

Try :

Code: Select all

set the text of button "cb_customer_id" to tTheComboBoxData into 

You're going to have something like :

"ABCD,1"
"LJZL,2"
"EEEEE,3"

That's not very fancy.

So here is a scheme :
-you put only id name into the the button
-you create a custom property into the button, in order to store the id values
-then after you are able to link the name chosen by the user with the dropdown... with the id value stored into the custom property thanks to the number of the selected line

into your DB script :

Code: Select all

set itemdelimiter to tab

put empty into tListe1
put empty into tListe2

repeat for each line tABC of tTheComboBoxData 
put item 1 of tABC&cr after tListe1
put item 2 of tABC&cr after tListe2
end repeat

set the text of button "cb_customer_id" to tListe1
set the cMyProperty of button "cb_customer_id" to tListe2

into your button :

Code: Select all

on menuPick pItemName

put the menuhistory of me into tLine --- will take the number of the line of the menu selected by the user

put line tLine of the cMyProperty of me into tValue  --- create before the custome property "cMyProperty" into your button

answer tValue

end menuPick

It's dirty but i hope you get the idea.

[jalz]

Thank you bangkok,

your db script code worked an absolute treat.

I couldn't get your menupick code working, but I figured if I do the following I can get what I wanted stored in the cb_customer_id button.

Code: Select all

on menuPick pItemName
   
   split pItemName by comma
   put pItemName[1] into button "cb_customer_id"
   
end menuPick

The only problem I now have is, once the id is stored, and suppose the user picked the wrong id, when they go back to the combo box and click on the down arrow to reveal the values, they seem to disappear and only the id that is stored is available for selection.

I thought I could use the mouseDown to repopulate the combo box (i.e. running the db script) - but that then doesn't seem to trigger the script to populate the db script

[jalz]

Hi I managed to get it working with the mouseDown handler on the control. When someone enters combobox I've forced it to repopulate using the dbscript bangkok provided.

I have two further questions regarding combo box.
1) First question is at the moment my combo box has a pixel width of 100. When the user clicks on the arrow to reveal the values, the long names can be barely read, as the drop dow is too narrow. Is there a way to programatically increase the width of the dropdown values so my users can read fairly long values (without me making the physical size of the combobox larger on my layout)

2) Im going to use this control on several cards. Is there a neat way I can store this control in a library stack an reuse it in the cards I need? Ultimately, if I update the code on the master control, I'd like it to filter through all the cloned controls. Hope that makes sense?
I notice there is an insert blank button control, but not sure if I can mimic my master combobox control to work within the blank control.

Any advice would be great.
Thanks

Jalz