Re: Alter table and update with ADO
- From: "Guillem" <guillemvicens-nospam@xxxxxxxxxxxxxxxxxx>
- Date: 26 Aug 2005 00:09:29 -0700
Hi,
Richard wrote:
> Hello,
>
> I am using an ADO SQLQuery to check to see if structure updates are
> needed for a database and apply them if they are needed. In one case
> I am checking to see if a field exists in a table, and if not I add
> the field and then I need to update the new field to contain data
> from another table (this isn't going to be a foreign key by the way).
> But the query errors out saying the field doesn't exist. Here's the
> statement:
>
> if not exists (SELECT *
> FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('TABLENAME')
> AND Name = 'NEWCOL'
> )
> begin
> alter table [TABLENAME]
> add NEWCOL integer;
>
> Update tablename set newcol=(select X from Othertable where
> y=tablename.y); end;
are you sure this statement should work? It looks like PL/SQL to me,
and AFAIK not all RDBMS support that. Have you tried to do it from your
database console?
>
> The query fails with 'Field newcol not found'. If I run this in
> Query Analyzer I could use the 'Go' statements to make sure each
> statement is executed when it needs to be so this doesn't happen, but
> I don't know of an equivelent using ADO. Is there an equivalent or
> is there another way to write this sql statement so this will work?
> Thanks!
an ADOQuery will try to return a result. It's specifically designed for
that purposes, not for altering databases. I would suggest to try with
either an ADODataset or an ADOCommand
--
Best regards :)
Guillem Vicens
Dep. Informática Green Service S.A.
www.clubgreenoasis.com
--
in order to contact me remove the -nospam
.
- References:
- Alter table and update with ADO
- From: Richard
- Alter table and update with ADO
- Prev by Date: Re: Alter table and update with ADO
- Next by Date: TAdoQuery.LoadFromFile with D7-Access97
- Previous by thread: Re: Alter table and update with ADO
- Next by thread: TAdoQuery.LoadFromFile with D7-Access97
- Index(es):
Relevant Pages
|
