Update error

[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

 

[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

 

[Armin Zingler]

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

 

[Chubbly Geezer]

Works fine..

 

[Chubbly Geezer]

Thanks Armin.

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

Chubbly