?
Solved

DBNAVIGATOR (enter data without enter ID for this data )

Posted on 2003-03-23
24
Medium Priority
?
293 Views
Last Modified: 2010-04-04
hi experts,
I have three tables
1-article(Aid,description,title,abstract,location); AID=P.K                                        relation:
2-keywordarticle( AID,keywordID) ; aid+keyword=p.k , aid=f.k,keywordID =f.k           article M:N   keyword
3-keyword(keywordID,keywordname);  keywordid:p.k

when a user enter  data as keywords in DBnavigator  don’t need to enter keywordID  only enter keywordname and
if keyword not exist in keyword table create a another keywordid dynamically and add this keywordid to keywordarticle table also.
If exist this keyword in the keyword table only add this keywordid to keywordarticle table.

Is this probable, if probable any one can help me in solving this problem?

Thanks in advance
Hamid reza.


0
Comment
Question by:123456
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 8
24 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8193196
usually use the beforepost-event
for check and supplying your id.

what database do you use and
what connectiontype (bde, ado, other)?

meikl ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8196538
My database is access 2000
My adoconnection is ADO.
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 200 total points
ID: 8200882
since you have a n:m relation,
my comment about beforepost-event
is nonsense (will only work in a n:1 relation)

a sample how it could be done

unit ado_m_n_relation_u;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ExtCtrls, DBCtrls, StdCtrls, Grids, DBGrids, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOTableArticle: TADOTable;
    ADOTableKeyWordArticle: TADOTable;
    ADOTableKeyWord: TADOTable;
    dsArticle: TDataSource;
    dbgArticle: TDBGrid;
    dbgAssignedKeywords: TDBGrid;
    dsAssignedKeyWords: TDataSource;
    edKeyWord: TEdit;
    dblSelectKeyword: TDBLookupComboBox;
    btnAssignKeywordToArticle: TButton;
    dsKeyWords: TDataSource;
    ADOTableKeyWordArticleAID: TIntegerField;
    ADOTableKeyWordArticleKeywordID: TIntegerField;
    ADOTableKeyWordArticleKeyWordNameLookUp: TStringField;
    DBNavigator1: TDBNavigator;
    ADOTableArticleAID: TAutoIncField;
    ADOTableArticleDescription: TWideStringField;
    ADOTableArticleTitle: TWideStringField;
    ADOTableArticleAbstract: TWideStringField;
    ADOTableArticleLocation: TWideStringField;
    procedure dblSelectKeywordCloseUp(Sender: TObject);
    procedure btnAssignKeywordToArticleClick(Sender: TObject);
  private
    function GetKeyWordId(AKeyWord : String) : Integer;
    function KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
    procedure AssignKeyWordToArticle(AArticleID, AKeyWordID : Integer);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.dblSelectKeywordCloseUp(Sender: TObject);
begin
  edKeyWord.Text := dblSelectKeyWord.Text;
end;

//Search/Create a KeyWordID
function TForm1.GetKeyWordId(AKeyWord : String) : Integer;
begin
  if not ADOTableKeyWord.Locate('KeyWordName',AKeyWord,[]) then
  begin
    ADOTableKeyWord.Append;
    ADOTableKeyWord.FieldByName('KeyWordName').AsString := AKeyWord;
    ADOTableKeyWord.Post;
  end;
  result := ADOTableKeyWord.FieldByName('KeyWordID').AsInteger;
end;

//check if a Keyword is already assigned
function TForm1.KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
begin
  result := not ADOTableKeyWordArticle.Locate('AID;KeyWordId',VarArrayOf([AArticleID,AKeyWordID]),[]);
end;

//insert a new relation
procedure TForm1.AssignKeyWordToArticle(AArticleID, AKeyWordID : Integer);
begin
  ADOTableKeyWordArticle.Append;
  ADOTableKeyWordArticle.FieldByName('AID').AsInteger := AArticleID;
  ADOTableKeyWordArticle.FieldByName('KeyWordID').AsInteger := AKeyWordID;
  ADOTableKeyWordArticle.Post;
end;

procedure TForm1.btnAssignKeywordToArticleClick(Sender: TObject);
var KeyWordID : Integer;
begin
  if ADOTableArticle.State = dsBrowse then
  begin
    KeyWordId := GetKeyWordId(edKeyWord.Text);
    if KeyWordIDisNotAssigned(ADOTableArticle.FieldByName('AID').AsInteger, KeyWordID) then
      AssignKeyWordToArticle(ADOTableArticle.FieldByName('AID').AsInteger, KeyWordID)
    else raise Exception.Create('Keyword is already assigned to this Article!');
  end else raise Exception.Create('Keyword can only assigned on saved Articles!');
end;

end.


hope that helps a bit

meikl ;-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 8200918
btw. if you leave your eMail,
then i could send you this sample project

meikl ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8201810
thanks,  if probable
send this to hr_1357@yahoo.com
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8202082
sample on the way ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8202198
thanks i recieved your mail
please read the last of my comment in ( master-detail question)

