Solved

Show all Tables in Database and Choose with "TabSheet"

Posted on 2004-09-25
27
326 Views
Last Modified: 2010-04-05
Hello All;    [ Delphi 6.02 Pro | Win2K Pro]
( Sorry for the title, but that is what I am needing :-)  )

[ HISTORY ]
   I have just started in the ADO Database part of Delphi.,
As I am (Have already) Designed a fully working project.
Using [ TQuery, TDataSource, TAdoTable, TDBGrid]
With a lot of " [TDBComboBox, TDBEdit, TDBMemo, TDBLabel];

   I have a web site, ( A Knowledge Base, like Microsoft's, that I have
built.
Access Database "ASP" )

   In the Admin area of the KB site, I have duplicated all the "Fields" into
A TPageControl.TabSheets'; With everything working beautifully. ( I Love
Delphi )
I have added in " DBGrid1.OnMouseMove" & "DBGrid1.TitleClick := For ASC &
DESC"
( I did a lot of searching on the internet, trying out several peoples
solutions to doing the ASC & DESC
in the DBGrid. and then i just added this in place.
[         ADOTable1.Sort := 'ContentID DESC, CatID ASC';      ]
And bingo, works like a charm :-)... The simplest things are always the ones
that you do last.
 So that part of it is over with.

===========================
[PLANED]
What I would like to do now, is to be able to make the following possible.

  Have my Connection to open up the "Database" showing "ALL" tables.
Then being able to click on a particular Table, and have it open a
[TabSheet from a PageControl], that is Embedded in the [Main
PageControl.TabSheet.]
In this PageControl.TabSheet. Will have a
DBGrid, TDBEdits, TDBMemo, and so on. ( I know you know what I am getting at )
That will allow me to then "Add/Edit/Delete" the information that is located in its Table.

There are a Total of: [ 32 ] Tables, located in the Database. With a Great
Possibility, for more.
As the KB Site grows, and more Categories become available.And more
information is needed
For the newer Categories. That will then benefit the original categories...

===========================
I know that this project is going to be something that might take a while to
develop.
As the general project that I have right now, I started on today, and have
gotten this far.
So I did it basically quick, Which I am a Great Visual Designer for Programs, and Web Sites.
So designing the User Interface of the program was nothing, very much fun actually.
And connecting all the "DB" objects together, was extremely cool, that made me love Delphi
That much more.

  So if anyone has some helpful links, information or some sample code that
they would be
Willing to share with me, that would be great.

Take Care All;

Wayne
0
Comment
Question by:Wayne Barron
  • 14
  • 13
27 Comments
 
LVL 12

Expert Comment

by:esoftbg
ID: 12153541
> [ Delphi 6.02 Pro | Win2K Pro]
well, but you did not mention about what kind of database is in use ....
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12153545
Hello [esoftbg];

  Actually I Did:

[Quote]I have a web site, ( A Knowledge Base, like Microsoft's, that I have
built.
Access Database "ASP" )[/Quote]

Above:  Access Database
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12153566
Thanks and sorry, obviously I did not read carefully ....
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12153679
Hello [esoftbg]

That is fine, I have done the same many times :-)
We are only human, Right?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12153702
I agree we are only human !
I never worked with ASP technology, so if your question was about a local Access database .MDB I would be able to answer, but for the remote located Access database I dare not ....
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12153714
It is located on a server, that is about 2-foot away from me :-) I connect through the network to the Access Database, of the project that I have started on.

  So it is like you connecting onto your Access Database on your Harddrive, of the same Computer.
Except with me, I am going through a High-Speed network Interface...  Basically the same deal.

Thank You
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12153740
This way I could try to develop a simple exemple with 3 or 4 tables (not 32) using ADO components.
Are you interested or not ?
May be ASP technology does not use ADO ? I don't know ...
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12153765
Yes Sir;

   The ASP Will not even be seeing the Application.
This is a program that is used to simply Administer the Database and that is it.


Project runs.
Shows the Entire Database.
Click on a Table ( Opens Tabsheet with DBGrid showing its Fields )
And so on.

  Thank you very much , for your time.

