Update error

23 Jan 2006 11:17 AM

Chubbly Geezer

Hi guys

I'm getting the following error:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

This is when trying to update a dataset having just created the commands with a command builder. I am basically trying to update records in the Subscriptions table.

My dataset is based on the following string:

"SELECT DISTINCT Subscriptions.* FROM Companies INNER JOIN (People INNER JOIN Subscriptions ON People.PersRef = Subscriptions.InvPersRef) ON (People.CoRef = Companies.CoRef) AND (Companies.CoRef = People.CoRef) WHERE PubCode='AF' AND ((SubStatus='ACTIVE' AND PaymentStatus='PAID') OR (SubStatus='ACTIVE' AND RateCode='FREE')) AND EndIssue<=451 AND (Companies.CompanyType<>'X' or Companies.CompanyType Is Null)"

Any ideas would be useful

thanks

Chubbly

23 Jan 2006 12:25 PM

andy

The select sql looks weird.
Does it work?

I would have expect to see something like
select a.* from a inner join b on a.key = b.key
inner join c on b.key = c.key

23 Jan 2006 2:34 PM

Chubbly Geezer

Works fine..

Show quoteHide quote

"andy" <ao***@lycos.co.uk> wrote in message
news:1138019157.487577.271510@f14g2000cwb.googlegroups.com...
> The select sql looks weird.
> Does it work?
>
> I would have expect to see something like
> select a.* from a inner join b on a.key = b.key
> inner join c on b.key = c.key
>

23 Jan 2006 2:26 PM

Armin Zingler

Show quote Hide quote

"Chubbly Geezer" <chubbly_geezer@newsgroup.nospam> schrieb
> Hi guys
>
> I'm getting the following error:
> Dynamic SQL generation for the UpdateCommand is not supported
> against a SelectCommand that does not return any key column
> information.
>
> This is when trying to update a dataset having just created the
> commands with a command builder. I am basically trying to update
> records in the Subscriptions table.
>
> My dataset is based on the following string:
>
> "SELECT DISTINCT Subscriptions.* FROM Companies INNER JOIN (People
> INNER JOIN Subscriptions ON People.PersRef =
> Subscriptions.InvPersRef) ON (People.CoRef = Companies.CoRef) AND
> (Companies.CoRef = People.CoRef) WHERE PubCode='AF' AND
> ((SubStatus='ACTIVE' AND PaymentStatus='PAID') OR
> (SubStatus='ACTIVE' AND RateCode='FREE')) AND EndIssue<=451 AND
> (Companies.CompanyType<>'X' or Companies.CompanyType Is Null)"
>
> Any ideas would be useful

The message says everything. I wouldn't know how to build an Update SQL from
this Select query.

Use
select * from subscriptions
as the commandtext. Now the table to create the updatecommand for is clear
and the primarykey fields can be retrieved. They are necessary for creating
an updatecommand.

Armin

23 Jan 2006 4:03 PM

Chubbly Geezer

Thanks Armin.

Upon checking my select statement it does indeed return a read only dataset.

Chubbly

Show quoteHide quote

"Armin Zingler" <az.nospam@freenet.de> wrote in message
news:umFVFzCIGHA.1088@tk2msftngp13.phx.gbl...
> "Chubbly Geezer" <chubbly_geezer@newsgroup.nospam> schrieb
>> Hi guys
>>
>> I'm getting the following error:
>> Dynamic SQL generation for the UpdateCommand is not supported
>> against a SelectCommand that does not return any key column
>> information.
>>
>> This is when trying to update a dataset having just created the
>> commands with a command builder. I am basically trying to update
>> records in the Subscriptions table.
>>
>> My dataset is based on the following string:
>>
>> "SELECT DISTINCT Subscriptions.* FROM Companies INNER JOIN (People
>> INNER JOIN Subscriptions ON People.PersRef =
>> Subscriptions.InvPersRef) ON (People.CoRef = Companies.CoRef) AND
>> (Companies.CoRef = People.CoRef) WHERE PubCode='AF' AND
>> ((SubStatus='ACTIVE' AND PaymentStatus='PAID') OR
>> (SubStatus='ACTIVE' AND RateCode='FREE')) AND EndIssue<=451 AND
>> (Companies.CompanyType<>'X' or Companies.CompanyType Is Null)"
>>
>> Any ideas would be useful
>
>
> The message says everything. I wouldn't know how to build an Update SQL
> from
> this Select query.
>
> Use
> select * from subscriptions
> as the commandtext. Now the table to create the updatecommand for is clear
> and the primarykey fields can be retrieved. They are necessary for
> creating an updatecommand.
>
>
>
> Armin
>