How to use ADODB parameterized query with adArray data type in VBScript?

Question

I have not been able to find an example of how to use this anywhere. I am trying to do a simple SELECT statement with a parameterized query. I want to use the adArray data type. Here is an example

sql = "SELECT * FROM table WHERE id IN ?"
set objCmd = Server.CreateObject("ADODB.Command")
set objParam = objCmd.Createparameter("@id", 0x2000, 1, length, arrMyArray)

objCmd.Parameters.Append objParam

This throws a wrong type error. I was curious if anyone has ever gotten this to work or has any examples. That'd be great.

Thanks for the help in advance!

I left out all the connection and recordset and actually executing the command. — robbie
– robbie, Commented Feb 16, 2012 at 16:26

Community · Accepted Answer · 2017-05-23 11:53:03Z

2

I have no idea which database providers will support arrays.

What I prefer to do is pass the array as a single long string, then use a UDF called Split(). The result is something like this:

sql = "SELECT * FROM table WHERE id IN (Split(?))"
set objCmd = Server.CreateObject("ADODB.Command")
myBigString = ConvertArrayToCSV(arrMyArray) ' you have to write this, of course
set objParam = objCmd.Createparameter("@id", 200, 1, length, myBigString)

objCmd.Parameters.Append objParam

Here's a discussion of the Split() concept.

Edit

I corrected the above (the parameter type is 200, not 0x2000), and I also now see that ADO appears to support this syntax:

0x2000 OR 129 ' array of strings
0x2000 OR 200 ' array of varchar

But I haven't tested this.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Feb 16, 2012 at 16:34

egrunin

25.2k8 gold badges53 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

robbie Over a year ago

Thanks for the quick response. This may be a dumb question, but how exactly am I supposed to tell the objParam that it's an array of string type. The documentation says this: Combine with another data type to indicate that the other data type is an array How do I do that with the 0x2000

user692942 Over a year ago

I'm pretty sure that although it's included in the documentation it isn't support by ADO and was added for future compatibility.

Nickolay Over a year ago

More info from Lankymart on the adArray type being unimplemented: stackoverflow.com/a/35453499/1026

Collectives™ on Stack Overflow

How to use ADODB parameterized query with adArray data type in VBScript?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related