Page 1 of 1
Query in Dbase 6
Posted: Thu 10 May 2012 11:29
by marsheng
I've been chasing my tail for days. (New to SQL) I cannot get the DBGrid from the query to work correctly. Currently is displays all the data from tblEventBikes.
I have 3 tables tblEventsDetails, tblMembers and tblEventBikes
I have 3 DBGrids dbgEventsDetails, dbgMembers and dbgEventBikesMem (filled from qryEventBikesMem)
Using the query builder I got
Code: Select all
SELECT
register.eventbikes.*
FROM
register.eventsdetails,
register.eventbikes,
register.members
WHERE
register.eventbikes.EventID = register.eventsdetails.EventID AND
register.eventbikes.MemID = register.members.MemID
On the DBGrids I have OnClick set to
Code: Select all
procedure UpdateBikes;
begin
with dm.qryEventBikesMem do
begin
SQLUpdate;
end;
end;
How do I go about trying to debug this ?
Re: Query in Dbase 6
Posted: Thu 10 May 2012 14:54
by AndreyZ
Hello,
SQLUpdate is a property that stores the UPDATE statement. The code you provided just receives the UPDATE statement that is stored in qryEventBikesMem.SQLUpdate and does nothing else. Please specify what functionality you want to achieve in the UpdateBikes procedure.
Re: Query in Dbase 6
Posted: Thu 10 May 2012 20:21
by marsheng
Should be Delphi 6 - Old school programmer.
To clear things up for me, when I execute SQLUpdate I presume that whatever is in the Update SQL Property is executed. I'm unsure what the Insert Update Delete Lock and Refresh radio button do?
Ok I have just seen that these correspond to the properties of the Query. The name on the form probably should have been called SQL Properties instead of Update SQL. I have moved the code from the Insert button to the Update.
Still no joy. I get the complete list.
Does it matter that the query is preformed on a child form instead of the main form? The record pointers would still set to the corresponding member and race details?
Alternatively how can I transfer a string into the where clause in the SQL statement
register.eventbikes.EventID = My Delphi string
Re: Query in Dbase 6
Posted: Fri 11 May 2012 12:34
by AndreyZ
SQLUpdate is a property that stores SQL statement that is used to update data in a dataset. Here is a code example:
Code: Select all
MyQuery.SQL.Text := 'select * from tablename';
MyQuery.SQLUpdate.Text := 'update tablename set somefieldname=:somefieldname where id=:id';
MyQuery.Open; // here the 'select * from tablename' is executed
MyQuery.Edit;
MyQuery.FieldByName('somefieldname').AsString := 'test';
MyQuery.Post; // here the 'update tablename set somefieldname=:somefieldname where id=:id' is executed
When you execute the following code:
, it just retrieves the UPDATE statement from the SQLUpdate property.
I don't understand the functionality you want to obtain. What do you mean by 'I get the complete list.' ? What do you want to do with a dataset that stores the result of your query?
To use your code in a SQL statement, you can use the following code:
Code: Select all
MyQuery.SQL.Clear;
MyQuery.SQL.Add('select * from register.eventbikes');
MyQuery.SQL.Add('where register.eventbikes.EventID=' + IntToStr(your_value));
MyQuery.Open;
Re: Query in Dbase 6
Posted: Sun 13 May 2012 00:55
by marsheng
I'm used to using Master-Detail tables and this is where my thinking was
I'm was trying to use the selected record from members table to be the search parameter for the SQL as in Master-Detail. .
Fixed it - 3 days to get there !!!
SELECT * FROM eventbikes
WHERE MemID = :MemID
AND EventID = :EventID
procedure UpdateBikes;
begin
with dm.qryEventBikesMem do begin
ParamByName('MemID').AsInteger := dm.tblMembersMEMID.AsInteger;
ParamByName('EventID').AsInteger:= dm.tblEventDetailsEVENTID.AsInteger;
if (Active = true) then
Refresh
else
Active:=True;
end;
end;
Re: Query in Dbase 6
Posted: Mon 14 May 2012 08:08
by AndreyZ
You don't need to provide parameters for master-detail relationship on your own. There are two way to establish master-detail relationship. You can find theirs detailed description with code examples in the 'Working with Master/Detail Relationships' article in the MyDAC documentation.
Re: Query in Dbase 6
Posted: Tue 15 May 2012 00:30
by marsheng
'Working with Master/Detail Relationships' article in the MyDAC documentation.
Any chance of a link to where this is to be found ?
Re: Query in Dbase 6
Posted: Tue 15 May 2012 07:35
by AndreyZ
You should perform the following steps:
- open the MyDAC documentation using the Delphi Main menu->MyDAC->MyDAC Help;
- go to the "Contents" tab;
- expand the "Using MyDAC" folder.
The second article in the "Using MyDAC" folder is "Working with Master/Detail Relationships" .
Re: Query in Dbase 6
Posted: Tue 15 May 2012 10:37
by marsheng
Thanks for the reply and I found it. It is funny how we are very particular to get the syntax correct in programming as it has to be correct but we follow a much looser syntax in life. If it was a program, MySql documentation <> MySql Help as I discovered while searching the web for MySql documentation and found nothing.
Re: Query in Dbase 6
Posted: Wed 16 May 2012 10:09
by AndreyZ
MySQL Reference Manuals are available at
http://dev.mysql.com/doc