ADO Command Object with Array Parameter

[Chris Kettenbach]

Has anyone had any success passing an adArray arg to a command object
parameter for an IN Clause?
As in "SELECT * FROM tmp1 WHERE MyID IN ?"

I successfully open the connection object, and I can append the param the
the parameters collection but I can not get the result I am looking for.

I am using VBScript

dim cmd
set cmd = createObject("ADODB.Command")

const adArray = xH2000
const adInteger = 5
const adParamInput = 1

dim a
a(0) = 4
a(1) = 5
a(2) = 6

cmd.parameters.Append cmd.CreateParameter(, adArray Or adInteger,
adParamInput, 8, a))

I have tried it a million different ways. It appends to the collection just
fine if I leave off the Array, but as soon as I try to set the params value
to an Array, I get a wrong type error. I checked the field in the table and
Access lists it's value as Double. I thought it should have been a Long
Data Type. I have tried other values for adInteger, like adDouble, and a
couple others. Still nothing. I looked on the net and did not see any
Access databases getting this to work properly. Maybe it is not supported.
Anyways the desired result is to place the array in to the SQL statement IN
clause. Please point me in the right direction if anything is obvious.
Thanks to all who reply.

Regards,
Chris

[William \(Bill\) Vaughn]

This is a very common question and it's been answered a number of times...
check the archives. (google groups)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Chris Kettenbach" <ch***@piasd.org> wrote in message
news:Y7********************@giganews.com...

Has anyone had any success passing an adArray arg to a command object
parameter for an IN Clause?
As in "SELECT * FROM tmp1 WHERE MyID IN ?"

I successfully open the connection object, and I can append the param the
the parameters collection but I can not get the result I am looking for.

I am using VBScript

dim cmd
set cmd = createObject("ADODB.Command")

const adArray = xH2000
const adInteger = 5
const adParamInput = 1

dim a
a(0) = 4
a(1) = 5
a(2) = 6

cmd.parameters.Append cmd.CreateParameter(, adArray Or adInteger,
adParamInput, 8, a))

I have tried it a million different ways. It appends to the collection
just
fine if I leave off the Array, but as soon as I try to set the params
value
to an Array, I get a wrong type error. I checked the field in the table
and
Access lists it's value as Double. I thought it should have been a Long
Data Type. I have tried other values for adInteger, like adDouble, and a
couple others. Still nothing. I looked on the net and did not see any
Access databases getting this to work properly. Maybe it is not
supported.
Anyways the desired result is to place the array in to the SQL statement
IN
clause. Please point me in the right direction if anything is obvious.
Thanks to all who reply.

Regards,
Chris

[Chris Kettenbach]

I looked in the groups and it looks like it is not possible. I wish you
would have said that instead of me going on a wild goose chase. But, thanks
for replying nonetheless.

Regards,
Chris

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:uw**************@TK2MSFTNGP15.phx.gbl...

This is a very common question and it's been answered a number of times...
check the archives. (google groups)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________

"Chris Kettenbach" <ch***@piasd.org> wrote in message
news:Y7********************@giganews.com...

Has anyone had any success passing an adArray arg to a command object
parameter for an IN Clause?
As in "SELECT * FROM tmp1 WHERE MyID IN ?"

I successfully open the connection object, and I can append the param the the parameters collection but I can not get the result I am looking for.

I am using VBScript

dim cmd
set cmd = createObject("ADODB.Command")

const adArray = xH2000
const adInteger = 5
const adParamInput = 1

dim a
a(0) = 4
a(1) = 5
a(2) = 6

cmd.parameters.Append cmd.CreateParameter(, adArray Or adInteger,
adParamInput, 8, a))

I have tried it a million different ways. It appends to the collection
just
fine if I leave off the Array, but as soon as I try to set the params
value
to an Array, I get a wrong type error. I checked the field in the table
and
Access lists it's value as Double. I thought it should have been a Long
Data Type. I have tried other values for adInteger, like adDouble, and a couple others. Still nothing. I looked on the net and did not see any
Access databases getting this to work properly. Maybe it is not
supported.
Anyways the desired result is to place the array in to the SQL statement
IN
clause. Please point me in the right direction if anything is obvious.
Thanks to all who reply.

