searching the result of a join!?! 1

[Jonah86]

Hi there. You may have read my previous message, I'm having all kinds of problems

I have these two tables, that I need to join within my project, I've joined them in an sql query object so that all the data will be usable. The problem is I need to be able to search within the results of the join. The only way I know how to make the join usable is giving it an alias which doesn't seem to work for me.

Please help me, and maybe let me know if I'm going about this entirely wrong.

Thanks so much!
Jonah

 

[lespaul]

Are you just trying to run a query from the press of a button?

Then you need to drop a TQuery (or ADOQuery) on your form. When you double click the button in the designer you'll get a basic procedure outline. Then run the query and display the results in some DB aware components.

procedure TForm1.Button1Click(Sender: TObject);
var
strFormVariable, strSomeQueryVar : string;
begin
  strFormVariable := Form1.EditBox.Text;
  Form1.Query.SQL.Clear; //clears the existing SQL statement
  Form1.Query.SQL.Add('SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.FieldName = TABLE2.FieldName WHERE SomeField = ' + strFormVariable);
  Form1.Query.Active := True; //runs the query
  strSomeQueryVar := Form1.Query.FieldByName('SomeField').AsString  //assigns results from query to a variable
end;

So that's the basic code for running a query from a button. If you need help writing the SQL statement, you can post back or there are several SQL forums.

Leslie

 

[Jonah86]

Well I tried that sql that you have in there...and it wouldn't work. It just stops and says there is an error near the join. I'm able to do close to what I want if I search by the primary key of both tables and just select all from both tables using that criteria. But when I try to search on a field that is unique to one of the tables I NEED the join to work, and it just won't.

Thank you for your help, though.

Jonah

 

[lespaul]

What is the query you are trying to run? I find that if I do:


ShowMessage(Query1.Text);

(add Dialogs to the uses clause)
then I see the exact query that is being passed to the database and sometimes it's as easy as not having a space between words.



Leslie

 

[Jonah86]

Thank you very, that worked out wonderfully. I've made some substantial progress now.

I have one other question though. I want to automatically create the record number value when I am inserting a new record, and the way I want to do it is to simply add 1 to the highest recordnumber in the db. In vb I would just go to the end of the table and take that record number and add one...but I'm not sure how to go about that with Delphi. Any ideas?

Thanks again!

 

[lespaul]

It depends. If you have a lot of users, then you may want to store that number in its own table. I have a process (which is only run once a week by one person) where I run a query:

SELECT MAX(IDNUMBER) AS IDNUMBER FROM TABLE

Then I can use:

intIDNumber := FieldByName('IDNUMBER').AsInteger;
Inc(intIDNumber)

The problem with this set up if you have a lot of users is having multiple users run the request at the same time and end up with the same number.

If you already have a query run (or a table component) that has the highest number (you said going to the end of the table), then you can use Query1.Last to move to the last record and then get the value like above from that record.

HTH

Leslie

 

[Jonah86]

Ok...I'm trying this method as best I can, but it won't seem to work. I got it to work aside from the fact that the recordnum that it comes up with is wrong. My highest current number is 6, and it gives me 2 as my new recordnum after the increment. Here is the code for that:

datamodule1.headerquery.SQL.Clear;
datamodule1.headerquery.SQL.Add('select max(recordnum) as recordnum from header');
datamodule1.headerquery.ExecSQL;
recordnum := datamodule1.Table1.FieldByName('recordnum').AsInteger;
inc(recordnum);

I have the datasource for table1 set as headersource whose query is headerquery(great names I know), which you can see is where I am adding the SQL. The table is the only place I could find the "fieldbyname" function...so I used it. At least I'm getting closer.

Thank you SO much!

 

[lespaul]

change

datamodule1.headerquery.ExecSQL;


to

datamodule1.headerquery.Active := True;

you only need to use the ExecSQL when you are doing an update, insert or delete query.

That should do it!!

Leslie

 

[Jonah86]

How are you so awesome!? Anyway, that worked, and I thank you from the bottom of my heart. I'll try not to bug you anymore:|

Thanks again!

 

[lespaul]

You're not bugging me! I'm glad to help! You should see some of my questions when I was first learning Delphi! I'm happy to pass along what I've learned, that's what this place is all about!

les

(although it IS nice to be called AWESOME!!! - thanks!)