It is 3:15am were I am .. NC USA
So I will check back in here when I get up tomorrow, ( Or ) Today :-) considering the time.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12153771
Good night !
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12153775
Here is 10:24am Bulgaria, Eastern Europe and I am going to develop ....
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12153791
Eastern Europe.
I have always wanted to go there,
Have seen it on the "Discovery Channel".
Beautiful Place.

Take care
I am going to bed now

Nite
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 125 total points
ID: 12154379
Good Morning carrzkiss !

Take a look at the example below:

unit Unit1_Q_21145009;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, DB, DBTables, Grids, DBGrids,
  Buttons, StdCtrls, ExtCtrls, DBCtrls, ComObj, ADODB, ToolWin, ComCtrls,
  Spin, Calendar;

type
  TForm1 = class(TForm)
    dsaLIST: TDataSource;
    ADOQuery: TADOQuery;
    ToolBar: TToolBar;
    ADOConnection: TADOConnection;
    ADOQry: TADOQuery;
    ADOCommand: TADOCommand;
    ADOStoredProc: TADOStoredProc;
    ListBoxTables: TListBox;
    PageControl: TPageControl;
    tbtnShowTable: TToolButton;
    tbtnDatabase: TToolButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure tbtnShowTableClick(Sender: TObject);
    procedure PageControlChange(Sender: TObject);
    procedure tbtnDatabaseClick(Sender: TObject);
  private{ Private declarations }
  public { Public declarations }
    ConnStr:   string;
  end;

var
  Form1: TForm1;

implementation

uses
  Unit2_Q_21145009;

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  FLDN:   string;
  FN:     string;
  TN:     string;
  T:      string;
  R:      string;
  V:      Variant;
  SL:     TStringList;
  function  CreateAccessTable(TableName: string; Fields: string): Boolean;
  begin
    Result := True;
    try
      ADOQry.Connection := AdoConnection;
      T :=' Create Table ' + TableName
        + ' ( ' + Fields + ' )';
      ADOQry.SQL.Text := T;
      ADOQry.ExecSQL;
    except
      Result := False;
    end;
  end;
begin
  R := '';
  FN := ExtractFilePath(Application.ExeName) + 'DATABASE.MDB';
  ConnStr := 'Data Source=' + FN + ';Provider=Microsoft.Jet.OLEDB.4.0';
  if not FileExists(FN) then
  begin
    V := CreateOleObject('ADOX.Catalog');
    V.Create(ConnStr);
  end;
  ADOConnection.ConnectionString := ConnStr;
  ADOConnection.Open;
  SL := TStringList.Create;
  try
    AdoConnection.GetTableNames(SL);
    ListBoxTables.Items.Assign(SL);

    TN := 'SINGERS';
    if (SL.IndexOf(TN)>=0) then
      R := 'Table ' + TN + ' already exists'
    else
    begin
      FLDN :='ID AUTOINCREMENT PRIMARY KEY,'
           + 'ALL_NAMES TEXT(48),'
           + 'COUNTRY TEXT(32),'
           + 'INFO NOTE';
      if CreateAccessTable(TN, FLDN) then
        R := 'Table ' + TN + ' is created successfully'
      else
        R := 'Table ' + TN + ' is not created';
    end;

    TN := 'TOURS';
    if (SL.IndexOf(TN)>=0) then
      R := 'Table ' + TN + ' already exists'
    else
    begin
      FLDN :='ID AUTOINCREMENT PRIMARY KEY,'
           + 'SINGER_ID INTEGER,'
           + 'COUNTRY TEXT(32),'
           + 'TOWN TEXT(32),'
           + 'INFO NOTE';
      if CreateAccessTable(TN, FLDN) then
        R := 'Table ' + TN + ' is created successfully'
      else
        R := 'Table ' + TN + ' is not created';
    end;

    TN := 'CARS';
    if (SL.IndexOf(TN)>=0) then
      R := 'Table ' + TN + ' already exists'
    else
    begin
      FLDN :='ID AUTOINCREMENT PRIMARY KEY,'
           + 'SINGER_ID INTEGER,'
           + 'MANUFACTURER TEXT(32),'
           + 'MODEL TEXT(32),'
           + 'PRICE NUMBER,'
           + 'INFO NOTE';
      if CreateAccessTable(TN, FLDN) then
        R := 'Table ' + TN + ' is created successfully'
      else
        R := 'Table ' + TN + ' is not created';
    end;

    TN := 'HOUSES';
    if (SL.IndexOf(TN)>=0) then
      R := 'Table ' + TN + ' already exists'
    else
    begin
      FLDN :='ID AUTOINCREMENT PRIMARY KEY,'
           + 'SINGER_ID INTEGER,'
           + 'COUNTRY TEXT(32),'
           + 'TOWN TEXT(32),'
           + 'PRICE NUMBER,'
           + 'INFO NOTE';
      if CreateAccessTable(TN, FLDN) then
        R := 'Table ' + TN + ' is created successfully'
      else
        R := 'Table ' + TN + ' is not created';
    end;
  finally
    SL.Free;
  end;
