Re: What to use for ADO parameter type and size

True, actually a bit more. This was compared to concatenating the array elements to the query string in
the loop.

RBS

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:uIaGfEG0HHA.5980@xxxxxxxxxxxxxxxxxxxxxxx
4%?!?!?

I'm shocked. Are you sure it wasn't .04% different?

RB Smissaert wrote:
Have done some timing and not explicitly setting the parameter object
(as you suggested) is a bit faster.
In my particular situation about 4%, so will go with that.
Thanks again for the help.

RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:O8rx3vF0HHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
Running a parameterized query that loops through
a variant array holding integer values. These values may go up to
something like 123456789012345, so beyond
the Integer or Long datatype.

The code will be like this:

----------------------------------------------------------------

Dim cmdADO As ADODB.Command
Dim oParam As ADODB.Parameter

Set cmdADO = New ADODB.Command
Set oParam = New ADODB.Parameter

With cmdADO
.ActiveConnection = ADOConn
.CommandText = strSQL3 & "?"
.CommandType = adCmdText
End With

With oParam
.Type = adChar
.Size = 15
.Direction = adParamInput
End With

cmdADO.Parameters.Append oParam

For i = 0 To UBound(arr2)

cmdADO.Parameters(0).Value = arr2(i, 0) 'this is a variant
array
Set rs = cmdADO.Execute()
----------------------------------------------------------

How should I define the properties of oParam?
The coding as above works, but somehow doesn't look quite right.
Any suggestions/advice?


What doesn't look right about it (besides the fact that you did not
tell ADO not to build a recordset)?

It could have been simpler: you did not have to use an explicit
parameter object:

Dim cmdADO As ADODB.Command

Set cmdADO = New ADODB.Command

With cmdADO
Set .ActiveConnection = ADOConn '"Set" should be used here
.CommandText = strSQL3 & "?"
.CommandType = adCmdText
.Execute ,Array(arr2(i, 0)), adExecuteNoRecords
End With

But really, there's nothing whrong with what you did.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages