Caching problem???

[Stewart Saathoff]

Hello,

I have a problem and I am not sure but it may be related to the way the system caches information. I have a web service that retrieves data from a database. The select statement is quite simple and I will include it below:

SELECT departments.department, employees.clockedin, employees.FName, [time].ireason, [time].oreason, [time].oDate
FROM ((departments INNER JOIN employees ON departments.departmentid = employees.departmentid)
INNER JOIN [time] ON employees.employeeid = [time].employee)
WHERE [time].idate=(SELECT max(idate) from [time] where employee=employees.employeeid)
ORDER BY employees.FName

The problem is this. When I retrieve the records in the database, the dataset is generated like its supposed to. (The most recent date record is retrieved.) If I use the application after I make some mods to the records, it appears to append new records rather than re-running a fresh query.

Here is the code for my Web Method:

cn.Open()
DsEmpStatus1.Clear()
Me.daEmpStatus.Fill(DsEmpStatus1.employees)
cn.Close()
cn.Dispose()
Return DsEmpStatus1

 

[Stewart Saathoff]

Here is more information, it may help. When I first run this web service, it generates a list of all latest records in the database. When I add a new record and rerun the procedure, the system responds with all the latest records and adds an additional record for each new record. It is more like it is appending new records rather than running a new query.

Can someone help?

Stewart
Hello,

I have a problem and I am not sure but it may be related to the way the system caches information. I have a web service that retrieves data from a database. The select statement is quite simple and I will include it below:

SELECT departments.department, employees.clockedin, employees.FName, [time].ireason, [time].oreason, [time].oDate
FROM ((departments INNER JOIN employees ON departments.departmentid = employees.departmentid)
INNER JOIN [time] ON employees.employeeid = [time].employee)
WHERE [time].idate=(SELECT max(idate) from [time] where employee=employees.employeeid)
ORDER BY employees.FName

The problem is this. When I retrieve the records in the database, the dataset is generated like its supposed to. (The most recent date record is retrieved.) If I use the application after I make some mods to the records, it appears to append new records rather than re-running a fresh query.

Here is the code for my Web Method:

cn.Open()
DsEmpStatus1.Clear()
Me.daEmpStatus.Fill(DsEmpStatus1.employees)
cn.Close()
cn.Dispose()
Return DsEmpStatus1

 

[Stewart Saathoff]

I found the problem, but I still need assistance. It appears that I am pulling from the idate column. There is also an iTime column that stores the Time value separately. Is there a way to select the record that is the maximum date and time from two separate rows in a record?
Hello,

I have a problem and I am not sure but it may be related to the way the system caches information. I have a web service that retrieves data from a database. The select statement is quite simple and I will include it below:

SELECT departments.department, employees.clockedin, employees.FName, [time].ireason, [time].oreason, [time].oDate
FROM ((departments INNER JOIN employees ON departments.departmentid = employees.departmentid)
INNER JOIN [time] ON employees.employeeid = [time].employee)
WHERE [time].idate=(SELECT max(idate) from [time] where employee=employees.employeeid)
ORDER BY employees.FName

The problem is this. When I retrieve the records in the database, the dataset is generated like its supposed to. (The most recent date record is retrieved.) If I use the application after I make some mods to the records, it appears to append new records rather than re-running a fresh query.

Here is the code for my Web Method:

cn.Open()
DsEmpStatus1.Clear()
Me.daEmpStatus.Fill(DsEmpStatus1.employees)
cn.Close()
cn.Dispose()
Return DsEmpStatus1

 

[Jonathan Levine]

I found the problem, but I still need assistance. It appears that I am
pulling from the idate column. There is also an iTime column that
stores the Time value separately. Is there a way to select the record
that is the maximum date and time from two separate rows in a record? [...]
SELECT departments.department, employees.clockedin, employees.FName,
[time].ireason, [time].oreason, [time].oDate
FROM ((departments INNER JOIN employees ON departments.departmentid
= employees.departmentid)
INNER JOIN [time] ON employees.employeeid = [time].employee)
WHERE [time].idate=(SELECT max(idate) from [time] where
employee=employees.employeeid)
ORDER BY employees.FName

