Running SQL query for each item in an Array

[liz0001]

I am trying to run an SQL query for each item in an Array using ASP.

I have coded it as such. IDsArray() is an array of integers.

ArrayLength=15
 
sqlGetiIDs = "SELECT * FROM Table1 WHERE (dID = " & IDsArray(j) & ")"
j=0
Dim GetiIDsRSArray(ArrayLength)
 
do
    set GetiIDsRSArray(j) = conn.execute(sqlGetiIDs)
    j=j+1
loop until j=ArrayLength
 

First, I get an error while declaring the array: "Expected integer constant." Why doesn't it see the variable as an integer, and how can I fix it?
Second, this seems extremely awkward to create this array of recordsets. Can anyone suggest a better methodology? I would rather have it saved into one large recordset.

Many Thanks.

[liz0001]

I got it - I was trying to do it the hard way.

The right way to do this is from the back end, the actual database.

I created a UNION and made the array part of an actual table. Then I could run querys on it as normal and I would have access to all the data points at the same time.

I don't need it anymore, but just curious - I still don't know why the array declaration wouldn't take the variable, do anyone know about that?

[jhardman]

I am trying to run an SQL query for each item in an Array using ASP.

I have coded it as such. IDsArray() is an array of integers.

ArrayLength=15
 
sqlGetiIDs = "SELECT * FROM Table1 WHERE (dID = " & IDsArray(j) & ")"
j=0
Dim GetiIDsRSArray(ArrayLength)
 
do
    set GetiIDsRSArray(j) = conn.execute(sqlGetiIDs)
    j=j+1
loop until j=ArrayLength
 

First, I get an error while declaring the array: "Expected integer constant." Why doesn't it see the variable as an integer, and how can I fix it?
Second, this seems extremely awkward to create this array of recordsets. Can anyone suggest a better methodology? I would rather have it saved into one large recordset.

Many Thanks.

first, I noticed this problem before as well, I think vbscript might need a constant, rather than a variable, to define arrays. don't ask me why, it works everywhere else.

second, try:

'just to simplify a little bit, let's take out the parenthases which 
'are not necessary in such a straight-forward query statement
'and let's put the conditions in a series of OR clauses
sqlGetiIDs = "SELECT * FROM Table1 WHERE "
for j = 0 to 15
   sqlGetiIDs = sqlGetiIDs & "dID = " & IDsArray(j) & " OR "
next
 
'sqlGetiIDs currently ends with " OR " which needs to be removed thus:
sqlGetiIDs = left(sqlGetiIDs, len(sqlGetiIDs)-4)
 
'it's a good idea to check the final string just to make sure it looks right:
response.write "<!-- sqlGetiIDs: " & sqlGetiIDs & " -->" & vbNewLine
 
'then send this to your recordset object:
objRS.open sqlGetiIDs, conn
'I just like to say "open" rather than "execute", your call, really.

Does this make sense?

Jared