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



I hope you fixed your Execute statements ...

Yes, all fixed now.
Surprised that I haven't noticed this earlier and that
it hasn't caused problems. I suppose it was just ignored.

Yes, I probably didn't need the full .CreateParameter method in the end, but there
might be some advantage if only that I learned something new there.

RBS

"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message news:O27M%23twEIHA.1316@xxxxxxxxxxxxxxxxxxxxxxx
You should not have had to. There is nothing wrong with doing it that way, but there is no necessity to do it that way either, especially with a Jet backend (sing Jet does not support output and return parameters)

I hope you fixed your Execute statements ...

RB Smissaert wrote:
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