Returning results from database using IN array

Question

I was wondering if someone could help me.

I have an array which is like this

fileArray = '2612(25).jpg', '2012(15).jpg', '2012(31).jpg', '2012(21).jpg', '2012(58).jpg'

Now i have the following code.

Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open strOLEDBString, strODBCUser, strODBCPass

SQL = "SELECT p, sum(vote) as likes FROM vote WHERE p in (" & fileArray & ") GROUP BY p ORDER BY sum(vote) desc"

Set rs = DBConn.Execute(SQL)

If rs.eof Then
    HTMLTable = HTMLTable & " Nothing returned"
Else 
    HTMLTable = HTMLTable & rs("p")
End If

The results only show the last filename in the array, it should be displaying all of them.

I think you have an error here. How can you take an array of a bunch of different strings and jam it into a space where only one string is expected? — STLDev
– STLDev, Commented Jun 13, 2013 at 3:00
@YuriyGalanter-who are you asking? If me, then, yes, I'm familiar with it. — STLDev
– STLDev, Commented Jun 13, 2013 at 3:05
@STLDeveloper Actually I beleive in this case it's a single string of comma separated values. — Comfortably Numb
– Comfortably Numb, Commented Jun 13, 2013 at 3:16

Comfortably Numb · Accepted Answer · 2013-06-13 03:17:27Z

1

You need to loop thru ADO Recordset to get all the records:

...
Else
  Do While Not rs.eof
    HTMLTable = HTMLTable & "<tr><td>" & rs("p") & "</td></tr>"
    rs.MoveNext
  Loop
End If

edited Jun 13, 2013 at 3:17

answered Jun 13, 2013 at 3:01

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

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

4 Comments

Comfortably Numb Over a year ago

Where do you assign initial value of HTMLTable? It should have at least the same result you did before, but possibly more. Depending on result returned by the query. How many records do u get if u run the query directly in DB?

BigJobbies Over a year ago

Quite a few lines up, using HTMLTable = "<table border = 0 width=""100%"" >" & vbCRLF

Comfortably Numb Over a year ago

I've modified my code taking into account this being a table, can u give it a try?

BigJobbies Over a year ago

Opps ... My bad ... I forgot to add the 'Not' in the Do While statement doiiiiii .. That works perfect now ... Thank you :)

Tom Collins · Accepted Answer · 2013-06-13 03:17:20Z

0

You need "" around your filearray statement.

fileArray = "'2612(25).jpg', '2012(15).jpg', '2012(31).jpg', '2012(21).jpg', '2012(58).jpg'"

answered Jun 13, 2013 at 3:17

Tom Collins

4,0592 gold badges22 silver badges36 bronze badges

Collectives™ on Stack Overflow

Returning results from database using IN array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related