OLe DB Command and Using the value of an input column more than once

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

anydobbo wrote:

Hi there,

I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.

For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -

update tableA

set columnA = ?,

column B = ?

where columnC = ?

AND (columnA != ? OR columnB != ?)

I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.

Yeah, that's what you have to do!

anydobbo wrote:

I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -

declare @.ValueA varchar(50),

@.ValueB varchar(50),

@.ValueC varchar(50)

select @.ValueA = ?,

@.ValueB = ?,

@.ValueC = ?,

update tableA

set columnA = @.ValueA ,

column B = @.ValueB

where columnC = @.ValueC

AND (columnA != @.ValueA OR columnB != @.ValueB)

Any suggestions?

I may be wrong but I don't think you can do that. The OLE DB Command accepts a DML statement but not a statement block (as far as I know).

-Jamie

|||Thanks Jamie