Mechanics of using SQL to get Data from Access

[BillKilgore]

I'm writing an Access DB that will maintain an inventory of metal barstock and the products that they ultimately become.

Each bar is assigned a sequential number when it enters inventory independent of its make up. There are many different metals and alloys.

As each bar is machined into one of 18 different products, the bar used is removed from inventory and the parts are entered in.

There are 18 different part numbers.

I have to do the following:

-Find the last number assigned to a bar so the new number can be assigned.

-Periodically, loop thru the bar inventory to count the number of bars by metal type and sum the value of each type value at the same time.

-Do the same for each type of part

We'll be able to associate a bar with a batch of parts and see if the process is within limits.

I've had formal SQL training but never used it with Delphi. My basis for using Delphi was previous training in Turbo Pascal. I've gotten to where I can use "Locate," set field values, and use "insert".

I'm using ADOTables, DBComboBoxes, and DBEditBoxes.

My trouble is how and where to set up the SQL Code?

Given what I've learned in the last 2 weeks it's maybe,

With ADOTable do
begin
MYSTERY AREA
end

Can I do this from an ADOTable?

I know how complex SQL code can become so that in the past I've usually extracted with a SELECT * FROM WHERE sequence and massaged the gross data with software. You can also take more than one slice of the same data.

With Delphi, will I have to save the SQL data to a file or load an array before I can work with the data?

I've spent a pretty good length of time searching through the archives at this (and other) web sites with no luck. I've done pretty good getting through the basics of DB usage
with Delphi but as things become more complex the examples
thin out.

If you've stayed with me so far, thank you and I really appreciate any input you'd care to impart.

Thanks, Bill Kilgore

 

[lespaul]

you can drop an ADOQuery component on the form and use the SQL property to create your SQL statement and then set Active := True to run the query.

To get the data, I prefer to use the FieldByName.

var
SomeField : string;
begin
  With ADOQuery do
  begin
    SQL := 'SELECT * FROM tableName';
    Active := True;
    SomeField := FieldByName('FieldName').AsString;
  end;
end;

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[BillKilgore]

Thanks to Lespaul for getting back to me.

I set an ADOQuery component, a DataSource component, and a
DBEdit box into my form.

I set the ConnectionString to my Access DB. Could'nt set the "Active" property to True without putting something into the "SQL" property.

I inferred that the code probably handled that.

I tried the Code as presented and got an error message,

"Incompatible Types: 'TStrings' and 'String'."

I tried other tables with only text fields. I also set the "SQL :=..." code line into the ADOQuery and the "Active" to true. I got the same error.

I reset the "...:= FieldByName(FieldName..." to Value. Same result.

The field I'm reading in the table is Text.

I can't understand why a TString won't accept the 'SELECT *...' as a string and I don't see much else in the ADOQuery to reset.

Or does the code shown above go into the SQL property of the ADOQuery instead of the DBEdit box on the form?

I'm using Delphi 7.0 Professional. Is there some Preference or Option I didn't set properly?

I'm kinda stumped here.

Bill Kilgore

 

[lespaul]

could you post the actual code you are trying to use?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[BillKilgore]

As Lespaul requested, here's a copy of the code I'm using. It's in the form and associated with a DBEdit box called DBEdit.

procedure TfmBarInventoryInput.DBEditChange(Sender: TObject);
var SomeField : String;
begin
with ADOQuery1 do
begin
SQL := 'SELECT * FROM Bars';
Active := True;
SomeField := FieldByName('BarNumber').AsString;
end;
end;

Bill Kilgore

 

[donvittorio]

Bill,

the 'incompatible types' error message you get occurs because you are trying to assign a single string to the SQL property, which is of type TStrings. TStrings is a collection of strings, not a single string. Use Add to add a single string to the collection, or use the Text property to access the whole collection as one long string. Did that make sense? i.e.

procedure TfmBarInventoryInput.DBEditChange(Sender: TObject);
var SomeField : String;
begin
  with ADOQuery1 do
  begin
    SQL.Add('SELECT * FROM Bars');
    Active := True;
    SomeField := FieldByName('BarNumber').AsString;
  end;
end;

OR

procedure TfmBarInventoryInput.DBEditChange(Sender: TObject);
var SomeField : String;
begin
  with ADOQuery1 do
  begin
    SQL.Text := SELECT * FROM Bars';
    Active := True;
    SomeField := FieldByName('BarNumber').AsString;
  end;
end;

Steve

 

[lespaul]

my bad, forgot the 'Add'

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[BillKilgore]

Thank you so much, people. It bought it.

If I'd stayed up with this stuff like I'm supposed to I'd have known this stuff.

Bill Kilgore

 

[svanels]

Maybe the select * from bars will give some problems, you are using SQL to obtain data and the wildcard * is not a SQL standard. The equivalent in most SQL dialects is LIKE


Steven

 

[donvittorio]

I think the * is an SQL standard, it's certainly always worked for me in Oracle, Interbase, Access, Paradox and MySQL.
The 'like' operator is used for comparison in the where clause, e.g.

select * from bars
where bar_name like 'IRON%'

Steve

 

[lespaul]

and yes, the like operator in Access is ALSO the *:

SELECT * FROM bars
where bar_name like 'IRON*'