Query problem

[MLNorton]

I am trying to learn SQL. I have a simple Project with an ADOConnection, An ADOQuery, a DataSource and a DBGrid. In the ADO Connection I have a connection to a Database, Brigade DB1.mdb. Brigade DB1.mdb contains four Tables. The one I want to access is Members. In the ADOQuery SQL string I have:
SELECT *FROM xxxxxx.
I do not know what to use for the xxxxxx. I have tried ADOTable1, Members and Brigade DB1.None is acceptable. I cannot set the ADOQuery to true.

What am I doing wrong?

 

[Glenn9999]

In a database, you have a database name which can have tables in them. In SQL, the part you have xx'ed out is the table name. You will need to know the table name as it is defined within the database.

And for future reference in your learning, "select *" is considered bad form, so don't use it in any production-oriented programs - and hopefully in your learning you will progress past it.

It is not possible for anyone to acknowledge truth when their salary depends on them not doing it.

 

[MLNorton]

I was using the incorrect Table name. The correct name is Membership and now I can set ADOQuery to true and set the DataSource DataSet to ADOQuery 1. I have set the ADOQuery SQL String to:

SELECT LastName, FirstName
FROM Membership

I have the following code:

procedure TForm_Member.Button_GoClick(Sender: TObject);
begin
With ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add ('SELECT * from Membership');
SQL.Add ('ORDER BY LastName DESC');
Open;
end;
end;

My DBGrid is empty.

I get no response when I run the programs and press the Go Button.

What am I doing wrong?

 

[majlumbo]

SQL is rather simple

Select Clause is used to select which columns you want. You are not limited to only physical columns in the table. You can select the count(), or avg(), even use if statements.

MySQL example (concat is specific to MySQL):
select concat(lastname, ', ', firstname) as fullname

From Clause identifies which tables you are drawing the columns. If you are getting columns from more than one table, then you need to join the two tables either in the from clause or in the where clause. (I prefer to do it in the where).

From Table1, Table2

Where Clause identifies all the filters and identifies the common columns in the tables listed in the from clause.

MySQL example (curdate() is specific to MySQL)
Where table1.ColumnName_id = table2.ColumnName_id and date_received < curdate()

Order By clause lists all of the columns you want to order your result set by (while ascending is default, descending is also possible)

ORDER BY
lastname, firstname, columnA desc

There are other clauses (group by, having, but this will get you going for a while)

to update a record use

Update <tablename> set <columnname> = <value>

To insert a new record use

Insert into <tablename> (<columnA>, <columnB>) values ('<ColumnAValue>', '<ColumnBValue>')

To delete record(s) use
delete from <tablename> where <columnname> = '<columnvalue>' (if no where clause is given, all the records are deleted)

 

[DjangMan]

Your DbGrid must be associated with a TDataSource and the TDataSource must be associated to your TADOQuery.

 

[smays]

I rarely ever 'test drive' a query inside the program in which I embed the query. Whenever possible I use a separate program to test the queries. For example, for MySQL I use QueryBrowser, for Access databases I use either a program I whipped up myself or I use Access.exe, and for Oracle I use a program I whipped up myself. SQL query generation can be just as complicated as GUI development, so I try to not mix together the two unknowns until I know the query is going to return exactly what I think it should.

If you have an Access database (*.mdb) but lack Access.exe, OpenOffice Base can be downloaded and installed so you can experiment with queries before attempting to use them in your program.

Testing queries outside your program will help determine whether you have your TDBGrid, etc... set up correctly as DjangMan was saying or if the query is actually going to return anything at all.

Steve.