end;

procedure TForm1.FormDestroy(Sender: TObject);
var
  I:      Integer;
begin
  for I := 0 to ADOConnection.DataSetCount-1 do
    ADOConnection.DataSets[I].Active := False;
end;

procedure TForm1.tbtnShowTableClick(Sender: TObject);
var
  B:      Boolean;
  I:      Integer;
  Idx:    Integer;
  SL:     TStringList;
  S:      string;
  T:      string;
  ADOQ:   TADOQuery;
  DBG:    TDBGrid;
  DSA:    TDataSource;
  Sheet:  TTabSheet;
  ListBox:TListBox;
begin
  Idx := 0;
  if (ListBoxTables.Items.Count>0) then
  if (ListBoxTables.ItemIndex>-1) then
  begin
    B := True;
    S := ListBoxTables.Items[ListBoxTables.ItemIndex];
    for I := 0 to PageControl.PageCount-1 do
    begin
      if (PageControl.Pages[I].Caption=S) then
      begin
        Idx := ListBoxTables.ItemIndex;
        B := False;
        Break;
      end;
    end;
    if B then
    begin
      Idx := PageControl.PageCount;
      Sheet := TTabSheet.Create(PageControl);
      Sheet.PageControl := PageControl;
      Sheet.Caption := S;
      ListBox := TListBox.Create(Sheet);
      ListBox.Parent := Sheet;
      SL := TStringList.Create;
      ADOQ := TADOQuery.Create(Sheet);
      try
        AdoConnection.GetFieldNames(S, SL);
        ListBox.Items.Assign(SL);
        ADOQ.Connection := AdoConnection;
        T :=''
          + ' SELECT * FROM ' + S
          + '';
        ADOQ.SQL.Text := T;
        DSA := TDataSource.Create(Sheet);
        DSA.DataSet := ADOQ;
        DBG := TDBGrid.Create(Sheet);
        DBG.Parent := Sheet;
        DBG.DataSource := DSA;
        DBG.Height := 256;
        DBG.Align := alTop;
        ListBox.Align := alClient;
        ADOQ.Active := True;
      finally
        SL.Free;
      end;
    end;
    PageControl.ActivePageIndex := Idx;
  end;
end;

procedure TForm1.PageControlChange(Sender: TObject);
begin
  ListBoxTables.ItemIndex := PageControl.ActivePage.TabIndex;
end;

procedure TForm1.tbtnDatabaseClick(Sender: TObject);
begin
  Form2.ShowModal;
end;

end.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12154419
download a working example from:
page:        http://www.geocities.com/esoftbg/
  link:        Q_21145009.zip

//........

I did try to implement code for open a new Database, but I have not a Network, so I did not test Unit2_Q_21145009 / Form2 ....
I hope all is O.K.

Best Regards,
Emil
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 30

Author Comment

by:Wayne Barron
ID: 12156092
Well, After I ran the demo, and kept getting errors,
Everytime I turned around, about not being able to find the Database. I Finally put 1&1 together and hey.
Change this

FN := ExtractFilePath(Application.ExeName) + '\\My-Server\My-path\data\my.mdb

to

FN := '\\My-Server\My-path\data\FireKB.MDB';

