• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

DBNAVIGATOR (enter data without enter ID for this data )

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
123456
Asked:
123456
  • 16
  • 8
1 Solution
 
kretzschmarCommented:
usually use the beforepost-event
for check and supplying your id.

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

meikl ;-)
0
 
123456Author Commented:
My database is access 2000
My adoconnection is ADO.
0
 
kretzschmarCommented:
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
Technology Partners: 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!

 
kretzschmarCommented:
btw. if you leave your eMail,
then i could send you this sample project

meikl ;-)
0
 
123456Author Commented:
thanks,  if probable
send this to hr_1357@yahoo.com
0
 
kretzschmarCommented:
sample on the way ;-)
0
 
123456Author Commented:
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
 
123456Author Commented:
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
 
123456Author Commented:
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
 
123456Author Commented:
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
 
kretzschmarCommented:
? 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
 
123456Author Commented:
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
 
kretzschmarCommented:
not noticed this (works properly for me),
are you sure that you forward the right IDs?
0
 
123456Author Commented:
please see my sql request in  master-detail question.
0
 
123456Author Commented:
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
 
123456Author Commented:
please save me meiki in (masetr-detail sql ) question.
0
 
kretzschmarCommented:
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
 
123456Author Commented:
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
 
123456Author Commented:
any feed back from you, meikl?
0
 
123456Author Commented:
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
 
kretzschmarCommented:
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
 
123456Author Commented:
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
 
123456Author Commented:
any feed back from you
0
 
123456Author Commented:
hi meikl,
i was solved my problem
thankd for your every assisting
hamid reza
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 16
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now