Re: Alter table and update with ADO
- From: "Jon Jacobs" <JonJacobsAtcomcast.net>
- Date: Thu, 25 Aug 2005 17:26:34 -0500
Perhaps you could make your query return a value, 1 if you had to add the column, and 0 if not. You could then use
Open/Close instead of ExecSQL. Based on the value, you could then call (or not) a separate query that does the update.
Declare @ColAdded Bit
if not exists ...
begin
Alter Table ...
Set @ColAdded = 1
end else
Set @ColAdded = 0
Select @ColAdded as ColAdded
Then in Delphi your code could look something like this...
if qryTryAdd.FieldByName('ColAdded').AsBoolean = True then
qryUpdateNewCol.ExecSQL;
HTH,
Jon
--- Richard wrote on 08/25/2005 09:30:48 :
>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;
>
>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!
>
>
.
- References:
- Alter table and update with ADO
- From: Richard
- Alter table and update with ADO
- Prev by Date: Re: How do I capture Delphi 5 TADODataset timeout error consistently?
- Next by Date: Re: Alter table and update with ADO
- Previous by thread: Alter table and update with ADO
- Next by thread: Re: Alter table and update with ADO
- Index(es):
Relevant Pages
|