And now it works like a complete charm...

Thank you so very much. Now I have a shot load of work ahead of me now. :-)

Thank you.

Wayne
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12159256
I'm glad that you have been solve the problem with FN ....

But I had some mistakes in the example, so please download the new version from the same link.

Thank you,
Emil
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12159842
Hello [esoftbg]; Emil;

  I downloaded the new version, and the [Delete Pages]
Is something that I needed to know as well, so you read my mind
On that one.

  In viewing the Tables, I have to add this in to the :
ListBoxOnClick
begin
DBG.Update; // ( DBG = TDBGrid )
end;

 As when opening a lot of Tables it would get conjested and the Tables would not match up to there "Tabname" or the "ListBox Name"
So with [ DBG.Update ] into play, everything runs smoothly now.

===============

  With the example that you provided, Everything is done at [Runtime];
Which makes for not to big of an .exe file.
Which I would like to keep like that. ( And it is teaching me new things as well )
What I am trying to do now, is to:
You know how if you have the DBGrid - Visual on your form,
And it is connected to all the DB Component and to the Database.
How you are able to click on a "Field" and if you have a "TDBMemo"
On the form, it will show its information into the "TDBMemo".

Would I do this under the 'OnClick" or "OnClickCell" ?
It would be the "OnClickCell" if I am correct. Right?

----------
Also.
In the Project Code that you made, you did the
DBGrid & ListBox ( The ListBox that shows the "Fields" )
with all there property atributes, for "Align, Height" And so forth.

What I would like to know is this?
I have another "TabSheet" located underneath the mainTabSheet.
And this TabSheet will have all the DB Components that will be
used to show/edit information from the Table.Fields.
DBMemo, DBEdit, DBComboBox, TLabel and so forth.
What is the best practice for setting these up on there own tabs?

Example:
On the project that I was making when I first posted this Topic.
------
I have [6-Tabsheets].
1st TabSheet - 3 DBComboBoxes ( With 3 TLabels Above Each DBCB )
2nd TabSheet - 8 DBEdits & 1 DBMemo Plus 9 TEdits (Above each Component )

And it is basically the same through-out the rest of the TabSheets.

What I am going to need to do, is to have a lot more TabSheets.
Depending on the "Table" that is opened.

  If you can give me a Breif description on how to do this.
With " PageControlInfo = The second PageControl on the Form "
TabSheet1 - has this
TabSheet2 - has this
TabSheet3 - has this

If I can get a general understanding of how this all works.
and how to implement it, then I should not have a problem in
Designing it, but I need just a little more help from you please?

Thank You
Wayne
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12161447
I was wrong on this?

[Quote]Would I do this under the 'OnClick" or "OnClickCell" ?
It would be the "OnClickCell" if I am correct. Right?[/Quote]

It is not the Cell, it is the [ListBoxTables]

I just though about this, so I am going to see if I can implement this, I will post back later on today

Wayne
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12162119
Hello [carrzkiss]; Wayne;

> So with [ DBG.Update ] into play, everything runs smoothly now.
O.k. that's fine
//........
>  With the example that you provided, Everything is done at [Runtime];
I did it this way because I don't know the structure of your Database and can not design Sheets at [DesignTime]
My opinion about [RunTime] vs [DesignTime] is:
 1). Programming about unknown different Databases [RunTime] - more difficult, less flexibility (It is more difficult to assign events for the runtime-created-Components);
 2). Programming about a known Database [DesignTime] - more easy, more flexibility;
//........
> How you are able to click on a "Field" and if you have a "TDBMemo"
> On the form, it will show its information into the "TDBMemo".

