Query in Dbase 6

[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 ?

[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.

[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

[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:

Code: Select all

MyQuery.SQLUpdate;

, 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;

[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;

[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.

[marsheng]

'Working with Master/Detail Relationships' article in the MyDAC documentation.

Any chance of a link to where this is to be found ?

[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" .

[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.

[AndreyZ]

MySQL Reference Manuals are available at http://dev.mysql.com/doc