Arrays as Input to ADO Parameters

[Dynapen]

Here the problem. I need to do a SQL statment similary to the following.

Select * from tableA where field1 in ?

Where the question mark is a call to a ADO paramenter object. MS tells me that I can put arrays into the parameters and that it will work. But I can't find a example of how to create the parameter so I can append it to the command object

commobj.Parameters.Append(commobj.CreateParameter("ID", adArray Or adVarChar, adParamInput, 10, ID_array));

Is the best stuff I have now, but i am getting a error about a expected ) which just doesn't seem right. I am just hoping someone out there as seen this and can show me a example of how to format this command. The money's gone, the brain is shot.....but the liquor we still got.

 

[mkuan]

Are u missing a pair of () to enclose the question mark:

Select * from tableA where field1 in (?)


Min.

 

[Dynapen]

While yes, that would have caused a error, that error would have come from the Oracle Database that I am connected to, not the ADO object itself. The money's gone, the brain is shot.....but the liquor we still got.

 

[JuanitaC]

When I need to pass an array into a stored procedure like this, I usually loop through the array and create a comma delimited string, then pass the string into the stored procedure or sql string, not the array itself.

 

[Dynapen]

Here's the problem with doing that. THen the DB has to hard parse the entire string everytime becuase they don't have it in memory. Using the ADO parameters allows the DB to only have to parse the values of the parameters and not entire SQL string. It can save you time on the DB end.

Also, I am building the SQL statement in line so I don't have a SP to pass the array to. I need to write the array in the place where the (?) is so that the DB receives it like

where field1 in ('1','2','3','4',5') etc....

Someone out there has to know how to do this. The money's gone, the brain is shot.....but the liquor we still got.