I am trying to a sql statement in a for loop. The for condition runs for the UBOUND of array which stores the condition for WHERE clause in the following format:



Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


'database credentials
Dim uName As String, uPass As String

Dim strConnect As String
strConnect = "Driver={SQL Server Native Client 10.0};" & "Server=[servername]" & "Database=[dbname] ;Uid="&uname&";Pwd=" & upass & ";trusted connection=yes"


cn.ConnectionString = strConnect
cn.Open
for i =lbound(array1) to ubound(array1)     'array 1 and array 2  have same Lbound and Ubound

sqlselect=" select * from tablename where condition1=array1(i) and condition2= array2(i)

With rs
.ActiveConnection = cn
.Open sqlselect


sheets(1).Range("A1").copyfromrecordset rs
end with
next i

'close recordset
'close connection
The code fails at rs.open sql and gives a Type Mismatch error. The workflow is : run for loop for all the items in the array, plug in values in sql and dump the result in excel.

Excel: 2003
Database: SQL Server
OS: Windows XP SP3
Connection: ADODB


Any help/suggestions will be greatly appreciated.

Thanks,