?
Solved

ADO question

Posted on 2004-10-26
10
Medium Priority
?
234 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:TherosEquipment
  • 5
  • 4
10 Comments
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12413675
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
 

Author Comment

by:TherosEquipment
ID: 12413817
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12413955
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 12

Expert Comment

by:esoftbg
ID: 12414026
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
 

Author Comment

by:TherosEquipment
ID: 12414102
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12414431
> 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
 

Author Comment

by:TherosEquipment
ID: 12414608
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 12414638
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
 

Author Comment

by:TherosEquipment
ID: 12414978
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
 
LVL 12

Accepted Solution

by:
esoftbg earned 500 total points
ID: 12415061
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question