How to use parameters in OLE DB Command

Question

Post reply

How to use parameters in OLE DB Command

FreeHansje

SSChampion

Points: 11751
More actions
December 4, 2007 at 7:07 am

#118993

So I have a source, which gives me records with columns. I do my thing and wish to deliver records thru a parameterized OLE DB Command to a table, don't ask why;)...
I would like to do something like
INSERT INTO Tabel1(field1, field2)
SELECT ?,?
I can't write the correct code for this and cannot find an example. Currently I call a SP and pass the parameters there to be inserted:
EXEC usp_InsertTest ?,?
The SP simply does
INSERT INTO Tabel1(field1, field2)
SELECT ?,?
This is an extra step which is unnecessary. How to use parameters to directly insert into the table?
Greetz,
Hans Brouwer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Norman Kelm SSCommitted Points: 1922 More actions · Answer 1

Hans,

How about INSERT INTO Tabel1(field1, field2) VALUES(?, ?)

Norman

DTS Package Search

http://www.dtspackagesearch.com/

FreeHansje SSChampion Points: 11751 More actions · Answer 2

This is horrible, never thought of that... I begin to doubt myseklf:w00t:

So how would I go about using parameters in an update? I would think something like:

UPDATE TABLE

SET Field1 = ?,

Field2 = ?

Tnx for answering.

Greetz,
Hans Brouwer

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Alas Nite_eagle, your solution does not work, I receive a syntax-error.

I really don't get it and could use an example of how to use parameters in an OLE DB Command. It can't be that difficult!!

Basically I have an OLE DB Source, next a Lookup and last an OLE DB Command, where I want to use the output from the Lookup as parameters. When I use EXEC usp_SP ?,? it works fine. If I use

INSERT INTO TABLE

VALUES(?,?)

it does not...I can qualify the fields in the INSERT INTO TABLE, it makes no difference.

If I follow How to: Map Query Parameters to Variables in an Execute SQL Task from BOL I wonder why it does not work like that in OLE DB Command. Should I store my resultset in a temporary table, the use an Execute SQL Task? It would mean an extra step.

Again, if any1 can point me to an example of how to use parameters in OLE DB Command, that would be really helpful.

TIA,

Greetz,
Hans Brouwer

Norman Kelm SSCommitted Points: 1922 More actions · Answer 4

I don't understand why that will not work. I've use this in a parameterized Execute SQL Task successfully. I'll have to try it in the OLEDB Command (haven't used it before) to see if I can get it to work.

There have been other posts on the OLEDB Command here recently, perhaps they can provide some help.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

FreeHansje SSChampion Points: 11751 More actions · Answer 5

Right...

The INSERT works in another package. I was disgusted with the problem package and started to work on something else. And here it works, don't know why. I'll have a new look into the problem package later; now I have seen it working I'm convinced it must be something else I'm doing wrong.

Still having problems with the correct syntax for an UPDATE in an OLE DB COMMAND. Currently I have tried

UPDATE TABLE

SET Field1 = ?,

Field2 = ?

But that yields a syntax/unspecified error.

Another question: can I use a parameter in a CASE statement, like:

Field1 =

CASE WHEN (? IS NULL) OR (? = '0') THEN Field1

ELSE ?

END,

Greetz,
Hans Brouwer

FreeHansje SSChampion Points: 11751 More actions · Answer 6

OK, I got the UPDATE problem figured out as well.

It seems it is not possible to use a CASE statement where you use multiple paranmeter references. It will just increment the parameters and mess up the follow-up references.

Have to find something else to replace the CASE.

Tnx again,

Greetz,
Hans Brouwer