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



Think I have this all fixed now.
I needed to create the paramaters with .CreateParameter
and set the datatype and size (for strings) of all the parameters.

RBS


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:%23hl$reuEIHA.280@xxxxxxxxxxxxxxxxxxxxxxx
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?
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.

So, how do I handle a string with comma's in the parameter array?
I could for this particular query go back to not using parameters, but I am sure there must be a
solution to this.


RBS


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:eRoLADrEIHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
I think I have this mostly fixed now.
Needed to replace the constants with variables.
Also I needed to enclose fields that had comma's with single quotes.
This surprised me and it is not that good as these single quotes do appear in the database.
Any solution for this last problem?

RBS


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:%23A2DxooEIHA.4752@xxxxxxxxxxxxxxxxxxxxxxx
RB Smissaert wrote:
Trying to debug a procedure that writes data to an Interbase database.
I am using an Intersolve ODBC driver and ADO in Excel VBA.
This is done in a loop and I used to d