your experience is so high in writing sql,
if you read the first comments of LUKA you found that his skill in writing sql is not so high(sorry i tell it).

thanks again.
hamid reza


0
 
LVL 2

Author Comment

by:123456
ID: 8208022
Hi again meiki,
This section is not work properly , for it test a same word. I cant find it but why ?
//check if a Keyword is already assigned
function TForm1.KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
begin
  result := not ADOTableKeyWordArticle.Locate('AID;KeyWordId',VarArrayOf([AArticleID,AKeyWordID]),[]);
end;



0
 
LVL 2

Author Comment

by:123456
ID: 8208124
Hi again meiki,
This section is not work properly , for it test a same word. I cant find it but why ?
//check if a Keyword is already assigned
function TForm1.KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
begin
  result := not ADOTableKeyWordArticle.Locate('AID;KeyWordId',VarArrayOf([AArticleID,AKeyWordID]),[]);
end;
0
 
LVL 2

Author Comment

by:123456
ID: 8208152
Hi again meiki,
This section is not work properly , for it test a same word. I cant find it but why ?
//check if a Keyword is already assigned
function TForm1.KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
begin
  result := not ADOTableKeyWordArticle.Locate('AID;KeyWordId',VarArrayOf([AArticleID,AKeyWordID]),[]);
end;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8208184
? what did not work properly

is there already the same keyword assigned to the article,
and its creates a new one or do it try to insert a new
relation, which already exists?

meikl ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8208658
if you enter a same keyword that exist in special article dont work propely as you want.
and get error when insert to keywordarticle table with database.
because in my mind this if dont work properly:

if KeyWordIDisNotAssigned(ADOTableArticle.FieldByName('AID').AsInteger, KeyWordID) then
     
m.reza
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8208670
not noticed this (works properly for me),
are you sure that you forward the right IDs?
0
 
LVL 2

Author Comment

by:123456
ID: 8208872
please see my sql request in  master-detail question.
0
 
LVL 2

Author Comment

by:123456
ID: 8209043
I enter in two times these words for a same article
Salam
But in second time get error with database not with your
raise Exception.Create('Keyword is already assigned to this Article!');
I don’t understand  because everything is right.
I test this program with step to step  I found that the result of this section in my computer every time is true .
For it get error with databases on second word.
//check if a Keyword is already assigned
function TForm1.KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
begin
  result := not ADOTableKeyWordArticle.Locate('AID;KeyWordId',VarArrayOf([AArticleID,AKeyWordID]),[]);
end;

Why!?
 Say that my OS is win98A . and ado version is  2.7?
0
 
LVL 2

Author Comment

by:123456
ID: 8210113
please save me meiki in (masetr-detail sql ) question.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8210231
well will try,
but i'm short in time.

does it have time until weekend?
(at weekend i'm not so under pressure,
so that i can look closer to this problem)

about the problem above,
there is another possibility to check,
if a keyword is assigned to an article
(i've heard, but this never happened to me,
that locate sometimes will not work properly)

solution follows later

meikl ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8212264
I will see you as soon as possible for you.

i accept your answer and thanks for every assisting in this question and other questions.
GOD BLESS YOU.

hamid reza
0
 
LVL 2

Author Comment

by:123456
ID: 8230523
any feed back from you, meikl?
0
 
LVL 2

Author Comment

by:123456
ID: 8230529
any feed back from you meikl?

on thing:
about the problem of this question(locate problem).
i was found that if the key of seraching is increased to more than one key  this problem was occured.

See you as soon as probable.


God bless you
Hamid reza

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 8243388
well, a replacemant,

//check if a Keyword is already assigned
function TForm1.KeyWordIDisNotAssigned(AArticleID, AKeyWordID : Integer) : Boolean;
const vSQL = 'select * from KeywordArticle where AID = %d and KeyWordId = %d';
begin
  ADOQuery1.SQL.Text := Format(vSQL,[AArticleID,AKeyWordID]);
  ADOQuery1.Open;
  result := ADOQuery1.IsEmpty;
  ADOQuery1.Close;
end;

will be a bit slower than locate, but safer

hope this helps

meikl ;-)
0
 
LVL 2

Author Comment

by:123456
ID: 8254247
Hi,
Great, the previous problem solved.

Every day I have a new problem !
You implement my request work with  dbgrid(that exist a lookup field for showing the related field in article table).
But ,The other changes such as delete and edit of keywords for a special article is unavailable(this unavailable for lookup field).
Do any work that add these features to  dbgrid or if adgrid have not these branch of features any other work existed for implementing  these features(ps: I cant use  express quantum grid  for its price although the copyright law is not attention in my country)?
My request :
I want to show the keywords for special article and change or delete keyword in this article but in m-n relationship
I cant, why?
I need m-n relationship.
Thanks in advance

God bless you
Hamid reza

0
 
LVL 2

Author Comment

by:123456
ID: 8264502
any feed back from you
0
 
LVL 2

Author Comment

by:123456
ID: 8278972
hi meikl,
i was solved my problem
thankd for your every assisting
hamid reza
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month10 days, left to enroll

762 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