ComboBox linked to database

[Gomrath]

Howdy all,

I'm new to Revolution and hoping that one of you guru's can assist me here!

I want to link a combo box to a database to populate the records in the drop down selection. I created a query using the database query builder and I can connect to the query and associated column but the combo box only displays the first record of my column. What I'm trying to do is use a combo box to select a record and then based on the selection, populate additional fields with other records from the table into some text fields.

Any help or suggestions would be appreciated!

Cheers

[Mark]

Hi Gomrath,

I never use the query builder, because I do this kind of things directly by script.

if you want to use the query builder, I would create a field with the requireed query. Then make a script that displays the contents of the field in the combobox.

Best,

Mark

[Gomrath]

Thanks for the suggestion Mark!

When I say I'm new to revolution I mean really new!

Would it be to much to ask if you could give me some scripting examples to achieve this

Cheers

[Mark]

Gomrath,

Make the query for a field, then

Code: Select all

on preOpenCard
  put fld "Your Query" into button "You Combobox"
  pass preOpenCard
end preOpenCard

Give that the data is a return delimited list, this should work. If not, you have to modify that list before you put the data into the combobox, possibly with

Code: Select all

replace comma with cd in fld "Your Query"

If your data is tab-delimited, you use tab instead of comma, etc.

Best,

Mark

[QuangNgo]

Hi guys
I'm new with livecode .I do as you said but it doesn't work,so appreciated if you fix this problem.
I try to load database into combobox .
on preOpenCard
---call openConnection
openConnection
if gConnectionID is a integer then
## Query the database for data
put revQueryDatabase( gConnectionID, "select name from student") into theCursor
put theCursor into button "ComboBox1" ====the problem here can not get data into combobox1
end if
pass preOpenCard
end preOpenCard

Hope guys help me.

[Mark]

Hi QuangNgo,

Which part doesn't work for you? Making the connection? Getting the data? Putting the data into the combobox? Something else?

Mark

[QuangNgo]

Sorry Mark ,
I've just edited my post, Connect is OK,just can not get data from mysql into Combobox1

[QuangNgo]

Hi Mark,
I edited some new lines to my code ,it's work but just add one record to the combobox. And this record is at the end of my table.
For example :
I have table student
ID name
1 Quang
2 Jodie
3 Nguyen
it just get "Nguyen " to combobox
Hope you give me some ideas
Here is my code:
on preOpenCard
call openConnection
global gConnectionID
if gConnectionID is a integer then
## Query the database for data
put revQueryDatabase( gConnectionID, "select name from students ") into theCursor
put revdb_recordcount(theCursor) into count ==return the number of rows in table
answer count
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
put the result into theError
if theError is empty then
repeat until count
add 1 to i
repeat for each line tKey in (the keys of theDataGridArray)
put theDataGridArray["name"] into button "Combobox 1"===the problem i guess here.
end repeat
end repeat
end if

## Close the database cursor
revCloseCursor theCursor
end if
## Close the database connection
revCloseDatabase gConnectionID
else
answer "Error connecting to the database:" && gConnectionID & "."
end if

pass preOpenCard
end preOpenCard

Thanks,

[Klaus]

Chào Quang,

Code: Select all

...
repeat for each line tKey in (the keys of theDataGridArray)
   put theDataGridArray[i]["name"] into button "Combobox 1"===the problem i guess here.
end repeat
...

You are right!

If you take a look yo will see that the content of the Combobox will be overwritten
in every loop, so in the end only the LAST entry will be visible in the button!

You need to collect ALL entries first and THEN put them into the button like this:

Code: Select all

...
repeat for each line tKey in (the keys of theDataGridArray)
   put theDataGridArray[i]["name"] & CR after tComboBox
end repeat
  ## Get rid of trailing CR
  delete char -1 of tComBox
  set the text of btn "combobox" to tComboBox
...

Best

Klaus

P.S.
i also strongly recommend you to check these stacks here:
http://www.runrev.com/developers/lesson ... nferences/

[QuangNgo]

Hi Klaus,
Thank you so much I fixed that problem ,that link is very useful
Quang

[QuangNgo]

Hi Klaus,

Sorry to bother you again.I'm developing a application that I want to implement code library which It can be reused for any card or stack for example
connect to the database , retrieve database or doing some thing else .Because I'm beginner now so my code is not pretty good ,I have to rewrite a code in many stack and card many times.Hope you have a good website or good advice for me .I'm so appreciated for that help

[Klaus]

Hi QuangNgo,

RunRev also has some lessons on their site:
http://lessons.runrev.com/

P.S.
You could put all relevant scipts into the script of your MAIN stack!
This way all substacks (with all cards! and the mainstack itself) can access them!


Best

Klaus

[QuangNgo]

Hi Klaus,
I have 1 stack and 1 substack in my application .The problem here is not able to call the command on substack.Could you explain to me why ? I'm trying to use any way to fix and call that CovertCursortoArray command but it doesn't work.I could write it in the same card but It made redundant for my code.Because I 'll call this command many times .Hope you could give me some ideas or other way to fix it.
*Stack is Application
-Test card : there is 1 button to select record into datagrid
*substack is MyLibrary
-DBConnection card : there are 3 command
-openConnection
-CloseConnection
-ConvertCursortoArray pCursor,@pOutArray
My problem is not able to call ConvertCursortoArray but I can call openConnection.
I have try to fix but It doesn't help.Hope you guys can help me
Here is my code
* myStack Application
on mouseUp
call openConnection of card "DBConnection" of stack "Mysubstack" ==call command from substack
global gConnectionID
if gConnectionID is a integer then
put revQueryDatabase( gConnectionID, "call test1()") into theCursor
if theCursor is an integer then
call ConvertSQLCursorToArray theCursor, theDataGridArray ======================PROBLEM IS HERE "CAN"T FIND HANDLER ERROR==============
put the result into theError

if theError is empty then
set the dgData of group "DataGrid 1" to theDataGridArray
end if
## Close the database cursor
revCloseCursor theCursor
end if
## Close the database connection
revCloseDatabase gConnectionID

else
answer "Error connecting to the database:" && gConnectionID & "."
end if
end mouseUp

I'm so appreciated for your help
Quang

[Klaus]

Hi Quang,

you now posted this script for the fourth time in different threads!
I don't think that repetition will raise the chance for an answer!

You received a couple of hints alreaday but you do not seem to care

1. I proposed to put all your scripts into the script of your MAIN stack. so all other stacks/cards/objects can use these scripts
2. Then someone else proposed that you make your substack with the relevant scripts a LIBRARY stack, which is obviously what you need here.
3. Someone else pointed out that you do definitvely NOT need to use "call" at all here, which does not work at all anyway.
No reaction from you, but the same question(s) again.

OK, I will give it another try, but please read this carefully and try to understand what we are porposing!
And look up every unknown (or uncertain!) term in the LiveCode dictionary!
And work through all the stack we already posted links to!

1. To make you substack a REAL library stack, in the true meaning of the word, you do this:
Add this handler to your mainstack script, maybe "on openstack"

Code: Select all

on openstack
  ## Your stuff here
  ## ..
  start using "Nmae of your substack with all the handlers and functions!
end openstack

Now ALL stack (main or sub) and all cards and all objects on all cards in all stack can access the script of your substack/library stack!
AND now there is no need for "CALL" anymore", so you can simply:

Code: Select all

on mouseUp
  ## call openConnection of card "DBConnection" of stack "Mysubstack" ==call command from substack
  ## Not neccessary anymore! Instead use:
  openConnection
 ...

2. You use:
...
private command ConvertSQLCursorToArray pCursor, @pOutArrayA
## ???
...
a. PRIVATE does not make sense and is wrong in this contexthere, so don't use it!
b. @pOutArrayA: this means hat you are going to PASS a value BY REFERENCE, but you don't.
If you do not understand what the @ sign (pass by reference) means, then don't use it!

Turn this handler into a function it will work:

Code: Select all

function ConvertSQLCursorToArray pCursor
  local i
  local theFields
  local theError
  ## Get the names of all the columns in the database cursor
  put revDatabaseColumnNames(pCursor) into theFields
  put empty into pOutArrayA

  ## Any problems? Then get out of here!
  if theFields begins with "revdberr," then
    answer "AN error occured:" & CR & item 2 to -1 of theFields i
    EXIT to top
  end if
    put 0 into i
    ## Loop through all rows in cursor
    repeat until revQueryIsAtEnd(pCursor)
      add 1 to i
      ## Move all fields in row into next dimension of the array
      repeat for each item theField in theFields
        put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[i][ theField ]
      end repeat
      revMoveToNextRecord pCursor
    end repeat
  return pOutArrayA
end ConvertSQLCursorToArray

c. Now you can use this anyhwere in your stacks like:

Code: Select all

on mouseUp

  ## No need to CALL anything anymore!
  openConnection
  global gConnectionID
  if gConnectionID is a integer then
    ## Query the database for data
    put revQueryDatabase( gConnectionID, "call test1()") into theCursor
    ## I am not sure what "call test1()" is meant to do here?

    if theCursor is an integer then
      PUT ConvertSQLCursorToArray(theCursor) into theDataGridArray
    ### etc...
    ...

So please try our solutions and check if that will work for you, which we are sure of!


Best

Klaus

[QuangNgo]

Hi Klaus,
I'm so shame when posted my script a lot , I just want to fix that problem without thinking of anything else.I'm so sorry about that.
Your instruction is very useful any specific ,I'm so appreciated for that.I'm trying to do it and hope it work fine.

Quang