OleDbCommand.Parameters.Add and 'IN' Comparator and MSAccess

  • Thread starter Thread starter mosscliffe
  • Start date Start date
M

mosscliffe

I am using Visual Studio with a MS Access Database

I just can not work out how to create my parameter for the following

Select Fld1, Fld2 from myTable where Fld1 in (?)

The value I wish to add to the parameter collection for Fld1 is '2, 3,
17, 9, 235'

to add a single Parameter I would do the following

cmd.parameters.add("",oledbtype.integer).value = 1

There does not seem to be an OleDbType for an integer array

But I don't have a single value and I do not know how many values,
there will be in the 'IN' query

I also have other parameters in the Selection (not shown) and as
OleDbParameters, seem to be positional, with no way of changing that, I
can't just enter many '?' into the 'IN' part and only enter as many as
I need. In any case what would happen if I had multiple 'IN'
comparators.

Any ideas or suggestions, welcomed
 
masscliffe,

You can only use one value or one field in a parameter, not an array of
those.

Bill (V) is giving an solution for that some messages below.
-------------------------as given by Bill

There are a number of approaches that work, however. First, you can create a
TableValue function that accepts a string (your delimited list of items) and
returns an in-memory table. This table can be use in the IN clause

.... WHERE X IN (SELECT item FROM myTVF(@MyDSParameter)
 
Cor,

Thanks,

I can see what you mean. I had not thought about the IN selection
being a subselect, but I do not understand how you would create a
TableValue function in memory. Help in Visual Studio does not
recognise a TableValue keyword.

Is it possible you could point me to an example of this.

Thanks again for your reply
 
mosscliffe said:
I am using Visual Studio with a MS Access Database

I just can not work out how to create my parameter for the following

Select Fld1, Fld2 from myTable where Fld1 in (?)

The value I wish to add to the parameter collection for Fld1 is '2, 3,
17, 9, 235'

to add a single Parameter I would do the following

cmd.parameters.add("",oledbtype.integer).value = 1

There does not seem to be an OleDbType for an integer array

But I don't have a single value and I do not know how many values,
there will be in the 'IN' query

I also have other parameters in the Selection (not shown) and as
OleDbParameters, seem to be positional, with no way of changing that,
I can't just enter many '?' into the 'IN' part and only enter as many
as I need. In any case what would happen if I had multiple 'IN'
comparators.

Any ideas or suggestions, welcomed
Click to expand...

Access first executes the IN part, so these parameters have to be
enlisted first, then the parameters of the rest of your where clause.
Though you've to add for each value in the IN part a parameter. That's
the way it works.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Thanks for your reply.

I fully understand the IN aspect, what I don't understand is how to
create my subselect table in memory as Cor suggested and referred to as
TableValue.
ie

select x, y from table where x in ( select INvars from memtable)

How do I create 'memtable' in Memory and populate it

Sorry for being so stupid.
 
Back
Top