Hi all,
I am new here and the very first post regarding the database component in Gambas gb.db. In the past I was using MS-Access every now and then for simple DB-driven apps. Now I try to get the first project in Gambas with a simple sqlite3 database for photo lenses. There are only 3 tables at the moment. I managed to get a form with the lens data (name, aperture, date of purchase etc). In two more forms I can add, delete or update the manufactorer and the mount (e.g. Canon, M42, ...).
Now the question: how can I manage to create a search form based on the lenses form? Do I have to create it manually? Is there any option to use another component?
I know the DataBrower object as part of gb.db. It already has controls for next/previous/new record/etc - but the DataBrowser is not able to show the appropiate foreign keys in table lens. So this seems no option. Any ideas? Thanks.
The simple schema of the DB:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Makers" (
"ID_Makers" INTEGER NOT NULL,
"Maker" TEXT NOT NULL,
"Notes" TEXT,
PRIMARY KEY("ID_Makers" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Mounts" (
"ID_mounts" INTEGER NOT NULL,
"Mount" TEXT NOT NULL,
"Notes" TEXT,
PRIMARY KEY("ID_mounts" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "lenses" (
"ID_lenses" INTEGER NOT NULL,
"Make" INTEGER NOT NULL,
"DateBuy" TEXT NOT NULL,
"Mount" INTEGER NOT NULL,
"MaxAperture" TEXT NOT NULL,
"MinAperture" TEXT NOT NULL,
"FocalLength" TEXT NOT NULL,
"Type" TEXT,
"Colour" TEXT,
"SerialNr" TEXT,
"Label" TEXT,
"Comments" TEXT,
PRIMARY KEY("ID_lenses" AUTOINCREMENT)
);
CREATE UNIQUE INDEX IF NOT EXISTS "ID_lenses" ON "lenses" (
"ID_lenses" ASC
);
CREATE VIEW "vwLenses" AS Select * from lenses;
gb.db - database component, simple search form?
- cogier
- Site Admin
- Posts: 1129
- Joined: Wednesday 21st September 2016 2:22pm
- Location: Guernsey, Channel Islands
Re: gb.db - database component, simple search form?
Welcome to the forum.
I am not up to speed with databases, however, there is a sample program on the 'Gambas Farm' or here that might help. My favoured way to deal with data is to use CSV files. If you post a spreadsheet of some of your data I can show you another way to search for items.
I am not up to speed with databases, however, there is a sample program on the 'Gambas Farm' or here that might help. My favoured way to deal with data is to use CSV files. If you post a spreadsheet of some of your data I can show you another way to search for items.
Re: gb.db - database component, simple search form?
Thank you. Before I started with Gambas I had a look into all database examples I could find. What I am actually looking for is a general solution for a form based search.
After thinking a while about this problem I try to code a general approach in order to re-use the code in other applications. Simple use-cases look like as follows:
- push a button "Search/Find". Execute the click-event
- the click-event creates new textboxes for each DataControl (same x/y-position, width and height) as a new layer above the existing DataControl controls at runtime. This is pretty easy with a loop through all Form.controls
- each of the textboxes get a Tag referring to the original DataControl
- the new textboxes can be edited for filter criteria
- Push a button "Apply search" and run the on-click-event
- the on-click-event procedure checks the content of the newly created textboxes, builds the WHERE-Statement and fuels DB.Subst and finally filters the datasource-control
This is already done (apart from the last point) with only a few lines of code. Thanks to Gambas. I hope you can follow my ideas.
So far so good. I am struggling at the moment with a simple problem:
How can I read the columns and rows of the control DataComboView provided by gb.db.form ?? I have to columns in one of my DataComboView with a couple of rows:
Maker ID_Maker (primary key)
------------------------------
Canon 1
Tamron 2
How can I go through the columns and rows of DataComboView and print/debug them into the debug window? I couldn't find any code snippet on the internet, so I have no idea how to do it. Any ideas?
After thinking a while about this problem I try to code a general approach in order to re-use the code in other applications. Simple use-cases look like as follows:
- push a button "Search/Find". Execute the click-event
- the click-event creates new textboxes for each DataControl (same x/y-position, width and height) as a new layer above the existing DataControl controls at runtime. This is pretty easy with a loop through all Form.controls
- each of the textboxes get a Tag referring to the original DataControl
- the new textboxes can be edited for filter criteria
- Push a button "Apply search" and run the on-click-event
- the on-click-event procedure checks the content of the newly created textboxes, builds the WHERE-Statement and fuels DB.Subst and finally filters the datasource-control
This is already done (apart from the last point) with only a few lines of code. Thanks to Gambas. I hope you can follow my ideas.
So far so good. I am struggling at the moment with a simple problem:
How can I read the columns and rows of the control DataComboView provided by gb.db.form ?? I have to columns in one of my DataComboView with a couple of rows:
Maker ID_Maker (primary key)
------------------------------
Canon 1
Tamron 2
How can I go through the columns and rows of DataComboView and print/debug them into the debug window? I couldn't find any code snippet on the internet, so I have no idea how to do it. Any ideas?
- BruceSteers
- Posts: 1595
- Joined: Thursday 23rd July 2020 5:20pm
- Location: Isle of Wight
- Contact:
Re: gb.db - database component, simple search form?
Hi. for a GridView you would use this method with angle brackets..01McAc wrote: ↑Tuesday 26th January 2021 12:58pm So far so good. I am struggling at the moment with a simple problem:
How can I read the columns and rows of the control DataComboView provided by gb.db.form ?? I have to columns in one of my DataComboView with a couple of rows:
Maker ID_Maker (primary key)
------------------------------
Canon 1
Tamron 2
How can I go through the columns and rows of DataComboView and print/debug them into the debug window? I couldn't find any code snippet on the internet, so I have no idea how to do it. Any ideas?
For iRow As Integer = 0 To GridView1.Rows.Max
Debug GridView1[iRow, 0].Text ' 0 for column 0
Next
it's possibly the same or similar for your object. (not used it myself)but hit the left angle bracket [ and see what it says in the ide
Bruce
Last edited by BruceSteers on Tuesday 26th January 2021 2:43pm, edited 1 time in total.
If at first you don't succeed , try doing something differently.
BruceS
BruceS
Re: gb.db - database component, simple search form?
Appreciate your suggestions. I checked immediately but unfortunately, it doesn't work as DataComboView controls do not have a .Max method like GridView. The compiler throws an error message.
Other ideas?
Other ideas?
- BruceSteers
- Posts: 1595
- Joined: Thursday 23rd July 2020 5:20pm
- Location: Isle of Wight
- Contact:
Re: gb.db - database component, simple search form?
sure Max is a synonym for Count-1
So if the object lacks .Max use .Count - 1
So if the object lacks .Max use .Count - 1
If at first you don't succeed , try doing something differently.
BruceS
BruceS
- BruceSteers
- Posts: 1595
- Joined: Thursday 23rd July 2020 5:20pm
- Location: Isle of Wight
- Contact:
Re: gb.db - database component, simple search form?
oopsBruceSteers wrote: ↑Tuesday 26th January 2021 2:38pm sure Max is a synonym for Count-1
So if the object lacks .Max use .Count - 1
Maybe try GridView1.Rows.Max
(I forgot the Rows part sorry , I'll adjust the example above)
If at first you don't succeed , try doing something differently.
BruceS
BruceS
- BruceSteers
- Posts: 1595
- Joined: Thursday 23rd July 2020 5:20pm
- Location: Isle of Wight
- Contact:
Re: gb.db - database component, simple search form?
okay sorry i have breifly looked at the wiki now.
the object does not have Rows so it's done a different way
using the associated DataView or DataSoiurce object the combobox is attached to.
DataView.Count
or
DataSource.Count
It will be a similar to retrieve the info as it was to enter it.
I feel i can't help any more though and may only miss-advise as it's not something i've used before and seems to work in a way you probably know more about than me.
There's always a way though
Bruce
the object does not have Rows so it's done a different way
using the associated DataView or DataSoiurce object the combobox is attached to.
DataView.Count
or
DataSource.Count
It will be a similar to retrieve the info as it was to enter it.
I feel i can't help any more though and may only miss-advise as it's not something i've used before and seems to work in a way you probably know more about than me.
There's always a way though
Bruce
If at first you don't succeed , try doing something differently.
BruceS
BruceS
Re: gb.db - database component, simple search form?
Bruce,
thanks for your help. I need to think about the problem with the DataComboView-controls.
In the meantime I finished a rough solution re the use-cases I mentioned above in one of my posts. In detail: a form based DB-search in sqllite3 works for the dabase bound controls DataControl. When I start the search DB.Subst looks like.
Before I am able to assemble the filter in a function I need to lookup each field in the database what type ID_lenses (INTEGER) , FocalLength (TEXT) and Colour (TEXT) is. As sqlite3 is not fully SQL compatible it requires a weird to describe the table "lenses".
If someone is interested in the code I'll post it once it is tidied up.
thanks for your help. I need to think about the problem with the DataComboView-controls.
In the meantime I finished a rough solution re the use-cases I mentioned above in one of my posts. In detail: a form based DB-search in sqllite3 works for the dabase bound controls DataControl. When I start the search DB.Subst looks like
Code: Select all
ID_lenses = 1 AND FocalLength = '35' AND Colour = 'Black'
Before I am able to assemble the filter in a function I need to lookup each field in the database what type ID_lenses (INTEGER) , FocalLength (TEXT) and Colour (TEXT) is. As sqlite3 is not fully SQL compatible it requires a weird
Code: Select all
pragma table_info("lenses)"
If someone is interested in the code I'll post it once it is tidied up.