Regards,
Chris

[William \(Bill\) Vaughn]

It IS possible, but it's not easy. There are several options if you want to
take this approach.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Chris Kettenbach" <ch***@piasd.org> wrote in message
news:HB*******************@newssvr13.news.prodigy. com...

I looked in the groups and it looks like it is not possible. I wish you
would have said that instead of me going on a wild goose chase. But,
thanks
for replying nonetheless.

Regards,
Chris

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:uw**************@TK2MSFTNGP15.phx.gbl...

This is a very common question and it's been answered a number of
times...
check the archives. (google groups)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no

rights.

__________________________________

"Chris Kettenbach" <ch***@piasd.org> wrote in message
news:Y7********************@giganews.com...

> Has anyone had any success passing an adArray arg to a command object
> parameter for an IN Clause?
> As in "SELECT * FROM tmp1 WHERE MyID IN ?"
>
> I successfully open the connection object, and I can append the param the > the parameters collection but I can not get the result I am looking
> for.
>
> I am using VBScript
>
> dim cmd
> set cmd = createObject("ADODB.Command")
>
> const adArray = xH2000
> const adInteger = 5
> const adParamInput = 1
>
> dim a
> a(0) = 4
> a(1) = 5
> a(2) = 6
>
> cmd.parameters.Append cmd.CreateParameter(, adArray Or adInteger,
> adParamInput, 8, a))
>
> I have tried it a million different ways. It appends to the collection
> just
> fine if I leave off the Array, but as soon as I try to set the params
> value
> to an Array, I get a wrong type error. I checked the field in the
> table
> and
> Access lists it's value as Double. I thought it should have been a
> Long
> Data Type. I have tried other values for adInteger, like adDouble, and a > couple others. Still nothing. I looked on the net and did not see any
> Access databases getting this to work properly. Maybe it is not
> supported.
> Anyways the desired result is to place the array in to the SQL
> statement
> IN
> clause. Please point me in the right direction if anything is obvious.
> Thanks to all who reply.
>
> Regards,
> Chris
>
>
>

[ck]

I am all ears Bill. Let's get an example if at all posible. Thanks in
advance for any help you may be able to provide.

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:uB**************@TK2MSFTNGP10.phx.gbl...

It IS possible, but it's not easy. There are several options if you want to take this approach.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________

"Chris Kettenbach" <ch***@piasd.org> wrote in message
news:HB*******************@newssvr13.news.prodigy. com...

I looked in the groups and it looks like it is not possible. I wish you
would have said that instead of me going on a wild goose chase. But,
thanks
for replying nonetheless.

Regards,
Chris

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:uw**************@TK2MSFTNGP15.phx.gbl...

This is a very common question and it's been answered a number of
times...
check the archives. (google groups)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no

rights.

__________________________________

"Chris Kettenbach" <ch***@piasd.org> wrote in message
news:Y7********************@giganews.com...
> Has anyone had any success passing an adArray arg to a command object
> parameter for an IN Clause?
> As in "SELECT * FROM tmp1 WHERE MyID IN ?"
>
> I successfully open the connection object, and I can append the param

the

> the parameters collection but I can not get the result I am looking
> for.
>
> I am using VBScript
>
> dim cmd
> set cmd = createObject("ADODB.Command")
>
> const adArray = xH2000
> const adInteger = 5
> const adParamInput = 1
>
> dim a
> a(0) = 4
> a(1) = 5
> a(2) = 6
>
> cmd.parameters.Append cmd.CreateParameter(, adArray Or adInteger,
> adParamInput, 8, a))
>
> I have tried it a million different ways. It appends to the collection > just
> fine if I leave off the Array, but as soon as I try to set the params
> value
> to an Array, I get a wrong type error. I checked the field in the
> table
> and
> Access lists it's value as Double. I thought it should have been a
> Long
> Data Type. I have tried other values for adInteger, like adDouble, and

a

> couple others. Still nothing. I looked on the net and did not see

any > Access databases getting this to work properly. Maybe it is not
> supported.
> Anyways the desired result is to place the array in to the SQL
> statement
> IN
> clause. Please point me in the right direction if anything is obvious. > Thanks to all who reply.
>
> Regards,
> Chris
>
>
>