2

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!

robbie
  • 658
  • 3
  • 11
  • 26

1 Answers1

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.

Community
  • 1
  • 1
egrunin
  • 24,650
  • 8
  • 50
  • 93
  • 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 – robbie Feb 16 '12 at 18:23
  • 1
    I'm pretty sure that although it's included in the documentation it isn't support by ADO and was added for future compatibility. – user692942 Feb 17 '16 at 10:22
  • More info from Lankymart on the adArray type being unimplemented: http://stackoverflow.com/a/35453499/1026 – Nickolay Nov 28 '16 at 08:23