Check if certain values in a database table already exist

I would like to check if two values ('shift' and 'date') already exist in another table called "LOG" before I post data to it. The 'shift' value is determined by a selection in the grid that displays data from the "shifts" table (UNITable1) and date parameter is the AdvOfficeStatusBar1.Panels[0].Text (it displays current date). The query is this:

 procedure TForm1.cxButton4Click(Sender: TObject);
    begin
    UniTransaction1.AddConnection(UniConnection1);
    UNIquery2.Close;
    UNIquery2.SQL.Clear;
    UNIquery2.SQL.Add('INSERT INTO LOG (DATE,SHIFT,TASK,DONE) VALUES (:a1,:a2,:a3,:a4)');
    UNIquery2.Prepare;
    UniTransaction1.StartTransaction;
    try
    UNIQuery1.DisableControls;
    UNIQuery1.First;
    while Uniquery1.EOF = false do begin
    UNIquery2.Params[0].asString := AdvOfficeStatusBar1.Panels[0].Text; //Current Date
    UNIquery2.Params[1].asString := UniTable1.FieldByName('SHIFT').asString;// Which shift
    UNIquery2.Params[2].asString := Uniquery1.FieldByName('TASK').asString;//many tasks
    UNIquery2.Params[3].Value := Uniquery1.FieldByName('DONE').Value;// Checkbox
    Uniquery1.Next; //get 'all' the values from UNIQuery1
    UNIquery2.ExecSQL;
    end;
    UniTransaction1.Commit;
    UNIQuery1.EnableControls;
    finally
    if UNIquery2.Connection.InTransaction then
    UNIquery2.Connection.Rollback;
    end;
    end;

How can I incorporate this ? How can I rewrite this transaction so it checks for the existing records ???

Edited: UNIQuery1 is iterating through other records that I want inserted.

Answers


I don't use whatever components you're referring to (I have no idea what a UNIQuery2 or UNITransaction might be, but your logic is totally out of whack. (So is your formatting, which is simply atrocious - if that's how your real code works, you really need to learn better habits.)

And please - get rid of the use of with now. It's highly prone to error, makes code hard to read and comprehend, and makes debugging extremely difficult.

You need to (in this order):

  • Connect to the database
  • Set up your query
  • Prepare your query
  • Set your parameters
  • Start your transaction
  • Begin a try (finally)
  • Begin a try (except)
  • Execute your query
  • On exception, roll back your query
  • If no exception, commit your transaction
  • In finally, close the query and connection

Also, if your components are compatible with TDataSet, it should support TDataSet.IsEmpty that you should use (if not UNIQuery2.IsEmpty then) instead of if UNIQuery.Eof = false (which should more cleanly be if not UNIQuery.Eof).

I can't make an effort to re-work your code, because it's all over the place. You have UNIQuery2 in one place, UNIQuery1 in another (and the with statement that makes it all more confusing), and I don't use (or have) whatever components you're using to even try to figure it out. I hope this information can help you sort it out. If not, post a new question with your cleaned up (rewritten) code, more clear information about the problems you're having, and try to ask a clearer question, and we can be more help.


Need Your Help

Android filter listview custom adapter

android listview android-listview

Hello everybody I'm doing an app which has an edittext to search for items on the listview. If the user types a letter. The data come from my json string (database) and then display on my listview....

HTML - expand / collapse based on headings? "document map"

javascript jquery html css

I was wondering what I would need to learn to do this in HTML pages instead of Microsoft Word Docs.