• Registered: 2014-06-25
  • Posts: 863

Topic: SQL Query in Combobox

Dear all,

I am looking for a way to create a custom Query for a combobox which  should show an information without any relationship for the source. I mean the source of my queries afterward would be this combobox.

example:

let say, I have 2 tables

Table 1                        Table 2

a                                   10 
b                                   50
c                                   80
d                                   100
e                                    20
                                      id.table1



I want a combobox with the list of the content of Table1 which will display according content of Table2 in a grid.

In current version, I can only choose the foreignKeys which does not help in my project. Any idea, suggestion?

  • Registered: 2014-02-14
  • Posts: 10,294

Re: SQL Query in Combobox

You can fill the component the resulting of any SQL query.


Example:

 Form1.ComboBox1.dbSQLExecute ('SELECT fieldname, id FROM tablename');

Note that in SQL query is a field "id", the component could know what records in the database belongs to the selected item.
Allowed SQL query and without the presence of the field "id", but in this case it would be impossible to know what the record ID has selected in ComboBox using property Form1.ComboBox1.dbItemID



Another way:
Also you can populate ComboBox without foreign key, just type name of table in property ForeignKey

Dmitry.
  • Registered: 2014-06-25
  • Posts: 863

Re: SQL Query in Combobox

Thanks a lot Dmitry, I did not know that we could fill in the combobox property with a table name. Good to know.

The less script, the better in a project anyway smile

4 Reply by tcoton (edited by tcoton 2015-09-26 08:19:18)

  • Registered: 2014-06-25
  • Posts: 863

Re: SQL Query in Combobox

Actually, when I enter the table name in the field foreignkey, it searches in the table but I get an error :

"no such column: tablename"

and all subsequent queries are stopped...

When using the script I get an error when using a search button using the scripted combobox as a source for the id because the SQL query does not know which table.column corresponds to the id retrieved.

near"=":syntax error

The query looks like this when using default search properties for a button (no use of script for the search) :

SELECT DISTINCT TABLE.COLUMN, etc....FROM TABLENAME WHERE=1

it should look like

SELECT DISTINCT TABLE.COLUMN, etc....FROM TABLENAME WHERE +COMBOBOX1.id+=1

Does that mean that I would have to write all my application using scripts? sad

  • Registered: 2014-02-14
  • Posts: 10,294

Re: SQL Query in Combobox

tcoton
I made some changes, please download latest alpha version:
http://myvisualdatabase.com/forum/viewt … 9720#p9720

Dmitry.
  • Registered: 2014-06-25
  • Posts: 863

Re: SQL Query in Combobox

It is exactly the same with version alpha downloaded today.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: SQL Query in Combobox

Please send me project with this error to support@drive-software.com

Dmitry.
  • Registered: 2014-06-25
  • Posts: 863

Re: SQL Query in Combobox

I will send you my project. The query has a little bit improved with beta version as it is now:

SELECT DISTINCT TABLE.COLUMN,..... FROM TABLE WHERE "".id=1

  • Registered: 2014-06-25
  • Posts: 863

Re: SQL Query in Combobox

For the sake of other users, Dmitry made some changes in a new alpha version for MySQL which makes it working by just filling in table names in ForeignKey property instead of only relationships.

Thanks a lot Dmitry!