Mysterious adArray - Does anyone know how to use?

[ck]

I apologize for cross posting, this is just driving me crazy. I have been
beating myself up all day trying to find one good example of adArray as part
of a parameter. I went this route.

Sub Test1()
dim cn as new adodb.Connection
dim s$
s = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = somefile.mdb;"
cn.Open s

dim cmd as new adodb.Command
with cmd
..ActiveConnection = cn
.CommandText = "SELECT CompanyName From [Names] WHERE NameType IN ?"
.CommandType = adCmdText
End With

Dim ax() As String
ReDim ax(1) As String
ax(0) = "Member"
ax(1) = "Exhibitor"

With prm1
.Type = 8392 'Errors right about here, I want the Array ( ax() in
this example ) to provide the parameter values for an SQL IN Clause
.Direction = adParamInput
.Size = 20
.Value = ax()
End With

cmd.Parameters.Append prm1

Dim rs As New ADODB.Recordset
rs.Open cmd, , adOpenStatic, adLockPessimistic

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Sub

I have also tried cmd.Parameters.Append cmd.CreateParameter(, adArray or
adVarChar, adParamInput, 20, az()). I get an error saying wrong type of
arguments. I have tried just about possible DataType in the Array, Also
tried adVarray + adVarChar, still doen't worh right.

Any ideas? If you see anything obvious or any suggestions to try it a
different way, please let me know. Thank you to all whom reply.

Regards,
kc

 

[Salad]

I apologize for cross posting, this is just driving me crazy. I have been
beating myself up all day trying to find one good example of adArray as part
of a parameter. I went this route.

Sub Test1()
dim cn as new adodb.Connection
dim s$
s = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = somefile.mdb;"
cn.Open s

dim cmd as new adodb.Command
with cmd
..ActiveConnection = cn
.CommandText = "SELECT CompanyName From [Names] WHERE NameType IN ?"
.CommandType = adCmdText
End With

Dim ax() As String
ReDim ax(1) As String
ax(0) = "Member"
ax(1) = "Exhibitor"

With prm1
.Type = 8392 'Errors right about here, I want the Array ( ax() in
this example ) to provide the parameter values for an SQL IN Clause
.Direction = adParamInput
.Size = 20
.Value = ax()
End With

cmd.Parameters.Append prm1

Dim rs As New ADODB.Recordset
rs.Open cmd, , adOpenStatic, adLockPessimistic

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Sub

I have also tried cmd.Parameters.Append cmd.CreateParameter(, adArray or
adVarChar, adParamInput, 20, az()). I get an error saying wrong type of
arguments. I have tried just about possible DataType in the Array, Also
tried adVarray + adVarChar, still doen't worh right.

Any ideas? If you see anything obvious or any suggestions to try it a
different way, please let me know. Thank you to all whom reply.

Regards,
kc

Why not create the In clause of the SQL string. Here is an
example...one for numeric, the other for strings.
Sub InNumber()
Dim s As String
Dim I As Integer
For I = 1 To 5
s = s & I & ","
Next
s = "(" & Left(s, Len(s) - 1) & ")"
MsgBox s
End Sub
Sub InString()
Dim s As String
Dim I As Integer
For I = 1 To 5
s = s & "'" & I & "',"
Next
s = "(" & Left(s, Len(s) - 1) & ")"
MsgBox s
End Sub

I never saw what you set Prm1 to but why not create the full string of
the SQL statement then execute?

 

[Jim Burns]

Dim ax(1) as String
ax(0) = "Member " Is this your Problem ax(0) = "Member (NO SPACE HERE)"
ax(1) = "Exhibitor "

I apologize for cross posting, this is just driving me crazy. I have been
beating myself up all day trying to find one good example of adArray as part
of a parameter. I went this route.

Sub Test1()
dim cn as new adodb.Connection
dim s$
s = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = somefile.mdb;"
cn.Open s

dim cmd as new adodb.Command
with cmd
..ActiveConnection = cn
.CommandText = "SELECT CompanyName From [Names] WHERE NameType IN ?"
.CommandType = adCmdText
End With

Dim ax() As String
ReDim ax(1) As String
ax(0) = "Member"
ax(1) = "Exhibitor"

With prm1
.Type = 8392 'Errors right about here, I want the Array ( ax() in
this example ) to provide the parameter values for an SQL IN Clause
.Direction = adParamInput
.Size = 20
.Value = ax()
End With

cmd.Parameters.Append prm1

Dim rs As New ADODB.Recordset
rs.Open cmd, , adOpenStatic, adLockPessimistic

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Sub

I have also tried cmd.Parameters.Append cmd.CreateParameter(, adArray or
adVarChar, adParamInput, 20, az()). I get an error saying wrong type of
arguments. I have tried just about possible DataType in the Array, Also
tried adVarray + adVarChar, still doen't worh right.

Any ideas? If you see anything obvious or any suggestions to try it a
different way, please let me know. Thank you to all whom reply.

Regards,
kc

Why not create the In clause of the SQL string. Here is an
example...one for numeric, the other for strings.
Sub InNumber()
Dim s As String
Dim I As Integer
For I = 1 To 5
s = s & I & ","
Next
s = "(" & Left(s, Len(s) - 1) & ")"
MsgBox s
End Sub
Sub InString()
Dim s As String
Dim I As Integer
For I = 1 To 5
s = s & "'" & I & "',"
Next
s = "(" & Left(s, Len(s) - 1) & ")"
MsgBox s
End Sub

I never saw what you set Prm1 to but why not create the full string of
the SQL statement then execute?