Duplicate records check using TQuery

[reisende]

Hi everybody. I am having a very confusing problem trying to check for duplicate records in a table before posting employee time keeping information.

I am using a query to check our existing records in the employee time table when the 'Save Record' button is pressed. The query looks like this:

[tt]SELECT COUNT(*)
FROM "EMP_TIME.DBF" Emp_time
WHERE (Emp_time."date" = :cdate)
AND (prempl = rempl)
AND (total_hours = :total_hours)
AND (job_type = :job_type)
AND (clinic = :clinic)[/tt]

As you can tell I'm trying to count the records where the date, employee id, total hours, job type and clinic are the same as the data the user is trying to submit.

I then have an if statement like this:

[tt]
if dm_global.qry_check_dup.RecordCount > 0 then
begin
MessageDlg('This entry has already been saved',mtWarning,[mbok],0);
frm_input_hours2.Refresh;
end
[/tt]

It checks to see if the record count with the specific data is more than zero. If it is it returns a warning. If it is not, it posts the data.

The problem I'm having is when I test this and try to post data I receive the following error:

[tt]
Project timekeeping.exe raised exception class EDatabaseError with message 'qry_check_dup:Field 'date' not found'
[/tt]

I don't have any problems with the date field in the other queries so I am confused. Does this have something to do with the COUNT function? I've got to be doing something wrong. I'm just not sure where.

Any suggestions would be appreciated.

 

[lespaul]

Is your field really named 'DATE'? I would change that to TimeDate or ClockDate or something else, never name a field 'date' is almost ALWAYS a reserved word or function in any language.

Do you have a table or database component on the form somewhere? You shouldn't have to declare you DBF name like that in your query either.

Are you using the BDE or ADO?

leslie

 

[reisende]

Yeah, the field is named 'date'. It represents the date the employee worked and I need to check that so we don't get duplicate hours entered into our time keeping system. I didn't design the table so I'm stuck with the name.

The declaration for the DBF is coming from SQL builder as I used that to construct the query. And I am using BDE.

The query is located in a Data Module (the dm_global) and I have the data source for the query set to ds_emp_time which is the DS for the employee time table.

I didn't design this program but am now mainly responsible for its upkeep. I haven't had any problems, though, until with this query.

I hope this is enough info.

 

[lespaul]

Ok, do you have a database component? What kind of database is it?

the query should reference the database component module
the datasource should have the qry as it's source (but only if you are using the information for display, if you anen't displaying the data in DB components I you don't even need the datasource).

So:

dm_global should have a database component
qry should have database component listed in the DatabaseName property

since all you are doing is the count for verification I would drop the datasource and check more like this:

with dm_global.qryCheckRecord do
begin
  SQL.Clear;
  SQL.Add('SELECT * FROM Emp_Time Where Emp_Time."date" = ' + Form1.DateTimePicker.Date + ' AND PREMPL = ' + Form1.PremplFormComponent.Something + ' AND TotalHours = ' + Form1.TotalHoursComponent.Something + ' AND JobType = ' + Form1.JobType.Something + ' AND Clinic = ' + Form1.Clinic.Something);
  Active := True;
  If isempty then
    //post your new entry
  else
  begin
    ShowMessage('This entry already exists!');
    Exit;
  end;
end;

Leslie

 

[reisende]

I didn't think I needed the DataSource because I'm not displaying anything, I'm just used to pairing everything up with a data source and probably got a little confused with unfamiliar code .

Let me give your suggestion a try and I'll post the results later on.

Thanks.

 

[reisende]

It was a struggle, but I managed to get it working. I was far off originally I just had to add the COUNT to the fields editor and then change RecordCount to COUNT.AsFloat.

Works great now.

Thanks for you suggestions and your time, Leslie. It was much appreciated .