By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Mysterious adArray - Does anyone know how to use?

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ck wrote:
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?
Nov 13 '05 #2

P: n/a
Dim ax(1) as String
ax(0) = "Member " Is this your Problem ax(0) = "Member (NO SPACE HERE)"
ax(1) = "Exhibitor "

"Salad" <oi*@vinegar.com> wrote in message
news:Uf****************@newsread3.news.pas.earthli nk.net...
ck wrote:
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?


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.