ADO question

Hello,

Here is my connection...

...
 Data := ExtractFilePath(Application.ExeName) + '\sales.mdb';
 try
  ADOConnection2.ConnectionString :=
            'Provider=Microsoft.Jet.OLEDB.4.0;' +
            'Data Source=' + Data +';'+
            'Persist Security Info=False';
...

From this I want to open a table called 'sales units' and populate Combobox3 with a list of the different text entries in the 'make' column.
From that, when a 'make' is selected, I want to load combobox4 with all the 'model' using a filterbyselection of combobox3.

I think I can figure it out from there, but it's been so long since I've had to do this, that I'm totally lost.  I don't want to use any special db components, I just want to do this programmatically. "no predefined db connection settings.

I hope I'm being clear...

Thanks,
Gabe
TherosEquipmentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BlackTigerXCommented:
something like this...

const
  CONN_STR='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False';
var
  Data:string;
  ADOTable:TADOTable;
begin
  ADOTable:=TADOTable.Create(nil);
  try
    Data:=ExtractFilePath(Application.ExeName) + '\sales.mdb';
    ADOConnection2.ConnectionString:=Format(CONN_STR, [Data]);
    ADOConnection2.LoginPrompt:=False;

    ADOTable.Connection:=ADOConnection2;
    ADOTable.TableName:='SalesUnit';
    ADOTable.Active:=True;
    //do whatever you are going to do with your table here...
  finally
    ADOTable.Free
  end
end;

I created the ADOTable component dinamically, I don't know if you want that or to drop a component in the form...
0
TherosEquipmentAuthor Commented:
looks pretty strait forward, but the real problem I have is getting the data after I make the connection.  see the last part of my question.

Thanks
0
esoftbgCommented:
procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Active := False;
  ADOQuery1.Connection:=ADOConnection2;
  ADOQuery1.SQL.Text := 'SELECT DISTINCT make FROM sales_units';
  ADOQuery1.Active := True;
  ComboBox3.Clear;
  while not ADOQuery1.EOF do
    ComboBox3.Items.Add(ADOQuery1.FieldByName('make').AsString);
end;
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

esoftbgCommented:
procedure TForm1.ComboBox3CloseUp(Sender: TObject);
begin
  ADOQuery2.Active := False;
  ADOQuery2.Connection:=ADOConnection2;
  ADOQuery2.SQL.Text :=''
                     + 'SELECT DISTINCT model FROM sales_units'
                     + ' WHERE make = '
                     + '''' + ComboBox3.Text + ''''
                     + '';
  ADOQuery2.Active := True;
  ComboBox4.Clear;
  while not ADOQuery2.EOF do
    ComboBox4.Items.Add(ADOQuery2.FieldByName('model').AsString);
end;
0
TherosEquipmentAuthor Commented:
Ok,
no compile errors, but the program hangs and does not paint to the screen.

procedure TForm1.LoadComboBox3;
const
  CONN_STR='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False';
var
  Data:string;
  ADOTable:TADOTable;
begin
  ADOTable:=TADOTable.Create(nil);
  try
    Data:=ExtractFilePath(Application.ExeName) + '\sales.mdb';
    ADOConnection2.ConnectionString:=Format(CONN_STR, [Data]);
    ADOConnection2.LoginPrompt:=False;
    ADOTable.Connection:=ADOConnection2;
    ADOTable.TableName:='sales_units';
    ADOTable.Active:=True;
    //do whatever you are going to do with your table here...
    loadquery;
  finally
    ADOTable.Free
  end
end;

procedure TForm1.LoadQuery;
begin
    combobox3.Clear;
  ADOQuery1.Active := False;
  ADOQuery1.Connection:=ADOConnection2;
  ADOQuery1.SQL.Text := 'SELECT DISTINCT make FROM sales_units';
  ADOQuery1.Active := True;
  while not ADOQuery1.EOF do
    ComboBox3.Items.Add(ADOQuery1.FieldByName('make').AsString);
end;

I like this, because I can understand it, but Isn't there a way to do this just by using the ADOTable?
0
esoftbgCommented:
> populate Combobox3 with a list of the different text entries in the 'make' column

'SELECT DISTINCT make FROM sales_units' does exactly above requirements

How to do it using ADOTable ?
0
TherosEquipmentAuthor Commented:
esoftbg,

Your solution is not producing the desired result.  The Count is right, but all the combobox items are the same (the first "make" in the list).

for example...

ComboBox3 should contain...

Allen
Imer
Multiquip

but I'm getting...

Allen
Allen
Allen

Sorry  :(
0
esoftbgCommented:
Oh, excuse me, I forgot something important:

  while not ADOQuery1.EOF do
  begin
    ComboBox3.Items.Add(ADOQuery1.FieldByName('make').AsString);
    ADOQuery1.Next; // it is very, very important !
  end;
0
TherosEquipmentAuthor Commented:
esoftbg,

Your solution works and the question is yours.
Just one other thing...

Can you help with the sql query for this...

procedure TForm1.ComboBox4Change(Sender: TObject);
begin
  ADOQuery2.Active := False;
  ADOQuery2.Connection:=ADOConnection2;
  ADOQuery2.SQL.Text :=''
                     + 'SELECT price AND list FROM sales_units'  //  << This is not working
                     + ' WHERE model = '
                     + '''' + ComboBox4.Text + ''''
                     + '';
  ADOQuery2.Active := True;
  label10.Caption := 'Price: $' + AdoQuery2.FieldByName('price').AsString;
  label11.Caption := 'List Price: $' + AdoQuery2.FieldByName('list').AsString;
end;
0
esoftbgCommented:
procedure TForm1.ComboBox4CloseUp(Sender: TObject);
begin
  ADOQuery2.Active := False;
  ADOQuery2.Connection:=ADOConnection2;
  ADOQuery2.SQL.Text :=''
                     + 'SELECT price, list FROM sales_units'
                     + ' WHERE model = '
                     + '''' + ComboBox4.Text + ''''
                     + '';
  ADOQuery2.Active := True;
  label10.Caption := 'Price: $' + AdoQuery2.FieldByName('price').AsString;
  label11.Caption := 'List Price: $' + AdoQuery2.FieldByName('list').AsString;
end;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.