SQL sentence for duplicated field value

[cubangirl]

Hi everybody,

I'm new programming with Delphi and all the object oriented stuff. And with databases.

I have a visual dBase database, I'm using TTable, TDataSource, TQuery.

My problem: there is a REFERENCE field that must be unique. REFERENCE is an index, I put it as manteined, but not unique, because it somehow compared the value of the field with the erased records of the table.

I wrote this code, with the help of a friend, to avoid records with repeted REFRENCE of been posted to the database:

begin
refActual:=DataSet.FieldByname('REFERENCE').AsString;
if buscaREFsalvar.Active then buscaREFsalvar.Close;
buscaREFsalvar.SQL.Clear;
buscaREFsalvar.SQL.Add('SELECT COUNT(*) AS TOT_REC FROM "'+ dbfPath +'" WHERE REFERENCE='''+refActual+'''');
try
buscaREFsalvar.active:=true;
except
On E: Exception do
begin
MessageDlg(E.Message, mtError, [mbOk], 0);
Abort;
Exit;
end;
end;
if buscaREFsalvar.FieldByName('TOT_REC').AsInteger > 0 then
begin
MessageDlg('Reference field must be unique', mtWarning, [mbOk], 0);
buscaREFsalvar.Close;
Abort;
Exit;
end;
buscaREFsalvar.Close;
end;


Now, the problem is that sometimes I want to edit an old record of my table, and I can't. I was thinking of change the query not to count the records, but to return the RecNo of the record where the query finds refActual. if the number of the records is the same, the record can be posted, but i don't know if it's gonna be posted only once or twice...

I hope someone can help...

Thanx in advance

 

[lespaul]

Just so I'm clear on what's going on:

You run a query that returns the REFERENCE field. You assign this value to refActual. Then you run a query counting all the records that have refActual in the REFERENCE field. If there is an error you exit, but if the count is greater than 0 then you present a message to the user that the REFERENCE must be unique.

Now you want to be able to edit the information. So create a form that has DBEdit fields (or a DBGrid) that shows the information from the query, so:

begin
  refActual:=DataSet.FieldByname('REFERENCE').AsString;
  if buscaREFsalvar.Active then
    buscaREFsalvar.Close;
  buscaREFsalvar.SQL.Clear;
  buscaREFsalvar.SQL.Add('SELECT * FROM "'+ dbfPath +'" WHERE REFERENCE=' + QuotedStr(refActual));
  try
    buscaREFsalvar.active:=true;
  except
    On E: Exception do
    begin
      MessageDlg(E.Message, mtError, [mbOk], 0);
      Abort;
      Exit;
    end;
  end;
  if not buscaREFsalvar.IsEmpty then
  begin
    MessageDlg('Reference field must be unique', mtWarning, [mbOk], 0);
    buscaREFsalvar.Close;
    Abort;
    Exit;
  end
  else
    Form1.Show;
  buscaREFsalvar.Close;
end;

Where Form1 is the new form you create with the Database components (Data Controls Tab). With these controls you can edit the information in your database.


Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[cubangirl]

Hi Lespaul, thank you very much for your answer. I am not sure that I underestood it, but I will read it more carefully again. Probably I didn't explain myself very well in my previous message.

I already have the field edited. I have a TDBEdit with the REFERENCE field. I get the refActual:=DataSet.FieldByname('REFERENCE').AsString from the TDBEdit, not from a query.

Again: I am navigating in a TTable, I have some TDBEdit, a TDBRichEdit, I can go forward, backward, insert new records, edit them, delete whatever I want, etc.

But I also need sometimes to edit the records. And I can, as far as I change the REFERENCE. Because if I do not change the reference, the Query returns 1 record with that REFERENCE and the program goes to 'Reference must be unique' and aborts and exits the Post.

Maybe it would be easier, and more efficient if my quetion would had only been: I need a SQL Query sentence that returns the number of the record(s) where REFERENCE = TheValueIWantToFind.

I think that maybe I can solve the problem with that sentence.

Thank you again

 

[lespaul]

If all you want to do is locate the record in the TTable that matches a REFERENCE entered into an edit box then try this:

procedure TForm1.Edit1Change(Sender: TObject);
begin
  if (Edit1.Text <> '') then
  try
     Table1.FindNearest([Edit1.Text]);
  except
    On EDatabaseError do;
    On EConvertError do;
  end;
end;

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[cubangirl]

Lespaul, thank you again.

I finally could talk with my friend, the one who helped me before, and, as he knows what my problem is, he solved it at the moment.

The code I copied above is part of the BeforePost procedure. There is a question

if field = '' then
....
else
begin
the code above
end;

the problem was solved changing the else to
else if DataSet.State = dsInsert then

I appologize, because my question wasn't clear enough. I thought the problem was there, but it wasn't. I need to think object oriented, and I can not, yet.

Thanks and sorry again,

Liz

 

[lespaul]

No problem! Glad you got it figured out!

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned