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"


.

Relevant Pages

  • Re: What could be the problem with this INSERT with ADO parameters?
    ... that adExecuteNoRecords argument should be the third argument in the ... The first argument is supposed to contain the sql statement if you haven't ... The third argument takes the Command ... The only way I have found sofar is to enclose the string in single ...
    (microsoft.public.data.ado)
  • TIP #185: Null Handling
    ... nulls, and command modifications for manipulating them. ... Tcl deals with strings, the universal medium for representing data. ... is know and it is an empty string, but if a respondent forgets to give ...
    (comp.lang.tcl)
  • Re: Determine how Word was launched
    ... inspect the command line used to launch Word. ... Private Declare Function GetCommandLineA Lib "kernel32" As Long ... Public Function GetCommandLineAs String ... Dim lngCmdLinePtr As Long ...
    (microsoft.public.office.developer.vba)
  • Re: Class.getMethod in classs static initializer block
    ... I just wanted to add that I had some similar code for a "command line server" project. ... static private final boolean DEBUG = true; ... {public ReturnCode runCommand(String s) ... public ReturnCode parseLine(String line) ...
    (comp.lang.java.programmer)
  • Re: New "base document" available
    ... How to go about retrieving command line parameters? ... the command line arguments (and environment variables) ... Keyword and positional parameters can be mixed in the same command line. ... clp-name points to a string containing "P1" ...
    (comp.lang.cobol)