BorlandTalk.com Forum Index BorlandTalk.com
Borland discussion newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

RefreshRecord

 
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> Delphi Databases (ADO)
View previous topic :: View next topic  
Author Message
Vuksa Vladislav
Guest
PostPosted: Wed Jan 18, 2006 8:57 am    Post subject: RefreshRecord Reply with quote


Example:

Select C.CityID, C.Name, S.StateID, S.Name As NameOfState

From City As C

Left Join State As S On (S.StateID=C.StateID)

If I change S.StateID I want to refresh S.Name. I do not want to use requery
'cos it is too slow.

I have tried:

ADO components using properties Recordset.Properties['Resync Command'] and
Recordset.Resync(adAffectCurrent, adResyncAllValues)

TBetterAdo - works with some simple examples, but if I use Join with View
containing SUM it does not work

DbExpress components have implemented RefreshRecord which doesn't work with
Join

I have not succeeded to find any reasonable solution.



Back to top
John Herbster
Guest
PostPosted: Wed Jan 18, 2006 10:40 am    Post subject: Re: RefreshRecord Reply with quote


Vuksa,

Please do not multipost. Thank you.

--
JohnH
newsgroup list & guidelines http://info.borland.com/newsgroups/

Back to top
Vitali Kalinin
Guest
PostPosted: Wed Jan 18, 2006 11:47 am    Post subject: Re: RefreshRecord Reply with quote


This specific case looks like a proper candidate for using Lookup field.


Back to top
Vuksa Vladislav
Guest
PostPosted: Thu Jan 19, 2006 7:33 am    Post subject: Re: RefreshRecord Reply with quote
OK, but I thought that it concernd all those groups.

"John Herbster" <herb-sci1_AT_sbcglobal.net> wrote

Quote:
Vuksa,

Please do not multipost. Thank you.

--
JohnH
newsgroup list & guidelines http://info.borland.com/newsgroups/




Back to top
Vuksa Vladislav
Guest
PostPosted: Thu Jan 19, 2006 7:40 am    Post subject: Re: RefreshRecord Reply with quote
I have written resync command well and it works on this simple example,
but if I use Join with View containing SUM it does not work.

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Quote:

Example:
Select C.CityID, C.Name, S.StateID, S.Name As NameOfState
From City As C
Left Join State As S On (S.StateID=C.StateID)
If I change S.StateID I want to refresh S.Name. I do not want to use
requery
'cos it is too slow.
I have tried:
ADO components using properties Recordset.Properties['Resync Command'] and
Recordset.Resync(adAffectCurrent, adResyncAllValues)

TBetterAdo - works with some simple examples, but if I use Join with View
containing SUM it does not work

DbExpress components have implemented RefreshRecord which doesn't work
with
Join
You seem to have to the right code. My guess is that you have not
correctly
written the "Resync command"

for your example it would be
Select C.CityID, C.Name, S.StateID, S.Name As NameOfState
From City As C
Left Join State As S On (S.StateID=C.StateID)
Where C.CityID = ?

Not that if you are using TadoBetterDataset it uses the recordset refresh
equivalent to your code by default when you do a AdoDataset.refresh
and it has a Resync_command property
--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]



Back to top
Vuksa Vladislav
Guest
PostPosted: Thu Jan 19, 2006 7:42 am    Post subject: Re: RefreshRecord Reply with quote
Lookup field is very slow.

"Vitali Kalinin" <vitkalinin (AT) yahoo (DOT) com> wrote

Quote:
This specific case looks like a proper candidate for using Lookup field.




Back to top
Vuksa Vladislav
Guest
PostPosted: Thu Jan 19, 2006 7:45 am    Post subject: Re: RefreshRecord Reply with quote
If I use Join with View containing SUM the error message is:
Insufficient key column information for updating or refreshing.


"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Quote:

Example:
Select C.CityID, C.Name, S.StateID, S.Name As NameOfState
From City As C
Left Join State As S On (S.StateID=C.StateID)
If I change S.StateID I want to refresh S.Name. I do not want to use
requery
'cos it is too slow.
I have tried:
ADO components using properties Recordset.Properties['Resync Command'] and
Recordset.Resync(adAffectCurrent, adResyncAllValues)

TBetterAdo - works with some simple examples, but if I use Join with View
containing SUM it does not work

DbExpress components have implemented RefreshRecord which doesn't work
with
Join
You seem to have to the right code. My guess is that you have not
correctly
written the "Resync command"

for your example it would be
Select C.CityID, C.Name, S.StateID, S.Name As NameOfState
From City As C
Left Join State As S On (S.StateID=C.StateID)
Where C.CityID = ?

Not that if you are using TadoBetterDataset it uses the recordset refresh
equivalent to your code by default when you do a AdoDataset.refresh
and it has a Resync_command property
--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]



Back to top
Vuksa Vladislav
Guest
PostPosted: Mon Jan 23, 2006 2:23 pm    Post subject: Re: RefreshRecord Reply with quote

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Quote:

If I use Join with View containing SUM the error message is:
Insufficient key column information for updating or refreshing.
Sounds like your View isn't indexed.
Refreshing requires an index
--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]

