For the Views and the Stored Procedures (SP), there are absolutely no
difference for the update: ADP will do it in exactly the same way for both
of them.
The Resync command is used by ADP after the update is done to refresh the
data: it is essentially the same SP as the SP used as the source for the
form but with only a single (usually) parameter - which is the primary key -
and that return all the field for the current record.
For example, if the following SP is the record source for the form:
Create Procedure dbo.Source (@a int, @b int) as
Select ID, V1, V2, V3 from table T where T.a >= @a and T.b <= @b
then the following could be used as the resync command:
Create Procedure dbo.Source_Resync (@ID int) as
Select ID, V1, V2, V3 from table T Where T.ID = @ID
and the Resync property will be: « Source_Resync ? » (without the quote).
For another example, see:
http://msdn.microsoft.com/library/de...dateresync.asp
Don't forget to set the Unique Table property, too.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
"C.W." <c.w@nospam_com> wrote in message
news:u%(E-Mail Removed)...
> Hi Silvain
>
> Really appreciate the help. I had to kick myself when I looked at the
> trigger again, and realised that I did force to trigger to run only if the
> update query is issued out of an office application, and hence there
> wasn't a problem when I issue the update command from query analyser.
>
> Now can you help me with using the resync and parameter options of forms?
> I looked at the documention and couldn't figure out how they can be used.
> The reason I used a view is because I couldn't figure out how to update
> back to the database when data is retrieved through stored proc.
>
> Many thanks for your help
>
> Regard
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:(E-Mail Removed)...
>> From the test « where program_name like 'microsoft office%' ... »; I
>> don't think that much of this trigger is running when you run this script
>> in the query analyser.
>>
>> Have you took the precaution of setting a Resync Command? Otherwise, ADP
>> will make independant requeries for all tables and this could lead to a
>> deadlock because these commands are run asynchroneously.
>>
>> Does this problem occurs only when you are closing the form or if it
>> happens too when you are moving from record to record?
>>
>> You could also try with an INSTEAD OF Trigger for your view (personally,
>> I never used them with ADP but we never know).
>>
>> Finally, by default, ADP opens three connections to the SQL-Server, not
>> just one; so it's quite possible that the self blocking connection might
>> be two different connections instead.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: http://cerbermail.com/?QugbLEWINF
>>
>>
>> "C.W." <c.w@nospam_com> wrote in message
>> news:(E-Mail Removed)...
>>>I have got an updateable snapshot based on a view. When close the form,
>>>it automatically sends out the following script to update the underlying
>>>table.
>>>
>>> exec sp_executesql N'UPDATE "arpos".."tafe_hdr" SET "ref2"=@P1 WHERE
>>> "seqno"=@P2 AND "created_date"=@P3 AND "enrolmentno"=@P4 AND
>>> "ord_ref"=@P5 AND "ref1"=@P6 AND "ref2"=@P7 AND "notes" IS NULL AND
>>> "invno"=@P8', N'@P1 varchar(6),@P2 int,@P3 datetime,@P4 varchar(12),@P5
>>> varchar(4),@P6 varchar(10),@P7 varchar(4),@P8 int', 'testts', 246, 'Feb
>>> 3 2003 12:00:00:000AM', 'p02062693446', 'test', '0203000085', 'test',
>>> 246
>>>
>>> This screen in ADP simply times out. In SQL Server locks, I can see that
>>> this connection is blocked by itself?! (how is that possible).
>>>
>>> I run the same script in query analyser, the script completes under 1
>>> sec.
>>>
>>> Any idea what is going on?
>>>
>>> I have a trigger on the underlying table that is being updated
>>>
>>> CREATE TRIGGER ASAS_UPD_TAFE_HDR ON [dbo].[TAFE_HDR]
>>> FOR UPDATE
>>> AS
>>> if exists (select * from master.dbo.sysprocesses where program_name like
>>> 'microsoft office%' and spid=@@spid)
>>> begin
>>> update
>>> a
>>> set
>>> a.transdate=b.created_date,
>>> a.ref2=b.enrolmentno,
>>> a.x_enrol_id=b.enrolmentno,
>>> a.ord_ref=b.ord_ref,
>>> a.ref1=b.ref1
>>> from
>>> dr_trans a inner join inserted b on
>>> a.jobno=b.seqno
>>>
>>>
>>> insert into asas_audit
>>> select
>>> 'Update Invoice Header',
>>> system_user,
>>> host_name(),
>>> getdate(),
>>> invno
>>> from
>>> inserted
>>> end
>>>
>>> I don't think it makes any difference. I have included it just in case
>>> if anyone can spot any problem here.
>>>
>>> Thanks in advance
>>>
>>>
>>
>>
>
>
Similar Threads
