Thanks for the ideas BUT
TADOCommand has the same problem
Have tried the same query in Access in it also has the same problem - so the
problem is with the Jet Engine - any experts on this out there.
Steve Galyer
----- Original Message -----
From: "Neven MacEwan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Wednesday, 25 July 2001 17:21
Subject: Re: [DUG]: SQL Update question
> Steve
>
> Ah! I thought you were using SQL :-)
> Firstly since it doesn't return a result set use a TADOCommand (not
> TADOQuery)
> Secondly I'd try the update syntax from within Access (As the Jet Engine
has
> some strange SQL)
>
> HTH
>
> Neven
>
> ----- Original Message -----
> From: "Steve Galyer" <[EMAIL PROTECTED]>
> To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> Sent: Wednesday, 25 July 2001 16:49
> Subject: Re: [DUG]: SQL Update question
>
>
> > Underlying database is MS Access97 accessed via TADOQuery (ADO Express)
> > using Jet 4.
> >
> > RequestLive is not a property of TADOQuery.
> >
> > ExecSQL comes back with the same error.
> >
> > I want to perform the operation over the entire database.
> >
> > ----- Original Message -----
> > From: "Neven MacEwan" <[EMAIL PROTECTED]>
> > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> > Sent: Wednesday, 25 July 2001 16:51
> > Subject: Re: [DUG]: SQL Update question
> >
> >
> > > Steve
> > >
> > > You probably have RequestLive and are Opening the query where
> > > you should be using ExecSQL, What connection method are
> > > you using? Also you should paramerise ClientNumber as you
> > > are updating the whole database (triggers would be easier)
> > >
> > > HTH
> > >
> > > Neven
> > >
> > >
> > > ----- Original Message -----
> > > From: "Steve Galyer" <[EMAIL PROTECTED]>
> > > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> > > Sent: Wednesday, 25 July 2001 16:30
> > > Subject: Re: [DUG]: SQL Update question
> > >
> > >
> > > > This comes back with an error "Operation must use an updateable
query"
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "Neven MacEwan" <[EMAIL PROTECTED]>
> > > > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> > > > Sent: Wednesday, 25 July 2001 16:34
> > > > Subject: Re: [DUG]: SQL Update question
> > > >
> > > >
> > > > > Steve - 3 things
> > > > >
> > > > > 1/ count(*) is prob safer
> > > > > 2/ brackets in the wrong place
> > > > > 3/ Semicolon?
> > > > >
> > > > > Try
> > > > >
> > > > > UPDATE CLIENT
> > > > > SET CLIENT.CardIssuedTotal =
> > > > > (SELECT COUNT(*)
> > > > > FROM CARD
> > > > > WHERE CLIENT.ClientNumber = CARD.ClientNumber)
> > > > >
> > > > > Have you considered doing it in a trigger on CARD?
> > > > >
> > > > > HTH
> > > > >
> > > > > Neven
> > > > >
> > > > >
> > > > >
> > > > > ----- Original Message -----
> > > > > From: "Steve Galyer" <[EMAIL PROTECTED]>
> > > > > To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
> > > > > Sent: Wednesday, 25 July 2001 15:47
> > > > > Subject: [DUG]: SQL Update question
> > > > >
> > > > >
> > > > > I have a Client table and a Card table, each client can have any
> > number
> > > of
> > > > > cards, what I am attempting to achieve is to update a field in the
> > > Client
> > > > > table called CardIssuedTotal with the count of the cards
associated
> > with
> > > > > that client.
> > > > >
> > > > > I am using the following SQL statements, however I am getting an
> error
> > > > > "Parameter CARD.CardNumber has no default value"
> > > > >
> > > > > UPDATE CLIENT
> > > > >
> > > > > SET CLIENT.CardIssuedTotal =
> > > > >
> > > > > (SELECT COUNT(CARD.CardNumber)
> > > > >
> > > > > FROM CARD
> > > > >
> > > > > GROUP BY CARD.ClientNumber)
> > > > >
> > > > > WHERE (CLIENT.ClientNumber = CARD.ClientNumber);
> > > > >
> > > > > Any assistance appreciated.
> > > > >
> > > > >
> > > > >
> > > > > Steve Galyer
> > > > >
> > > > >
> > > > >
> > > >
> > >
> >
>
> --------------------------------------------------------------------------
> > > > -
> > > > > New Zealand Delphi Users group - Delphi List -
> > [EMAIL PROTECTED]
> > > > > Website: http://www.delphi.org.nz
> > > > > To UnSub, send email to: [EMAIL PROTECTED]
> > > > > with body of "unsubscribe delphi"
> > > > > Web Archive at:
http://www.mail-archive.com/delphi%40delphi.org.nz/
> > > > >
> > > >
> > >
> >
>
> --------------------------------------------------------------------------
> > > -
> > > > New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> > > > Website: http://www.delphi.org.nz
> > > > To UnSub, send email to: [EMAIL PROTECTED]
> > > > with body of "unsubscribe delphi"
> > > > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
> > > >
> > >
> >
>
> --------------------------------------------------------------------------
> > -
> > > New Zealand Delphi Users group - Delphi List -
[EMAIL PROTECTED]
> > > Website: http://www.delphi.org.nz
> > > To UnSub, send email to: [EMAIL PROTECTED]
> > > with body of "unsubscribe delphi"
> > > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
> > >
> >
>
> --------------------------------------------------------------------------
> -
> > New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> > Website: http://www.delphi.org.nz
> > To UnSub, send email to: [EMAIL PROTECTED]
> > with body of "unsubscribe delphi"
> > Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
> >
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
> Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
Web Archive at: http://www.mail-archive.com/delphi%40delphi.org.nz/
