Re: What could be the problem with this INSERT with ADO parameters?



BtW, that adExecuteNoRecords argument should be the third argument in the call

Ah, yes, thanks for that correction. Maybe this explains some of the trouble I had.
In any case, maybe it is better to do this all formally and specifically with .CreateParameter etc.
instead of having the variant array with all the parameters.

RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:%23qXjgawEIHA.4772@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
Let me explain the last problem a bit clearer:

given this SQL:

strSQL = "INSERT INTO TABLE1(FIELD1, FIELD2, FIELD3) " & _
"VALUES(?, ?, ?)"

and this ADO command:

cmdADO.Execute adExecuteNoRecords, Array(arr(i, 1), arr(i, 2),
arr(i, 3)
How do I handle the situation where arr(i, 1) is a string with a
comma in it?

You don't need to worry about it. That's the beauty of using parameters.
BtW, that adExecuteNoRecords argument should be the third argument in the call. Like this:

cmdADO.Execute , _
Array(arr(i, 1), arr(i, 2), arr(i, 3), _
adExecuteNoRecords

The first argument is supposed to contain the sql statement if you haven't already assigned one to CommandText. The third argument takes the Command and Execute options.

arr is a variant array.
The only way I have found sofar is to enclose the string in single
quotes, but that is
no good really as that full string, including the quotes will then be
written to the database.

I have tried doing this:

Dim str As String
str = CStr(arr(i, 1)
cmdADO.Execute adExecuteNoRecords, Array(str, arr(i, 2), arr(i, 3)

But that gives the same problem, it actually crashes Excel.

It's probably because you have the arguments in the wrong order - which should raise an error, not crash Excel ???

So, how do I handle a string with comma's in the parameter array?

Again: don't worry about them!!!


--
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"


.