Best approach selecting Keys and Update another table lazarus / 16-Databases ---------------------------------------------------------------------- jack · 2012-02-10 03:20 When I am stymied, I find this community most helpful,  I would appreciate your opinion again. I have created a simple subset of my application so I could ask for help in understanding the best way to go about it.  In my payroll application, I want to create a form that prompts the user to select first the date on which the work is done and then the employee who does the work.  I Query the EMPLOYEE Table to create the list of employees into a TDBLookupComboBox and use TDBCalendar to select the date.  The Date and Employee ID are the key to the PAYDATA Table where I want a row for every Employee-Day worked.  In setting this up I put a simple SELECT * FROM PAYDATA; into the PAYDATA TSQLQuery because I didn't know what else to do.  I came along later and tried to stuff the real query into the TSQLQuery with the Date and Employee ID in a WHERE Clause.  TWO Things Jumped up and caused me to seek help.  First was the presence of both an UpdateSQL and an InsertSQL Property on the TSQLQuery which I am not sure how to use. Second was finding myself manually coding the SQL for these same three operations.  Then finding myself tied in knots while trying to stuff them in at the right time and in the proper sequence of enabling and disabling various properties. =========================== None of this strikes me as rapid application development; therefore, I must be going about it wrong! What is the more eloquent way to go about this? Is there some way to use variable values in the little popup SQL window that the TSQLQuery  properties list provides for all three queries? Thanks again. ---------------------------------------------------------------------- BigChimp · 2012-02-10 07:46 Quote from: jack on February 10, 2012, 04:20:08 am[...] Excellent explanation, very clear! Quote from: jack on February 10, 2012, 04:20:08 amFirst was the presence of both an UpdateSQL and an InsertSQL Property on the TSQLQuery which I am not sure how to use. http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#TSQLQuery.In sertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_P arameters If that's not clear, tell me what's wrong and I can update the wiki... Quote from: jack on February 10, 2012, 04:20:08 amSecond was finding myself manually coding the SQL for these same three operations.  Then finding myself tied in knots while trying to stuff them in at the right time and in the proper sequence of enabling and disabling various properties. =========================== None of this strikes me as rapid application development; therefore, I must be going about it wrong! What is the more eloquent way to go about this? Is there some way to use variable values in the little popup SQL window that the TSQLQuery  properties list provides for all three queries? Thanks again. Edit: haven't looked at your code; responded based on your post only... Maybe an obvious one, but what happens if you start again with the SQL property set to (air code, OTTOH, not tested) Code: [Select]SELECT * FROM PAYDATA WHERE WORKDATE=:WORKDATE AND EMPLOYEEID=:EMPLOYEEID Does it work? (I've never tried something like that so that's why I'm asking) (You'd have to set the parameters, of course) and generate updates/inserts based on that? (See the wiki page, but I think the convention was to name the parameters the same as the field names) If not, you'd probably have to hand code insertsql, deletesql and updatesql as you said, sorry (unless people like Ludob come on the forum to correct me again*) - that's a very real possibility)... Thinking about it out loud, it doesn't sound as though dealing with these parameters in the automatic insertsql/updatesql/deletesql case should be extremely hard, but that's just from an amateur's point of view... Good luck, BigChimp (BTW: I hope you don't have a column called DATE as that's a reserved word in a lot of databases and will need special handling) *)Edit: not that that is a bad thing, and it perhaps got him to bite ---------------------------------------------------------------------- ludob · 2012-02-10 11:26 First a few comments: - TDBLookupComboBox and TDBLookupListBox in fpc are not made for changing the cursor in a dataset but only to fill in a field in an edited record in a dataset by using a lookup field in another dataset (or a fixed list). What you want to do is to position you cursor in PAYDATA by selecting an employee and a date. - adding a where clause to SQL does not mean you have to use InsertSql, etc. As long as your SQL is using only one table and you have a primary key in that table, you don't have to manually create InsertSQL, etc.  Here is possible solution: -query2: select * from PAYDATA where ID_EMPLOYEE=:idemp and PAYDATE=:pdate -datasource2 connected to query2 -dbgrid or whatever dbcontrols to visualise/edit data connected to datasource2 -query1: select * from employee, OnAfterScroll: Code: [Select]SQLQuery 2.ParamByName('idemp').AsInteger:=SQLQuery1.FieldByName('ID').AsIntege r; SQLQuery2.Refresh;-datasource1 connected to query1 -dbgrid using datasource1 showing only name -TCalendar, OnChange:Code: [Select]SQLQuery2.ParamByName('pdate').AsDate:=Calendar1.DateTime; SQLQuery2.Refresh; When the employee/date record exists it is loaded, when it isn't you are creating a new one. Before posting the new record, fill ID_EMPLOYEE and PAYDATE fields from SQLQuery1 and Calendar1. Also, open SQLQuery2 before SQLQuery1 otherwise the SQLQuery2.Refresh will complain about inactive dataset. That is RAD ---------------------------------------------------------------------- jack · 2012-02-10 13:24 Thanks for the thoughts, I see a glimmer of what I missed.