[Gambas-user] Using DataBrowser for a table which

[Benoît Minisini]

Le 19/08/2012 12:09, John Rose a écrit :
> My SQLite3 database contains country & centre tables. The centre table's
> primary key is the _id column. The centre table has a country_id column
> which is a Foreign Key to _id in the country table. The centre table
> contains a column named centre with values such as London, Paris,
> Strasbourg. The country table contains a column named country with
> values such as Great Britain, France. The Create Table SQL commands are:
> Create Table centre (_id Integer Primary Key AutoIncrement,
>                       centre Text Not Null,
>                       country_id Integer,
>                       Foreign Key (country_id) References country (_id)
>                                                On Delete Cascade
>                                                On Update Cascade);
> Create Table country (_id Integer Primary Key AutoIncrement,
>                        country Text Not Null);
>
> My FMain form contains a button (ButtonCentreMaintenance) which on
> clicking it calls (see code below) a form named FCentreMaintenance which
> is used to add/change/delete centres. The FCentreMaintenance form
> contains a DataBrowser named DataBrowserCentre which contains a
> DataSource named DataSourceCentre.
>
> Public Sub ButtonCentreMaintenance_Click()
>    Dim hForm As FCentreMaintenance
>    hform = New FCentreMaintenance
>    hForm.DataSourceCentre.Connection = MDatabase.DatabaseConnection
>    hForm.DataSourceCentre.Table = "centre"
>    hForm.DataSourceCentre.Filter = "country_id = " & CountryId
>    hForm.DataSourceCentre.Update()
>    hForm.Refresh()
>    hForm.DataBrowserCentre.Columns = ["centre"]
>    hForm.DataBrowserCentre.Labels = ["Centre"]
>    hForm.DataBrowserCentre.Editable = True
>    hForm.DataBrowserCentre.Update()
>    hForm.DataBrowserCentre.Refresh()
>    hForm.Show()
>    hForm.DataBrowserCentre.SetFocus()
>    Me.Hide()
> End
>
> The problem I have is that when I use the 'New' icon in
> DataBrowserCentre, it allows me to key in a new centre but when I try to
> save it (using the Save icon) it clears it on the form. The cause of
> this is the setting of the Filter in the code above (i.e. it only
> displays centre for centre rows which are in the particular country
> selected by the user when the form is called). What I need to be able to
> do is to have the save set the country_id column to the correct value
> (rather than 0 as it does now when saving). Any ideas on how to do this?
> The only way that I've thought of doing this is to have
> DataBrowserCentre also display the column country_id which the user
> would set to the correct value when adding a centre.
>
> If you want to run this yourself, I've attached the project & the
> database to this message. I used Germany & tried to add Berlin
>
>

You can do that, but:

1) You must embed the DataSource on "centre" inside a DataSource on 
"country" which points at the current country.

2) The name of the country primary key must be the same in all tables, 
so that Gambas automatically detects them.

Regards,

-- 
Benoît Minisini