Indexing didn't help.

Many conditions must be satisfied to index View, and when I finally
succeeded I got the same error message.

Full example Delphi 7 and SQL server 2000 :

----------------------------------------------------------------------------------------------------------------------------------------------------

-- SQL

----------------------------------------------------------------------------------------------------------------------------------------------------

Create Database Test

Go

Use Test

Go

CREATE TABLE [TestSKomitent] (

[SKomitent] [int] NOT NULL ,

[Naziv] [nvarchar] (80) COLLATE Croatian_CI_AS NOT NULL , CONSTRAINT
[PK_TestSKomitent] PRIMARY KEY CLUSTERED (

[SKomitent]

) ON [PRIMARY]

) ON [PRIMARY]

GO



Insert Into TestSKomitent(SKomitent, Naziv) Values(1,'test 1') Insert Into
TestSKomitent(SKomitent, Naziv) Values(2,'test 2') GO

CREATE TABLE [TestFaktura] (

[BrojFakture] [int] IDENTITY (1, 1) NOT NULL , [SKomitent] [int] NOT NULL ,
CONSTRAINT [PK_TestFaktura] PRIMARY KEY CLUSTERED (

[BrojFakture]

) ON [PRIMARY] ,

CONSTRAINT [FK_TestFaktura_TestSKomitent] FOREIGN KEY (

[SKomitent]

) REFERENCES [TestSKomitent] (

[SKomitent]

) ON UPDATE CASCADE

) ON [PRIMARY]

GO



Insert Into TestFaktura(SKomitent)

Values(1)

Go

CREATE TABLE [TestFakturaStavke] (

[BrojStavke] [int] NOT NULL ,

[BrojFakture] [int] NOT NULL ,

[SRoba] [int] NOT NULL ,

[Iznos] [numeric](15, 2) NOT NULL ,

CONSTRAINT [PK_TestFakturaStavke] PRIMARY KEY CLUSTERED (

[BrojStavke]

) ON [PRIMARY] ,

CONSTRAINT [FK_TestFakturaStavke_TestFaktura] FOREIGN KEY (

[BrojFakture]

) REFERENCES [TestFaktura] (

[BrojFakture]

) ON UPDATE CASCADE

) ON [PRIMARY]

GO

CREATE View viewTestFaktura As

Select

Fak.BrojFakture,

K.Naziv As SKomitentNaziv,

IsNull(FS.UkupanIznos,0) As UkupanIznos From TestFaktura As Fak Left Join
( Select FS.BrojFakture, Sum(FS.Iznos) As UkupanIznos From TestFakturaStavke
As FS Group By FS.BrojFakture

) As FS On (FS.BrojFakture=Fak.BrojFakture) Left Join TestSKomitent As K On
(K.SKomitent=Fak.SKomitent) Go

----------------------------------------------------------------------------------------------------------------------------------------------------

-- Source

----------------------------------------------------------------------------------------------------------------------------------------------------

procedure TForm1.FormCreate(Sender: TObject); begin

AdoQuery3.Open;

end;

procedure TForm1.ADOQuery3BeforeOpen(DataSet: TDataSet); begin

ADOQuery3.SQL.Clear;

ADOQuery3.SQL.Add('Select');

ADOQuery3.SQL.Add(' Fak.BrojFakture,');

ADOQuery3.SQL.Add(' Fak.SKomitent, V.SKomitentNaziv,');

ADOQuery3.SQL.Add(' V.UkupanIznos');

ADOQuery3.SQL.Add('From TestFaktura As Fak');

ADOQuery3.SQL.Add('Left Join viewTestFaktura As V On
(V.BrojFakture=Fak.BrojFakture)');

end;

procedure TForm1.ADOQuery3AfterOpen(DataSet: TDataSet); begin

ADOQuery3.Properties['Unique Catalog'].Value := 'Test';

ADOQuery3.Properties['Unique Table'].Value := 'TestFaktura';

ADOQuery3.Properties['Resync Command'].Value := ADOQuery3.SQL.Text+#13+

'Where Fak.BrojFakture=?'

End;

procedure TForm1.ADOQuery3BeforePost(DataSet: TDataSet); begin

ADOQuery3.Properties['Update Resync'].Value := adResyncUpdates; end;

procedure TForm1.ADOQuery3AfterPost(DataSet: TDataSet); begin

ADOQuery3.Recordset.Resync(adAffectCurrent, adResyncAllValues); end;





----------------------------------------------------------------------------------------------------------------------------------------------------

-- Error

----------------------------------------------------------------------------------------------------------------------------------------------------

If you try to change field SKomitent from ADOQuery3 from 1 to 2 you'll get
message:

Insufficient key column information for updating or refreshing.





Back to top
Vuksa Vladislav
Guest
PostPosted: Tue Jan 24, 2006 7:51 am    Post subject: Re: RefreshRecord Reply with quote
My querys are mutch more complicated from this test example and I have to
use view.

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Quote:
Indexing didn't help.

Many conditions must be satisfied to index View, and when I finally
succeeded I got the same error message.

Then I would advise to not use a view.


--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]



Back to top
Display posts from previous:   
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> Delphi Databases (ADO) All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.