[...]

Stewart,

If the iDate and iTime columns are stored as varchar or char, and
you have permission to change the table or add a view, you could
create a calculated field called iDateTime that would be the
concatenation of the two columns (e.g. cast(iDate + ' ' + iTime as
datetime))

If not, you might have to join the time table to itself. This
isn't the nearly the most efficient way to do it, but the general idea
is:

.... INNER JOIN [time] t1 ON employees.employeeid = t1.employee
INNER JOIN [time] t2 ON t1.employee = t2.employee and t1.iDate =
t2.iDate...

WHERE ... t2.itime = (SELECT max(itime) from [time] t3 where
employee = employees.employeeid and t3.idate = t1.idate) ...

Regards,

Jonathan

 

[Stewart Saathoff]

This does not work. Is there a better way to combine date and time values?

I found the problem, but I still need assistance. It appears that I am
pulling from the idate column. There is also an iTime column that
stores the Time value separately. Is there a way to select the record
that is the maximum date and time from two separate rows in a record? [...]
SELECT departments.department, employees.clockedin, employees.FName,
[time].ireason, [time].oreason, [time].oDate
FROM ((departments INNER JOIN employees ON departments.departmentid
= employees.departmentid)
INNER JOIN [time] ON employees.employeeid = [time].employee)
WHERE [time].idate=(SELECT max(idate) from [time] where
employee=employees.employeeid)
ORDER BY employees.FName

[...]

Stewart,

If the iDate and iTime columns are stored as varchar or char, and
you have permission to change the table or add a view, you could
create a calculated field called iDateTime that would be the
concatenation of the two columns (e.g. cast(iDate + ' ' + iTime as
datetime))

If not, you might have to join the time table to itself. This
isn't the nearly the most efficient way to do it, but the general idea
is:

... INNER JOIN [time] t1 ON employees.employeeid = t1.employee
INNER JOIN [time] t2 ON t1.employee = t2.employee and t1.iDate =
t2.iDate...

WHERE ... t2.itime = (SELECT max(itime) from [time] t3 where
employee = employees.employeeid and t3.idate = t1.idate) ...

Regards,

Jonathan

 

[Jonathan Levine]

Stewart,

Wasn't sure which thing (or both) didn't work.

is iTime a datetime column?

-- Jonathan

This does not work. Is there a better way to combine date and time values?

I found the problem, but I still need assistance. It appears that I am
pulling from the idate column. There is also an iTime column that
stores the Time value separately. Is there a way to select the record
that is the maximum date and time from two separate rows in a record? [...]
SELECT departments.department, employees.clockedin, employees.FName,
[time].ireason, [time].oreason, [time].oDate
FROM ((departments INNER JOIN employees ON departments.departmentid
= employees.departmentid)
INNER JOIN [time] ON employees.employeeid = [time].employee)
WHERE [time].idate=(SELECT max(idate) from [time] where
employee=employees.employeeid)
ORDER BY employees.FName

[...]

Stewart,

If the iDate and iTime columns are stored as varchar or char, and
you have permission to change the table or add a view, you could
create a calculated field called iDateTime that would be the
concatenation of the two columns (e.g. cast(iDate + ' ' + iTime as
datetime))

If not, you might have to join the time table to itself. This
isn't the nearly the most efficient way to do it, but the general idea
is:

... INNER JOIN [time] t1 ON employees.employeeid = t1.employee
INNER JOIN [time] t2 ON t1.employee = t2.employee and t1.iDate =
t2.iDate...

WHERE ... t2.itime = (SELECT max(itime) from [time] t3 where
employee = employees.employeeid and t3.idate = t1.idate) ...

Regards,

Jonathan