Array data in SQL Query?

[mazzarin]

Hi,

I'm trying to read data from a worksheet, inputting it into an array,
and make SQL queries based on the array value, repeat for each value.
I'm having problems getting the array value into the query though.

Heres example code:


Sub Pass_array()
Dim myarray As Variant
myarray = Range("C15:C18").Value
receive_array myarray

End Sub

---

Sub receive_array(thisarray)
Dim dbConn2 As New ADODB.Connection
Dim rs2 As New ADODB.Recordset 'the recordset

dbConn2.ConnectionString = (connection string here, works fine)

dbConn2.Open



For i = 1 To UBound(thisarray)

MsgBox thisarray(i, 1) *** <-- this is my check to see what value is
getting put in

With rs2

..Open "SELECT DISTINCT PRICING.PRICE FROM MAIN, PRICING WHERE
(MAIN.SERIES LIKE 'A123') AND (PRICING.TYPE = 'D') AND (MAIN.ID =
PRICING.PART_ID) AND (PRICING.START=thisarray(i,1)", dbConn2,
adOpenStatic

noRecords = .RecordCount

Range("F15").CopyFromRecordset rs

.Close

End With

Next

dbConn.Close


End Sub


If I do this, it says thisarray is not a function. If I just input i,
it says invalid column name.

Any help is appreciated thanks.

 

[RB Smissaert]

An array element is just like a variable, so you will have to do this:

..Open "SELECT DISTINCT PRICING.PRICE FROM MAIN, PRICING WHERE
(MAIN.SERIES LIKE 'A123') AND (PRICING.TYPE = 'D') AND (MAIN.ID =
PRICING.PART_ID) AND (PRICING.START = " & thisarray(i,1), dbConn2,
adOpenStatic

Otherwise thisArray(i,1) will taken as that literal string, which obviously
means nothing.

RBS

 

[mazzarin]

I see what you mean!

but you have the value placed outside of the query itself, and I cannot
get it to become what its supposed to be (in the first case, 500)
inside the quotes...

(PRICING.START= &thisarray(i,1))", --> Incorrect syntax near '&'

(PRICING.START= '&thisarray(i,1)')", --> Syntax error converting the
varchar value '&thisarray(i,1)' to a column of data type int

(PRICING.START= " &thisarray(i, 1), --> Incorrect syntax near '500'

Thoughts?

 

[RB Smissaert]

I find it easier to put the query in a variable, so you would get:

Dim strQuery As String

strQuery = "SELECT DISTINCT PRICING.PRICE " & _
"FROM MAIN, PRICING " & _
"WHERE MAIN.SERIES LIKE 'A123' AND " & _
"PRICING.TYPE = 'D' AND " & _
"MAIN.ID = PRICING.PART_ID AND " & _
"PRICING.START = " & thisarray(i, 1)

With rs2
.Open strQuery, dbConn2, adOpenStatic

As your brackets in the query served no purpose I left them out.
Also better to keep your code lines short and divide it in logical parts as
above.


RBS

 

[mazzarin]

It works! Thank you very much. I will try and organize my code better
in the future