Devart Forums

I have Delphi 7 and mydac version 3.55.0.21 30.05.05
sometimes i do a refreshrecord and i got the following sql request:
Select tim_id, tim_Ref, grp_id, erinnerung, termin, sys_arc.arc_id, arc_name, doc_id, doc_titel, info, status, usr_id_work, Optionen from sys_timer left join sys_arc using(arc_id) where usr_id_work = 15 and (( status 2)) and ((Termin between '2007-06-25 00:00:00' and '2007-07-01 23:59:59' ) or (Termin < '2007-06-25 00:00:00'))
AND sys_timer.tim_id = :Old_1 order by termin

it returns properly

on a similar case (i am not that familiar with the code)
i use refreshrecord and the command is:
Select Sql_No_cache tim_id, tim_ref, sys_timer.grp_id, termin, sys_arc.arc_id, arc_name, doc_id, doc_titel, if(grp_name is not null, grp_name, usr_name) as usr_name,erinnerung, info, status, usr_id_work from sys_timer left join sys_arc using(arc_id) left join sys_usr on sys_usr.usr_id = sys_timer.usr_id_work left join sys_grp on sys_grp.grp_id = sys_timer.grp_id where (usr_id_work = 15 or usr_id_shed = 15)
AND sys_timer.tim_id = :Old_1 AND sys_timer.tim_ref = :Old_2 AND sys_timer.grp_id = :Old_3 AND sys_timer.termin = :Old_4 AND sys_timer.doc_id = :Old_7 AND sys_timer.doc_titel = :Old_8 AND sys_timer.usr_name = :Old_9 AND sys_timer.erinnerung = :Old_10 AND sys_timer.info = :Old_11 AND sys_timer.status = :Old_12 AND sys_timer.usr_id_work = :Old_13 group by tim_ref order by termin


now it throws an exception of the class EMySqlExcaption:
Unknown Column 'sys_timer.usr_name' in 'where clause'

if i set

dm_DB.my_q_time.SQLRefresh.Text :=dm_DB.my_q_time.SQL.Text;
try
dm_DB.my_q_time.RefreshRecord;
except;
end;
i get always an exception of the calss EDatabaseError:
'refresh failed. Found 4 records.'

What am i doing wrong?

The RefreshRecord cannot work with such comlicated queries. You should provide the SQL query for the SQLRefresh property yourself. This query should return only one record with the same field names like the open dataset has.

Antaeus wrote:The RefreshRecord cannot work with such comlicated queries. You should provide the SQL query for the SQLRefresh property yourself. This query should return only one record with the same field names like the open dataset has.

Thx for the reply!
I understand that this is a complicated query, does mydac 5 handle it?

As i said i tried already SQLRefresh.text but it told me:
exception of the calss EDatabaseError:
'refresh failed. Found 4 records.'

What does it mean? How can i fix it?
Thx again!

iskywalker wrote:I understand that this is a complicated query, does mydac 5 handle it?

No, it does not.

iskywalker wrote:What does it mean?

The SQLRefresh query should return only one record to allow MyDAC update values of the current record in dataset with values of the selected record. When SQLRefresh returns more than one record (four in your case), MyDAC "does not know" which of selected record to use to update values of the current record in dataset.

iskywalker wrote:How can i fix it?

You should provide the correct SELECT statement for the SQLRefresh property. You can either compose it manually, or create it by MyDAC SQL Generator at design time and fix generated query.

Thx it worked, but i must set the refresh to giveme the exactly row i wanted,
so if i to actualize more rows at the same time i must search for the selected ones.

This is not an optimal solution,, but it is a solution.
Thx again!