> Would I do this under the 'OnClick" or "OnClickCell" ?
> It would be the "OnClickCell" if I am correct. Right?
Yes, I would use "OnClickCell" to identify Memo fields....
//........
> In the Project Code that you made, you did the
> DBGrid & ListBox ( The ListBox that shows the "Fields" )
> with all there property attributes, for "Align, Height" And so forth.
The reason to use "Align, Height" is: all the visual Components RunTime created obtained from Delphi properties: Top := 0; Left := 0; and default Height; Width;
So, for easy locating I have used Align := alTop; Height := ###; - Component locates at the top of the Parent control using whole Width (of the Parent) and the specified Height; In addition: Align := alClient; does not need to be with specified Height, because the component will occupy whole not occupied by components used (Align := alTop, alBottom, alLeft, alRight). If there is a component with Align := alNone; the only way to be shown is by ComponentName.BringToFront - this way it will be shown over the Component with Align := alClient;.
//........
> I have another "TabSheet" located underneath the mainTabSheet.
> And this TabSheet will have all the DB Components that will be
> used to show/edit information from the Table.Fields.
> DBMemo, DBEdit, DBComboBox, TLabel and so forth.
> What is the best practice for setting these up on there own tabs?
I thing the best for the known Databases (their Tables are not under construction) containing Tables with known Fields is
[DesignTime] arranging of the DBGrids, DBMemos, DBEdits, DBComboBoxs, TLabels; This way you can develop a flexible and a convenient for use application WITH ONLY THIS DATABASE.
//........
> What I am going to need to do, is to have a lot more TabSheets.
> Depending on the "Table" that is opened.
Again we are on decision the [DesignTime] solution is better to fit your needs about the different Tables ....
I think it will be better to use additional PageControl on the Form for some Tables; Or place more components on the only one TabSheet using ScrollBox to be able to work with more Components on it;
//........

I'll be online after 16 hours and be able to improve the example if you need it ....

Best Regards,
Emil
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12163103
OK.
  This is what I was tinking. [Designtime] would be the best.
I am going to see if I can implement your code into [Designtime] instead of [Runtime].
Will post back in here later on with information on how it is going.

Thank You

Wayne
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12167202
I am waiting for info about how it is going ....
If you want I will implement the example code into [Designtime] instead of [Runtime].
It is my mistake that I wrote the example code into [Runtime] mode.
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12169670
Hello Emil;

   Well not going so good :-(
I was hoping that I would be able to implement it myself.
But unfortunantly I do not have enough knowledge of
How the Database Components word.
With your Runtime code. It gave a great deal of information and knowledge, that I did not have before. But I do not have enough in order to get the Runtime code to work for what I am needing it for.

  If you could make up an example of the "Designtime" code, that would be great.

Thank you Emil, for all you time.

Wayne
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12169836
Hi Wayne,

O.k. I will make an example of the [DesignTime] code.

Best Regards.
Emil
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12173885
Hi Wayne,

Please download the new version [DesignTime] mode from the same link.
I will contionue to test it for errors and if any I will improve it ....
You may test and to say your requirements ....

Emil
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12186273
Hello Emil;

   I downloaded the newest Demo, and that is exactalent.
that is what I was needing.
Thank you so very much.

p.s.
  Do you still have the "Runtime" code still available?
As some how? I lost it the other day when Delphi was
Running and another applications drivers went bad,
Causing my whole screen to go white, including my tackbar.
I had to reinstall my Win2k, and ? For some reason since I had
The Runtime code in Delphi, the whole folder was deleted?
This is so very strang, I do not understand how the folder god deleted.
I am still bothered about it, I mean everything that I was trying to do,
Plus the Runtime code that I download from you, plus some other
Codes, I lost a lot of stuff, and am very upset over it.
But if you can please either send me : delphian at carrz-fox-fire.com
(  the "-" actually belong in the address... )
Or add it to your site repository again, that would be great.

Thank you
Wayne
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12186296
Hello Emil;

  Stupid me :-)

I backed up my Delphi Folder to "J" Drive, and for some stupid reason
I thought that I backed it up to my "H" drive.... :p

So I have it. woooooo that was close :-)
I thought that i had lost another program that I am building for my site.

But anyway, I have Everything..  :-)

Thank You
Wayne
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12187714
Hi Wayne,

I keep [DesignTime] and [RunTime] versions on my HDD, so if you really lose some of them or both, I will send it to you .... :-))
Or if you need some improvement of it's code ....
My e-mail address is esoftbg_e_e@yahoo.com

Have a nice day !
Emil
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 12189058
Thank you Emil.

Wayne
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now