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.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.asString := AdvOfficeStatusBar1.Panels.Text; //Current Date UNIquery2.Params.asString := UniTable1.FieldByName('SHIFT').asString;// Which shift UNIquery2.Params.asString := Uniquery1.FieldByName('TASK').asString;//many tasks UNIquery2.Params.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.
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.